MySQL innodb buffer pool size issues

I’m currently archiving some database records from our AVS logging platform. My previous technique was to rename the logging table and create a new one, leaving the renamed table to be cut up as required.  I had to do it like this because the tables were all MyISAM – the default for MySQL, but despite its many advantages did table locking rather than record locking when executing my maintenance queries. A quick squiz through the MySQL engine types and it seemed that innodb would do the job as it was record locking. I changed the logging table to innodb like this:

ALTER TABLE spamscanlogs ENGINE=InnoDB;

…And let the process carry on for a few months. When i ran my query to copy the records by month into the archive tables all seemed well, the table was not backing up queries for 15 mins while the query ran. The query is similar to this:

INSERT INTO 2011SEP SELECT * FROM temptable WHERE SpamTimeStamp > '2011-09-01' AND SpamTimeStamp < '2011-10-01';

That should get about 2-3GB of records from around 12GB (around 4 million records from around 20 million records) so the query is quite large and with a MyISAM table can take 15 - 20 mins on our hardware. However I was getting the following error when i tried to execute the query:

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Looks like i need to increase the innodb buffer pool size. I am running a server with 4GB of RAM so I'll set it to 2GB, up from the default of 8MB and see how it goes. Edit the my.cnf file and add the following line:

[mysqld]
innodb_buffer_pool_size = 2G

MySQL needs a restart to make this happen. Now I've swapped out the table again so the big table is now called temptable and spamscanlogs is a nice new pretty much empty table. Now working on the temptable...

mysql> INSERT INTO 2011SEP SELECT * FROM temptable WHERE SpamTimeStamp > '2011-09-01' AND SpamTimeStamp < '2011-10-01';
Query OK, 3724419 rows affected (40 min 26.70 sec)
Records: 3724419  Duplicates: 0  Warnings: 0

Its a lot or rows, 3.7 million but not very fast! I'l do the next one but i expect it to be faster if the table is cached now.

This entry was posted in FreeBSD Administration, MySQL and tagged , , , , . Bookmark the permalink.

Leave a Reply