On Fri, 26 Apr 2013, James Harper <james.harper@bendigoit.com.au> wrote:
The system load is ~1.15, and mysqld is highest on the list of processes but is only 1-2%, which I assume means that the cpu is being blocked somewhere due to IO (?).
Disk write performance inside the VM where Bacula and mysql runs is around 90MB/second, which is more than sufficient to insert a few hundred thousand rows.
iostat while all this is going on shows pretty much nothing happening.
What does iostat show for %util? If the overall speed is low but %util is near 100% then it usually means a storage hardware problem.
I don't see a %util. This is an average sort of output of iostat 1: avg-cpu: %user %nice %system %iowait %steal %idle 0.00 0.00 0.00 4.04 0.00 95.96 Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn xvdap1 91.00 0.00 204.00 0 204 xvdap2 0.00 0.00 0.00 0 0 %iowait varies from 0-20% (0% most of the time) %user varies from 0-2% (<1% most of the time) %system varies from 0-5% (~2% most of the time)
Also 90MB/s isn't the issue for most database stuff, the issue is the number of synchronous operations. Of course for a temporary table it probably shouldn't be synchronising the data, Arjen?
My point of including that figure is that it matches the baseline when the server was set up. IO performance hasn't dropped in any way I can measure. When I have a database issue like this I normally look at indexes and query plans and stuff, but of course this is a temporary table and not visible outside the process that created it, or at least as far as I can tell, so I can't easily test it. And this problem came on suddenly, without any change that I'm aware of. I've seen that happen before where database queries work fine for a few years until the database grows to the point where it can no longer fit the required information (indexes etc) in memory and performance drops off very quickly, but this particular operation is purely temporary tables which exist in isolation, and there is nothing else going on on the system at the same time. I'm looking up how to migrate from mysql to postgresql, which is something I have wanted to do for a while anyway, but that may not be straightforward for an established Bacula database... James