Hi All,
We recently encountered performance issues on UPDATEs on MySQL 5.1 as well as Percona 5.1.
When using Lucid deb package provided from Percona average update query takes about 50ms to complete on a table with ~500K records.
I compiled Percona from source with following flags:
CFLAGS="-O2 -DBIG_JOINS=1 -march=nocona -mfpmath=sse -msse2 -mmmx"CXXFLAGS="-O2 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -
Our server is a Dual Xeon CPU 3.40GHz with 4GB of RAM. Ubuntu 10.04 is installed.
The compiled version takes average 25ms to complete update query.
Here is output of the profiling:
±--------------------------±---------+| Status | Duration |±--------------------------±---------+| starting | 0.000081 || checking permissions | 0.000010 || Opening tables | 0.000019 || System lock | 0.000006 || Table lock | 0.000007 || init | 0.000100 || Searching rows for update | 0.004254 || Updating | 0.066596 || end | 0.000013 || query end | 0.000005 || freeing items | 0.042667 || logging slow query | 0.000004 || cleaning up | 0.000006 |±--------------------------±---------+
Same query takes 1ms per update on my macpro workstation.
I created a VM with Parallels installed Ubuntu 10.04 with 4 cpus and 4g ram and average update is 2ms, Percona was installed from deb package.
On server turning off the query cache won’t increase the performance.
Please let me know how I can provide more information to troubleshoot this performance issue.
Thank you
Moe