3 ms·
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
by jumby 13y ago
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.
- jumby 13y ago> 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).
- jol 13y ago>"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.
- jumby 13y agoI 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.
- webstartupper 13y agoThe 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.
- jumby 13y agooooohkay. you can run with that then.
- gngeal 13y agoThe 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.
- webstartupper 13y agoI 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)
- jumby 13y agoYou 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?
- webstartupper 13y agoMy apologies if I came across as if I was shooting down the opinions here.