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.
| Variable | NLB7 | NLB8 |
|---|
| host_cache_size | 628 | 179 |
| innodb_buffer_pool_chunk_size | 134217728 | 8388608 |
| innodb_buffer_pool_size | 134217728 | 8388608 |
| innodb_log_buffer_size | 16777216 | 1048576 |
| innodb_open_files | 4000 | 300 |
| innodb_thread_concurrency | 0 | 33 |
| innodb_thread_sleep_delay | 10000 | 0 |
| innodb_version | 8.0.13 | 8.0.20 |
| max_allowed_packet | 67108864 | 4194304 |
| max_connections | 512 | 151 |
| myisam_max_sort_file_size | 2146435072 | 107374182400 |
| myisam_sort_buffer_size | 8388608 | 512753664 |
| open_files_limit | 10570 | 6209 |
| performance_schema_error_size | 4468 | 4697 |
| read_buffer_size | 131072 | 65536 |
| table_definition_cache | 2000 | 1400 |
| table_open_cache | 4000 | 2000 |
| thread_cache_size | 13 | 10 |
| tmp_table_size | 16777216 | 262144000 |
| version | 8.0.13 | 8.0.20 |
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?
