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


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


 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).

  • could you please show us the output of "free-m", "vmstat", "uptime", "sudo /etc/init.d/mysql status" – Rui F Ribeiro Nov 28 '15 at 13:33
  • @RuiFRibeiro I've added the requested information, looks like I've had a higher proportion of slow queries since the last restart, with somewhat lower traffic than normal, could those be linked? The delays still seem pretty long for a server that's going idle though. – Haravikk Nov 29 '15 at 12:21
  • Very long indeed, I would venture to say you have application problems. Sorry for the delay, full of work. Add to your /etc/mysql/my.cnf "log_slow_queries = /var/log/mysql/mysql-slow.log" and do "sudo server mysql restart", and run it for a while. Do not leave this option on in a production system. – Rui F Ribeiro Dec 2 '15 at 7:17
  • I've already tried the slow query log; it doesn't seem to reveal any obvious causes for the delays. Even when the query takes several seconds to complete, lock-time is <1ms, and EXPLAIN shows the queries are using short indexes (mostly integer primary keys). – Haravikk Dec 3 '15 at 11:22
  • I find it odd it did not show anything, when in the status it shows 1089 slow queries. – Rui F Ribeiro Dec 3 '15 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.