I have 2 machines, NLB7 is a core i7 with SSD drives. I also have NLB8 which is a core i9 with M2 drives. NLB8 has always been faster (unsurprisingly). Suddenly something has changed and I'm not sure what. I had some crashes on NLB8 and upgraded the MySQL server to the latest release, that took a long time! Now when I run my application (which makes heavy use of the DB) it runs about 49 SQL statements per second and is very slow. The older NLB7 which should be slower runs 575 statements per second. What has happened to NLB8 to make it suddenly run so slow. In an effort to speed it up I have updated the BIOS, INF and ME drivers on the motherboard but nothing makes any difference. I have also tried changing the innodb_buffer_pool_size to 26000000000 but that doesn't have any impact either (the machine has 32gb of ram). What can I look at to help tune the actual DB itself, it runs fine on NLB7 so it's not the SQL.
posted 4 months ago
Follow up: I noticed the CPU was only at 25% on NLB8 so I increased the number of threads in my app to 22 (after some trial and error), this has got the CPU and Disk to 100%. NLB7 is running with 6 threads. The number of Selects per second is now about 70 but it is still nowhere near as fast as the NLB7 although the spec is vastly better. It seems to be something to do with the configuration of MySQL but I'm not sure where to start with that. I had a look at the MySQL System variables on each machine. The results are attached in the VarDiffs file.
Any advice would be appreciated.
It looks like it's throttling threads for some reason but overall it looks like I've configured it as a small server rather than one which handles mutliple threads, is there some sort of system wide setting that I need to change? Or was there a decision I got wrong when installing? It is an innodb.
I tried adding innodb_thread_concurrency=0 to my.ini but that made no difference and the server still reports that it is 33! Reading around the internet there are suggestions that this is only for Solaris systems and it should be 0, but how do I set it to 0?
Further update: after some messing around I set innodb_thread_concurrency to 0 and innodb_buffer_pool_size to 26000000000 (80% of the 32gb or ram). I got the processing up to about 2400 statements per second! I did this by opening a MySQL session and changing the variables using SET GLOBAL innodb_thread_concurrency=0. But when I restart MySQL I lose the two settings. Can I put these changes into my.ini so that they are always in place? I have tried several syntax variations but nothing makes any difference. I have also tried using 'persist' in the setting statement but it doesn't accept it.
It also still says in MySQL Workbench that key efficiency is 0. I know it isn't but can I get this to update on the screen somehow?
Wait for it ... wait .... wait .... NOW! Pafiffle! A perfect tiny ad!
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop