Percona - Metrics and perfs drop after migration.

MySQL & MariaDB Other MySQL® Questions
1

Hi i’m pretty new to MySQL, and recently our company moved from the “regular” version of MySQL (5.1) to Percona.(5.6)

We have this machine for migration :

2 x 2690v3 Intel Processors (24 Cores 48 Threads)
256 GB 2133Mhz ECC memory
2xIntel SSD for System
4xMicron P420m RAID 10 for DATA (Making it 1,3TB EXT4)
10 Gbps Intel Cards (Base-T)
Ubuntu 14.04.02 LTS
5.6.23-72.1-log Percona Server (GPL), Release 72.1, Revision 0503478

We got something like :

Read 4K : 4,9GB/s
Read 16K : 6,6 GB/s

Network Troughput : 9,42Gbps

Sysbench shows something like :

OLTP test statistics: 48 threads
queries performed:
read: 22059002
write: 0
other: 3151286
total: 25210288
transactions: 1575643 (26260.09 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 22059002 (367641.29 per sec.)
other operations: 3151286 (52520.18 per sec.)

Test execution summary:
total time: 60.0014s
total number of events: 1575643
total time taken by event execution: 2872.7015
per-request statistics:
min: 1.13ms
avg: 1.82ms
max: 18.27ms
approx. 95 percentile: 2.57ms

Threads fairness:
events (avg/stddev): 32825.8958/149.43
execution time (avg/stddev): 59.8479/0.00

I don’t know what your metric looks like, but, foor a noob like me, it looks solid.

Well, i guess we have some pretty good hardware down here, but so far, the App Team complains about really poor database performance compared to legacy hardware, which is strange. Old Xeons, 32GB of slower memory, only 2x10K 600Gigs Drives (Hardware raid with BBU and 256MB of cache).

We tuned the server accordingly to the parameters given by percona’s tuner.

But it might be related to something else.

In fact i’m beggining to think that there is a link between the change of version has more to do than the server itself.

Any clues ?

Have a nice day.

2

Hi NovemberMike;

Sysbench tests are only useful for comparison purposes. So in your case, if you ran the same exact test on your old hardware, that would be a useful comparison to say that your new hardware can do XX% more transactions per second.

Changing to new hardware and going from 5.1 to 5.6 all at once definitely introduces a lot of moving parts. However I would also be inclined to think the issue is more to do with the software side of things vs the hardware, but anything is possible (i.e. incorrectly configured / faulted disks can be a show stopper).

First thing to check would be to make sure things look good at the hardware level. Check all the usual things like system load, memory usage (make sure it’s not swapping), check I/O wait, etc. Check the system logs for any errors, especially ones related to memory and disk. Assuming all that looks good, then you need to start looking at MySQL.

Are your tables all InnoDB? And were they all InnoDB prior to the upgrade? Are your non-resource specific MySQL configuration settings the same, like transaction isolation level / innodb_flush_log_at_trx_commit, etc.?

Next thing I would do is run a slow query log and see what queries are taking a long time and see if indexes can help. Chances are some of your common queries are just being handled by the optimizer in a way that is no longer advantageous to your current indexes. This will often fix the issue, because if the few main query footprints that the application uses are not being optimized well, then the whole app will be slow.

Once you know what is actually slow, it will be easier to dig into what the problem may be. I would also ask for some data from them showing that the app is actually slower, as it wouldn’t be the first time something changed and people automatically thought it was worse when in reality it was all in their head (but make sure to phrase it nicely). :wink:

-Scott

3

Hello,

Thanks for your input,

Much appreciated.

Sorry for the late update. We finally get it to work as expected.

The front end server hosting the Web App connecting to Percona was the culprit. Once the Devs got this fixed, it flyed.

During the process Dev team replaced Percona by a standard issue MySQL server to troubleshoot.

Once they fixed the bug we reverted to Percona and they saw a massive improvement, especially in latency.

Regards,

N.