The domains table currently has 84 fields. We collect metrics from various sources, so reducing the number of fields is not feasible. All the field types are the smallest that we could use - e.g. tinyint(4) instead of an int etc.
Since there are so many fields with data from multiple sources, we have queries running searching on individual fields. Due to this we need to have many indexes. 4GB of the 8GB is the size of the index itself.
Are you sure you've read up on your C. J. Date? I've had that once before: someone complaining that "queries take too much time" with a paltry single-digit-GB database. When I asked about the specifics, the only repeating reply was "we can't tell you". You don't mention anything of value, but querying a few million records can't possibly take a few minutes on the aging desktop computer I've bought seven years ago, much less on a modern server.
I presume the reason it was slow was because the domains table was write heavy. There are multiple crons running in the background selecting data from the domains table, accessing external APIs and updating individual records.
Selects per hour: 37K
Updates per hour: 170K
While the speed of selects or updates by the background crons was and is not important, the speed of selects run by the users on the same table was important. The easy solution was to cache the data so that the users could search domains at a good speed. The memory table just worked brilliantly as a cache.
(I'm no mysql guru and its my first project where MyIsam did not work for me, so I know I definitely could do a better job of optimizing the Innodb table and Innodb settings in my.cnf)
You claim not a MySQL guru, but are shooting down the majority opinion here that something major is wrong with the schema design. That's fine - use your in-memory table and then claim you need a NoSQL solution when your "big data" hits 10GB.
84 fields in one table is terrible DB design, and I'm not surprised that it's slow.
If it were me, I'd split that one giant table into a "main" table with some basic information and foreign keys pointing to other tables with the more detailed information from the other sources.
I wonder how much the indexes are helping. It sounds like it could be a hard case for a query planner. You might be doing more full table scans than you realize, and it might not be as slow as you might expect. I think regex searches do full scans, so that gives an idea of the cost.
This is a great problem that would be fun to work on. Except that I guess you already found a satisfactory solution.
Comments
The domains table currently has 84 fields. We collect metrics from various sources, so reducing the number of fields is not feasible. All the field types are the smallest that we could use - e.g. tinyint(4) instead of an int etc.
Since there are so many fields with data from multiple sources, we have queries running searching on individual fields. Due to this we need to have many indexes. 4GB of the 8GB is the size of the index itself.
The domains table currently has 84 fields.
Are you sure you've read up on your C. J. Date? I've had that once before: someone complaining that "queries take too much time" with a paltry single-digit-GB database. When I asked about the specifics, the only repeating reply was "we can't tell you". You don't mention anything of value, but querying a few million records can't possibly take a few minutes on the aging desktop computer I've bought seven years ago, much less on a modern server.
I presume the reason it was slow was because the domains table was write heavy. There are multiple crons running in the background selecting data from the domains table, accessing external APIs and updating individual records.
Selects per hour: 37K Updates per hour: 170K
While the speed of selects or updates by the background crons was and is not important, the speed of selects run by the users on the same table was important. The easy solution was to cache the data so that the users could search domains at a good speed. The memory table just worked brilliantly as a cache.
(I'm no mysql guru and its my first project where MyIsam did not work for me, so I know I definitely could do a better job of optimizing the Innodb table and Innodb settings in my.cnf)
You claim not a MySQL guru, but are shooting down the majority opinion here that something major is wrong with the schema design. That's fine - use your in-memory table and then claim you need a NoSQL solution when your "big data" hits 10GB.
How's the disk io on that linode VPS?
My apologies if I came across as if I was shooting down the opinions here.
84 fields in one table is terrible DB design, and I'm not surprised that it's slow.
If it were me, I'd split that one giant table into a "main" table with some basic information and foreign keys pointing to other tables with the more detailed information from the other sources.
Your assumption is that all fields are not required in the default search query.
In our case, joining 6 tables with 14 fields each is going to be much slower than running the query of just the table.
I wonder how much the indexes are helping. It sounds like it could be a hard case for a query planner. You might be doing more full table scans than you realize, and it might not be as slow as you might expect. I think regex searches do full scans, so that gives an idea of the cost.
This is a great problem that would be fun to work on. Except that I guess you already found a satisfactory solution.
oooohkay. you can run with that then.