I have noticed quite a few queries appear in the slow query log. I then do a explain on those queries, however, the execution plan for these queries look OK to me.
This is one of the query:
select handset0_.id as id0_, handset0_.version as version0_,
handset0_.imei as imei0_, handset0_.model as model0_,
handset0_.variant as variant0_
from handset handset0_
where handset0_.id=9200;
(please execute the weired column naming as it’s a prepared statement from Hibernate.)
For MyISAM tables it could also be table locking for Innodb trivial queries in slow query logs usually mean system overload at that point in time. For example query could be waiting in queue to enter Innodb kernel.
for Innodb trivial queries in slow query logs usually mean system >overload at that point in time. For example query could be waiting >in queue to enter Innodb kernel.
Then what areas can I look at to further troubleshoot this?
Also After reading the show-innodb-status-walk-through page, I am thinking to do make the following changes:
increase innodb_sync_spin_loops from 20 to 40 since we were only using 14% of CPU on the db server when we were doing load testing.
decrease innodb_thread_concurrency from 8 to 4 as we only have 2CPUs on the db server.
I have allocated 1.6G for innodb buffer pool and the innodb status output indicates the buffer pool hit reate is 100%, therefore, I believe the memory that’s allocated is sufficient (correct me if I am wrong)
============================================================ ==
Total memory allocated 1878242646; in additional pool allocated 9884672
Buffer pool size 102400
Free buffers 79286
Database pages 20660
Modified db pages 23
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 18419, created 2241, written 337383
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
============================================================ ==
I think it’s more likely to be a disk issue as we are using NFS ( I got handled over this system which already has NFS configured and been told it’s not possible to change it at the moment).
However, I didn’t recall the iostat shows high disk utilisation – I will go back and re-collect the stats.