Re: Possible to delay index writes until server is less busy?
[EMAIL PROTECTED] wrote: Write to a memory table first then do a hotcopy on a scheduled basis. I'll look into that. Thanks for your reply. - Mathias - Original Message - From: Mathias [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 30, 2005 9:10 AM Subject: Possible to delay index writes until server is less busy? We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) The biggest problem now is if the indexes are created when the server is very busy. If there was a way of telling MySQL to delay creating the indexes when it is busy, then a big obstacle would be out of the way. Is this possible? We could not find anything in the MySQL documentation concerning this. Any suggestions would be greatly appreciated. Kind regards, Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
Dan Nelson wrote: In the last episode (Jun 30), Mathias said: We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) You want the delay_key_write flag. You can set it per-table, or globally. You can use the FLUSH TABLE mytable command to force mysql to update the on-disk copy of the indexes. http://dev.mysql.com/doc/mysql/en/create-table.html http://dev.mysql.com/doc/mysql/en/myisam-start.html http://dev.mysql.com/doc/mysql/en/flush.html Yes, that is something we are considering doing. Any suggestions though how to best decide when to do this? As far as we know, there is no way of determinining from within MySQL whether it is very busy or not. I guess we need to determine that externaly before running the queries - Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
Write to a memory table first then do a hotcopy on a scheduled basis. Do you mean converting the memory table into MyISAM on a scheduled basis? (mysqlhotcopy only works on MyISAM and ISAM tables). Or is there a faster way of storing the table to disk? - Mathias We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) The biggest problem now is if the indexes are created when the server is very busy. If there was a way of telling MySQL to delay creating the indexes when it is busy, then a big obstacle would be out of the way. Is this possible? We could not find anything in the MySQL documentation concerning this. Any suggestions would be greatly appreciated. Kind regards, Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
In the last episode (Jul 01), Mathias said: Dan Nelson wrote: In the last episode (Jun 30), Mathias said: We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) You want the delay_key_write flag. You can set it per-table, or globally. You can use the FLUSH TABLE mytable command to force mysql to update the on-disk copy of the indexes. Yes, that is something we are considering doing. Any suggestions though how to best decide when to do this? As far as we know, there is no way of determinining from within MySQL whether it is very busy or not. I guess we need to determine that externaly before running the queries If you don't mind the extra time required to check/repair damaged indexes after a system crash, you don't really need to flush at all. Or, if you aren't using hardware raid, consider getting one with battery-backed RAM (which will let it cache writes). That way your index writes will return immediately even with delay_key_write unset, and the raid card will flush to disk at its leisure. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
Write to a memory table first then do a hotcopy on a scheduled basis. - Original Message - From: Mathias [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 30, 2005 9:10 AM Subject: Possible to delay index writes until server is less busy? We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) The biggest problem now is if the indexes are created when the server is very busy. If there was a way of telling MySQL to delay creating the indexes when it is busy, then a big obstacle would be out of the way. Is this possible? We could not find anything in the MySQL documentation concerning this. Any suggestions would be greatly appreciated. Kind regards, Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
In the last episode (Jun 30), Mathias said: We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) You want the delay_key_write flag. You can set it per-table, or globally. You can use the FLUSH TABLE mytable command to force mysql to update the on-disk copy of the indexes. http://dev.mysql.com/doc/mysql/en/create-table.html http://dev.mysql.com/doc/mysql/en/myisam-start.html http://dev.mysql.com/doc/mysql/en/flush.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
