8 million records (at 7GB!) and it's slow means there is something seriously wrong with your schema. That table would entirely fit in InnoDB Buffer Pool on any modern hardware.
Unfortunately, since there are 30 different columns that can be searched on
That's your first problem. I bet the schema isn't normalized and these are all varchar columns.
~~ Normalize your schema ~~ such that the main table is a set of integers. JOIN on PK and index whatever is required. Trust me, MySQL is fast when you do it right. I have tables with 650M records and lookups are just as fast as day 1. Keep table size AS SMALL AS POSSIBLE (less columns, use ints, etc).
Use date_ids & time_ids (from standard DWH techniques) if you are storing datetimes, not datetime fields (default indexing them is 1 second resolution which is stupid).
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.
"That table would entirely fit in InnoDB Buffer Pool "
I would like to see how it fits in the pool on 2GB linode (mentioned in the article), also, I like that people try to think before saying "big data" and grabbing some NoSQL that is not very good fit in some places or gigantic mapreduce in 67 servers.
Comments
8 million records (at 7GB!) and it's slow means there is something seriously wrong with your schema. That table would entirely fit in InnoDB Buffer Pool on any modern hardware.
I want to see your slow query log.
That's your first problem. I bet the schema isn't normalized and these are all varchar columns.
~~ Normalize your schema ~~ such that the main table is a set of integers. JOIN on PK and index whatever is required. Trust me, MySQL is fast when you do it right. I have tables with 650M records and lookups are just as fast as day 1. Keep table size AS SMALL AS POSSIBLE (less columns, use ints, etc).
Use date_ids & time_ids (from standard DWH techniques) if you are storing datetimes, not datetime fields (default indexing them is 1 second resolution which is stupid).
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.
I am saying there is NO WAY MySQL sucks at 7M records. That's just jacked design. Talk to me at 7B when you need to reach for something bigger.