I've set up a MariaDB server and everything is running well, with most queries executing in well under 20 milliseconds, but every so often a query will take half a second or longer (sometimes several seconds) for no reason that I've been able to fathom.
I know that >0.5 seconds may not seem a long delay, but if I execute a similar query it will complete with no discernible delay at all so the query itself doesn't seem to be flawed. I'm using the slow_query_log to track delayed queries, but it's not showing any obvious culprits; lock-times of delayed queries are <1ms, indexes are being used with short key lengths, few rows are affected etc.
My setup is Ubuntu Server 14.04 with MariaDB 10.0.22, mostly vanilla settings. I do have the binary log enabled (in MIXED mode) so I can setup replication later, but I've observed the problem with it disabled too. The database is storing data for a low impact PHP application running on the same server, but I don't receive more than a couple of dozen simultaneous connections; while these may affect a single table, none affect the same row, and all my tables are InnoDB (XtraDB on MariaDB I believe), so locking shouldn't be an issue and the slow query log appears to back this up.
The queries that are being delayed are all UPDATE queries, but targeting only single rows using a short (integer) primary key, only perhaps 1 in 100 is being delayed, but there are a lot of such queries made regularly throughout the day. All fields are of fixed length (no blobs or text fields, only integers and short varchars).
It's the relatively disparity on the times (<20ms to 3s+) that has me puzzled, as I can't see any reason for it even when dealing with uncached tables and the like. I can't see any obvious spikes in performance from other programs either; I do run daily backups that sometimes slow things down a little, but these run at a fixed time, while the slow queries are occurring through the entire day. I'm using HHVM for running PHP scripts in case that's relevant, but I've found its memory and CPU usage far more stable than vanilla PHP, and the performance is excellent (when not delayed by slow queries) so it doesn't seem an obvious culprit, certainly not for delays of several seconds, as it has the same priority as MariaDB.
Is there anything more I can do to debug this, any possible MariaDB settings that may need to be tweaked?
[edit] As requested by Rui F Ribeiro, here's some additional stats; it's a fairly modest server, but very lightly utilised:
free -m:
total used free shared buffers cached
Mem: 993 768 225 8 83 304
-/+ buffers/cache: 380 613
Swap: 0 0 0
vmstat:
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 231752 85788 311716 0 0 15 38 30 32 0 0 99 1 0
uptime:
12:12:24 up 11 days, 21:32, 1 user, load average: 0.11, 0.08, 0.06
sudo /etc/init.d/mysql status:
* /usr/bin/mysqladmin Ver 9.1 Distrib 10.0.22-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Server version 10.0.22-MariaDB-1~precise-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 day 16 hours 23 min 23 sec
Threads: 1 Questions: 32381 Slow queries: 1089 Opens: 92 Flush tables: 5 Open tables: 17 Queries per second avg: 0.222
Since mysql was last restarted it looks like I've had a lot more than 1 in 100 slow queries (as the average is way up). It's also worth noting that I use persistent connections (which should account for the low number of opens), via PHP's MySQLi interface (so while persistent, locks etc. are freed automatically).
