Database / System Admin

[James Mills]

Hi guys.

I am currently working with a database which has a table with over 30 million rows in it. I have a couple of stored procedures (SP) that I am trying to run on it. We have a virtual machine setup with multiple processors, about 8GB of RAM and loads of disk space. 

When I leave the SP to run eventually it fails and I get a message along these lines 'Incorrect key file for...'. I have done research into this and it looks like it is connected with either the innodb_buffer_pool_size or space running out in the temporary file system.

I have set the innodb_buffer_pool_size to 6GB in the my.cnf file but I am not 100% its getting updated. I have also looked at altering which folder is being used for the temporary storage. I am still getting errors. At the moment we are using Ubuntu 64bit on a Citrix XenServer virtual machine. 

I am looking to connect with someone who would do a spot of consultancy for me (through the company I work for, Thap Ltd.). Hopefully someone who is experienced with working with large tables and who has had to setup/adjust server settings like this in the past.

Thanks, James 

[Alistair MacDonald]

This sounds like the sort of thing the Smith Electric team could help
with. They have silly sized My SQL databases they work with. Perhaps
it is worth posting on the Super Monday's list.

Alistair

[cheeryfella]

Hey James,

What storage engine you using? does the procedure work on ALL the data or can you split it up any? What indexes are in place and does the stored prod use fields not indexed...

[cheeryfella]

PS.

Awesome sysadmin guy says you may have to run REPAIR TABLE to rebuild indexes...

Try quick repair 1st otherwise a full repair - this will lock table so if this is in the wild it won't be readable for while...

On Monday, 16 July 2012 12:19:10 UTC+1, James Mills wrote:

On Monday, 16 July 2012 12:19:10 UTC+1, James Mills wrote:

On Monday, 16 July 2012 12:19:10 UTC+1, James Mills wrote:

On Monday, 16 July 2012 12:19:10 UTC+1, James Mills wrote:

[Sam Lambert]

Repair table will not work on InnoDB as it is not supported. 

I assume James is using InnoDB as he mentions changing the  innodb_buffer_pool_size which will only effect InnoDB tables.

If you want to rebuild an index on an InnoDB table just issue: alter table <tablename> ENGINE=InnoDB;

This will lock your table for a while though and in this case is unlikely to make a difference. 

James ->  I am at phpNE tonight if you want a chat about this issue.

Also we have an irc channel now on freenode called #phpNE so if anyone needs advice etc there are some really smart people hanging out in there and Jamie.

Sam