Today, after upgrading the MySQL version which allowed me to allocate more memory to the key buffer size (12 Gb), I managed to get it to 328k QPS.
The server is similar to the one from the previous post. Its a 4 year old, but quite meaty server.
The server ran triggers and stored functions that I wrote to help connect different data sets together. Basically reference another table using fairly complicated search logic, get an ID back and store it. The code was then tweaked to be as fast and light as possible.
This time I ran:
- 99 million row table to reference a 101 million row table
- 45 million row table to reference the same 101 million row table
- 35 million row table (same reference)
- 29 million row table (same reference)
- 28 million row table (same reference)
All at the same time..
The server hovered consistently around the 300k QPS mark, with a here and there high of 322-328k.
Also, I had to modify the code of Mtop (the application used to monitor MySQL in the picture) to ask for "Queries" and not "Questions" from MySQL after I upgraded it.
I'm very happy with the result. I can't wait to get my hands on a stronger server to try to get a higher number, and in the meantime I will try to keep tweaking the settings on this server to hopefully get it from a load of 4 to 10.
Good Work !! Do give us some brief details on parameters which were tweaked and how they helped.
ReplyDeleteThere are not queries, but questions. That means all statements inside triggers, stored procedures etc. Look at SHOW GLOBAL STATUS for the right numbers.
ReplyDelete