• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Slower Performance

 
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
NLB7Run.PNG
Application running on NLB7
Application running on NLB7
NLB8Run.PNG
Application running on NLB8
Application running on NLB8
 
Neil Barton
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
VariableNLB7NLB8
host_cache_size628179
innodb_buffer_pool_chunk_size1342177288388608
innodb_buffer_pool_size1342177288388608
innodb_log_buffer_size167772161048576
innodb_open_files4000300
innodb_thread_concurrency033
innodb_thread_sleep_delay100000
innodb_version8.0.138.0.20
max_allowed_packet671088644194304
max_connections512151
myisam_max_sort_file_size2146435072107374182400
myisam_sort_buffer_size8388608512753664
open_files_limit105706209
performance_schema_error_size44684697
read_buffer_size13107265536
table_definition_cache20001400
table_open_cache40002000
thread_cache_size1310
tmp_table_size16777216262144000
version8.0.138.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?
 
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
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic