HandlerSocket causes disk IO?

MySQL & MariaDB Other MySQL® Questions
1

Hello,

We use MySQL Percona Distrib 5.5.16, for Linux (x86_64)
in our highly loaded project.

  • The server is in Slave mode (data is inserted only through replication from master)
  • There is only one table in the database (no joins)
  • 100% are R/O transactions ( ~ 50 000 transactions per second)
  • All data fit in MySQL memory cache (96GB RAM)

Some time ago we changed a part of our MySQL queries into the
HandlerSocket protocol. The queries are all simple R/O selects by key.

After this switch we gained a lot in terms of response time CPU and traffic
for these queries. On the other hand we have some more complicated select
queries which cannot be processed through HandlerSocket protocol.
The duration for these queries raised approximately for 20%.

Also we noticed two times disk utilization increase on MySQL servers
after switching to HandlerSocket (from 4% to 8%).

I am trying to understand:

  • What could cause the duration and disk utilization increase?
  • Is it possible to do some MySQL fine tunning to avoid that?
  • Which tools could I use for diagnostics?

Any advices are highly appreciated.

Thanks in advance.