On 26/04/13 11:59, James Harper wrote:
On Fri, 26 Apr 2013, James Harper <james.harper@bendigoit.com.au> wrote:
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:
Run "iostat -x 1" and you'll get it.
I'm doing a test conversion to postgresql now, although swapping back is easy enough as the old mysql database is intact.
While doing the conversion which is basically a bulk insert into postgresql database, %util is indeed high - 95-100% in the VM and mostly >50% on the physical machine for both raid1 member disks, even though the iops remain low.
So my first guess is that it's probably not a problem with the actual disks - both disks appear equally affected and SMART reports nothing out of the ordinary. I'll migrate some VM's and reboot the physical machine, which I thought I'd already done but uptime says otherwise.
Any other suggestions?
When doing bulk inserts on PostgreSQL, you'll find it goes a lot faster if you do them all inside a transaction. For more speed, use the 'COPY' command rather than INSERT, as it's specifically designed for bulk loading. You can also drop the indexes at the start and recreate them at the end. (Which if done inside a transaction, will not drop them for other users) You can disable foreign key checks, or at least defer them all until the end of the transaction, which is quicker than having them checked per-insert. You can disable synchronous writes for your query. However as Ari pointed out too -- databases are quite capable of doing a LOT of inserts per second, even before applying the above optimisations. If it's running noticeably slowly then there's probably something wrong elsewhere, in the DDL or the program. -T