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.
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.
On Tuesday, 17 July 2012 at 16:49, cheeryfella wrote:
> 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:
> > 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
> On Monday, 16 July 2012 12:19:10 UTC+1, James Mills wrote:
> > 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
> On Monday, 16 July 2012 12:19:10 UTC+1, James Mills wrote:
> > 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
> On Monday, 16 July 2012 12:19:10 UTC+1, James Mills wrote:
> > 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
