Dealing with large numbers of records in MySQL
We recently got fed up with UPS's server outages (of the 2 IP's we typically use for shipping quotes, 1 was down pretty regularly for a while). So, I decided to populate our shippingrates table with all of the data we could ever need.
It all went along fine, until it got to about 10M records. At that point, our site started slowing and slowing, and what I found was the keys weren't able to be used for our standard query. It wasn't that big of a deal with 100k or even 200k records, but when I got up to 10M (it's over 30M now), that caused problems.
I did an alter table and made our index include one more field, and it now sorts everything the way I wanted by default. It wasn't actually the select statement causing the problem, it was the ORDER BY portion. It was having to use a filesort, which for a huge table doesn't work real quickly.
What other symptoms was this causing? For one, I was seeing the time spent waiting for IO growing. When looking at the output from top in Linux, that's the %wa column. All 4 were showing 80%+ for long periods on our boxes, so I knew something was hammering the drives.
I've also tweaked a few other configuration variables, such as sort_buffer, key_buffer, myisam_sort_buffer_size and record_buffer. The end result is that I can query that table at least as quick as when it was a fraction of the records. Yet again, the mysql book I bought a while ago comes in handy.
It all went along fine, until it got to about 10M records. At that point, our site started slowing and slowing, and what I found was the keys weren't able to be used for our standard query. It wasn't that big of a deal with 100k or even 200k records, but when I got up to 10M (it's over 30M now), that caused problems.
I did an alter table and made our index include one more field, and it now sorts everything the way I wanted by default. It wasn't actually the select statement causing the problem, it was the ORDER BY portion. It was having to use a filesort, which for a huge table doesn't work real quickly.
What other symptoms was this causing? For one, I was seeing the time spent waiting for IO growing. When looking at the output from top in Linux, that's the %wa column. All 4 were showing 80%+ for long periods on our boxes, so I knew something was hammering the drives.
I've also tweaked a few other configuration variables, such as sort_buffer, key_buffer, myisam_sort_buffer_size and record_buffer. The end result is that I can query that table at least as quick as when it was a fraction of the records. Yet again, the mysql book I bought a while ago comes in handy.
Comments