I am facing weird behavior in our MySQL DB and need your expert guidance on this.
We have a query (simple select without join, usages index as well) which is executes in 5.8 seconds However, when we increase the load / concurrency (either by application or by mysqlslap ) its elapsed time increases up to 44 seconds.
I can also see the same behavior of increased timeline even for "select now()" using concurrency.
Is the "increased timeline with increasing load" is something which is default behavior for MySQL or I am missing any config in MySQL
This is dedicated server for MySQL DB (community edition) version: 5.7.21-log core: 16 RAM: 128GB
innodb_thread_concurrency=20 innodb_read_io_threads=20 innodb_log_file_size=5G innodb_log_buffer_size=204217728 innodb_buffer_pool_size=72G
If I set innodb_thread_concurrency=0, CPU reaches to 99% but performance goes down as well.
Please suggest what should I look into further.