MySQL Forums
Forum List  »  InnoDB

Re: VERY slow InnoDB writes (in the teens/sec)
Posted by: Aram Mirzadeh
Date: November 09, 2013 12:27AM

>> innodb_buffer_pool_size = 16M
>Change to, say, 1G

See below first, but I did this afterwards.

>> innodb_flush_log_at_trx_commit = 1
> Change to 2 -- speed versus security.

What a huge difference, down to 3.1 seconds for every 1600 batched rows.

>> upgraded ... to 5.6.14
> From what? Were your tables previously MyISAM?

5.1.(6?) I think. I have no MyISAM tables in this database, they're all Innodbs.

>> remotely tries to insert several thousand rows of data in 1600 row batches
> Good.

Much Much better results. More in tune of what I was seeing before. I had not increased the memory, but I'll do that after this test to see if there is even further increased (and yes, the difference after the memory is slight, but it's there as well -- down to 2.6 seconds between batches )

>> Opened tables 26375
Ouch! Does no make sense. Anyway, increase this to, say 500:
>> table_open_cache = 64

Done.

> It took 18 seconds to alter two columns
Is the table partitioned?

No, simple one host/db/table - it's a physical CentOS with the MySQL binaries from mysql's site.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: VERY slow InnoDB writes (in the teens/sec)
1606
November 09, 2013 12:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.