Re: MySQL Partitioning showing low performance
> SELECT column8, column9 FROM myTable WHERE column2 = ? AND column3 = ? AND column4 =? AND column5 = ? AND column7 = ? AND column6 = ? LIMIT 1;
> I'm puzzled -- exactly what did you test that ran 3 times as fast? If we are looking at fractions of a second, I would not take much stock in that result.
To check the performance without implementing partitioning, the above query was put under a load test with different test data for a period of 15minutes. Testing with different test data made sure that the nothing is cached in the mysql buffer pool.
The same load test was then carried out after implementing partitioning for 15 minutes. And it was observed that while we got a TPS (Transactions Per Second) of 350 without partitioning, we only got a TPS of 139 after implementing partitioning. This is the reason why I said that the performance with MySQL Partitioning was seen almost 3 times slower.
So, TPS is what is compared here, and there is significant difference in performance observed when load is more. I am not talking about a single request here where the difference is a fraction of a second.
> The first SELECT warms up the cache (buffer_pool); the second one takes advantage of the cache. Also if the Query Cache is turned on, it could easily run 1000 times as fast. I like turn off the Query Cache, then quote the second SELECT.
The Query Cache is already set OFF. To add to that, I've made sure that before running each of these load tests, the MySQL server itself is restarted.
> There is nothing magical about this SELECT with/without partitioning. It will use the perfect index. PARTITIONed may be slower because it may check each partition.
Yes, that's right. There is nothing magical about the SELECT with/without partitioning as it will use the perfect index. But, I believe that Partitioning is not expected to be too slow also; since ideally, searching in a specific partition for a record (with index) is expected to be faster than searching for a row among 340million records stored in a 'single' database file.
Edited 1 time(s). Last edit at 02/08/2013 02:11AM by Manosh Manohar.