Mysql chạy chậm hơn trên CPU bốn nhân

Công ty mới mua con server 2 CPU - 4 nhân , thay thế con DB server (mysql)  1 CUP - 2 nhân hiện tại

Việc MySQL chạy chậm đi trên CPU 4 nhân mình đã nghe nói từ lâu . Nhưng đến giờ mới được thử nghiệm .

MySQL Results: Scaling

Back to our main subject, our astute readers have probably already noticed a weird anomaly. Let us analyze this further. If you look closely at both our measurements, Quad-core and Dual-core x86, you'll notice that the scaling is negative. To make it more clear, we made an average of all concurrency numbers from 5 and higher.

This is nothing short of amazing. It seems like an anomaly, but this is not the case. These benchmarks have been checked, verified and checked again. They are accurate. The x86 cores running on Linux perform better with two cores than with four cores, but the T1 running Solaris actually improves performance going from 4 to 8 cores.

So who is guilty? Linux or the Opteron system? We had to test with Solaris on the Opteron to be sure. However, the Serverworks chipset of our MSI 1U server was not supported by x86 Solaris. So we went back to our homebuilt server, based on the MSI K8N Master2-FAR.

And this puts the performance of our UltraSparc T1 in a whole different perspective. First of all, it is clear that while MySQL might not be the most scalable database, the current kernel of Linux is not helping matters. We did tweak the Linux kernel two ways: the 2.6.15 kernel was optimized for either Intel's or AMD's architecture and the AMD architecture also got NUMA support.

So what is going on here? After talking to our MySQL guru (P. Zaitsev), it turns out that in some circumstances, MySQL might cause trouble for the Linux mutex (mutual exclusion) implementation: "mutex ping-pong". The mutex implementation makes sure that two threads cannot access data in the main memory that is locked by another thread.

It seems however more a MySQL problem than a Linux one, as other databases like DB2 scale very well in Linux. For DB2 under the same load we noticed a performance increase of no less than 80-85% when going from two to four cores. Also, with some loads, the bad scaling kicks in later than our "Select dominated" load. Intel's performance labs told us that they also ran into the same problem.

These issues are not as severe as the problems we encountered with MySQL in Mac OSX. Note that Apple seems to have recognized the problem and seems to offer a workaround. We'll report back with other MySQL workloads to investigate the MySQL scaling problem further.

PostGreSQL Results

PostgreSQL 8.0.7, another open source database, uses processes and not threads to deal with connections. The consequence is that the benchmark numbers are a lot more stable: once each core is busy with it's process, you almost get maximum performance. In other words, the results didn't change much from 5, 10 or 25 concurrent users. To keep things simple, we only list the numbers with 20 users, which results in peak performance. The queries per second numbers at 5 and 25 were only a few percent lower. We did not include the T2000 Sun Server as the optimal PostGreSQL configuration is still under investigation.

Another clear victory for Woodcrest. On the Opteron, every 10% in clockspeed increase seems to result in a 7% performance increase. So if we extrapolate, an Opteron 3 GHz would arrive at 616 queries per second.

Mặt khác trong lúc thử nghiệm , lỗi TLB vẫn dính với Phenom mặc dù đã có thông tin là đã được fix .Theo thông tin trên trang anandtech thì Mysql 5.1 sẽ được fix , đành chờ vậy . Dù sao cũng chưa cần thay server , để con server mới (2 CPU - 4 nhân) làm proxy server vậy

Mọi người chú ý không nên mua server quá tốt cho LAMP , LAMP mạnh là nhờ sử dụng phần cứng có giá rẻ .

Có thể nghĩ đến chuyện mua máy từ quán net công cộng về lám data center chạy LAMP . Nếu thừa tiền thì đầu tư vào hạ tằng mạng ,đặc biệt là load balancer (phần cứng tốt hơn phần mềm).