Re: Reducing ibdata1 file size
Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.comwrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.orgwrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Right now one tablespace datafile. But does it matters if i have one file per table. *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Changes, you have a lot of changes. You change the equivalent of 1 gb data a day. Every change is similar to an insert and every delete does not free any disk space. No way to reduce this rate unless you change the app logic. More info needed to provide a hint. Claudio On May 22, 2012 10:50 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.comwrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
Hi Reindl Harald, Does this means that if we have a single tablespace with file per table and doing the optimization will reduce the size of the datafile size ? If yes, then why this not possible on the datafile (one single file) too ? * * *thanks regards,* *__* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 3:07 PM, Reindl Harald h.rei...@thelounge.netwrote: as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Re: Reducing ibdata1 file size
Yes, Looks like there is lot of fragmentation. Is this production or dev/qa. You need to take a dump, drop database, make changes to parameter file to have file per table and restore the dump, so that each table will have its own .idb files, and you can easily manage the space. regards anandkl On Tue, May 22, 2012 at 2:58 PM, Kishore Vaishnav kish...@railsfactory.orgwrote: Right now one tablespace datafile. But does it matters if i have one file per table. *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
single file will not release the space to OS, it will continue to hold the space got from drop or truncate and used only for the database On Tue, May 22, 2012 at 3:20 PM, Kishore Vaishnav kish...@railsfactory.orgwrote: Hi Reindl Harald, Does this means that if we have a single tablespace with file per table and doing the optimization will reduce the size of the datafile size ? If yes, then why this not possible on the datafile (one single file) too ? * * *thanks regards,* *__* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 3:07 PM, Reindl Harald h.rei...@thelounge.net wrote: as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Re: Reducing ibdata1 file size
because this is simply the way InnoDB is implemented as multiple said the default of a single table space is idiotic in my opinion, but however this is well known over years google: mysql innodb reduce datafiles Am 22.05.2012 11:50, schrieb Kishore Vaishnav: Does this means that if we have a single tablespace with file per table and doing the optimization will reduce the size of the datafile size ? If yes, then why this not possible on the datafile (one single file) too ? On Tue, May 22, 2012 at 3:07 PM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com mailto:anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org mailto:kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com mailto:claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org mailto:kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be mailto:vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com mailto:manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Pothanaboyina Trimurthy skd.trimur...@gmail.com hi sir, Please keep the list in CC, others may benefit from your questions, too. can we see any performance related improvements if we use innodb_file_per_table other than using a single ibdatafile for all innodb databases. Please let me know the difference sir. No, that shouldn't yield any particular performance benefit, as far as I'm aware. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net as multiple said the default of a single table space is idiotic in my opinion, but however this is well known over years I suppose there's a certain logic to favouring one-shot allocation and never giving up free space, in that it reduces on-disk fragmentation. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
yes, there some new features you can use to improve performance. If you are using mysql 5.5 and above, with files per table, you can enable BARACUDA file format, which in turn provides data compression and dynamic row format, which will reduce IO. For more benefits read the doc On Tue, May 22, 2012 at 4:45 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Pothanaboyina Trimurthy skd.trimur...@gmail.com hi sir, Please keep the list in CC, others may benefit from your questions, too. can we see any performance related improvements if we use innodb_file_per_table other than using a single ibdatafile for all innodb databases. Please let me know the difference sir. No, that shouldn't yield any particular performance benefit, as far as I'm aware. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Am 22.05.2012 13:19, schrieb Johan De Meersman: - Original Message - From: Reindl Harald h.rei...@thelounge.net as multiple said the default of a single table space is idiotic in my opinion, but however this is well known over years I suppose there's a certain logic to favouring one-shot allocation and never giving up free space, in that it reduces on-disk fragmentation. well but for what price? the problem is the DEFAULT users with enough knowledge could easy change the default currently what is happening is that mostly every beginner is caught in the trap with single-table-space and hearing from innodb_file_per_table after is is way too late __ BTW: From: Pothanaboyina Trimurthy skd.trimur...@gmail.com hi sir, Please keep the list in CC, others may benefit from your questions, too NO - do NOT set any CC send lists to a mailing-list ONLY to the list-address with this idiotic list as cc you are breaking reply-to-list button for every user with a well working mail-client signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
In regards to why the file grows large, you may wish to read some of the posts on the MySQL Performance Blog, which has quite a bit of information on this, such as http://www.mysqlperformanceblog.com/2010/06/10/reasons-for-run-away-main-innodb-tablespace/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
- Original Message - From: Ananda Kumar anan...@gmail.com yes, there some new features you can use to improve performance. If you are using mysql 5.5 and above, with files per table, you can enable BARACUDA file format, which in turn provides data compression and dynamic row format, which will reduce IO. True, but Barracuda isn't limited to file-per-table, is it? Also, while it's true that compression will lighten your I/O load, do not forget that it does so at the cost of additional CPU load. There's no such thing as a free lunch :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Reducing ibdata1 file size
yes, Barracuda is limited to FILE_PER_TABLE. Yes, true there is CPU cost, but very less. To gain some you have to loss some. On Tue, May 22, 2012 at 5:07 PM, Johan De Meersman vegiv...@tuxera.bewrote: -- *From: *Ananda Kumar anan...@gmail.com yes, there some new features you can use to improve performance. If you are using mysql 5.5 and above, with files per table, you can enable BARACUDA file format, which in turn provides data compression and dynamic row format, which will reduce IO. True, but Barracuda isn't limited to file-per-table, is it? Also, while it's true that compression will lighten your I/O load, do not forget that it does so at the cost of additional CPU load. There's no such thing as a free lunch :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net Subject: Re: Reducing ibdata1 file size well but for what price? the problem is the DEFAULT users with enough knowledge could easy change the default currently what is happening is that mostly every beginner is caught in the trap with single-table-space and hearing from innodb_file_per_table after is is way too late All true, but I would argue that enterprise-suitable defaults are a good thing - shame so many of the other defaults aren't :-) And yes, it is problematic for beginners, but so is having beginners manage production sites, isn't it? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Am 22.05.2012 13:40, schrieb Johan De Meersman: - Original Message - From: Reindl Harald h.rei...@thelounge.net Subject: Re: Reducing ibdata1 file size well but for what price? the problem is the DEFAULT users with enough knowledge could easy change the default currently what is happening is that mostly every beginner is caught in the trap with single-table-space and hearing from innodb_file_per_table after is is way too late All true, but I would argue that enterprise-suitable defaults are a good thing shame so many of the other defaults aren't :-) 95% of mysqld-installations have no problem with innodb_file_per_table so DEFAULTS should not be for 5% and as said in another reply to this thread you need innodb_file_per_table for compression and no, most servers these days are NOT cpu-bound most are IO-bound especially in enterprise environments with shared storage (SAN) And yes, it is problematic for beginners, but so is having beginners manage production sites, isn't it? not only for beginners very few people expecting that a datastorage is only growing and growing with no painless way to release all the wasted space if you make tests with real big data and drop them this has nothing to do with enterprise or beginners it is a surprising behavior and defaults should minimize surprises signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Ananda Kumar anan...@gmail.com yes, Barracuda is limited to FILE_PER_TABLE. Ah, I didn't realise that. Thanks :-) Yes, true there is CPU cost, but very less. To gain some you have to loss some. I've only got it enabled on a single environment, but enabling it added about 20% of a single vcore to the CPU usage. Very visible (and not problematic) because that system isn't CPU-bound :-) Converting an existing table to the compressed format did shoot the CPU through the roof, though. See http://www.tuxera.be/filestore/vefeuraxinie/mysql-cpu-year.png for an interesting graph. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Reducing ibdata1 file size
Am 22.05.2012 13:52, schrieb Johan De Meersman: - Original Message - From: Ananda Kumar anan...@gmail.com yes, Barracuda is limited to FILE_PER_TABLE. Ah, I didn't realise that. Thanks :-) Yes, true there is CPU cost, but very less. To gain some you have to loss some. I've only got it enabled on a single environment, but enabling it added about 20% of a single vcore to the CPU usage. Very visible (and not problematic) because that system isn't CPU-bound :-) Converting an existing table to the compressed format did shoot the CPU through the roof, though. See http://www.tuxera.be/filestore/vefeuraxinie/mysql-cpu-year.png for an interesting graph interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) [--] Data in InnoDB tables: 6G (Tables: 49) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Reads / Writes: 90% / 10% [--] Total buffers: 4.1G global + 1.2M per thread (500 max threads) [OK] Maximum possible memory usage: 4.7G (54% of installed RAM) [OK] Slow queries: 0% (3/455M) [OK] Highest usage of available connections: 18% (93/500) [OK] Key buffer size / total MyISAM indexes: 128.0M/76.4M [OK] Key buffer hit rate: 98.6% (40M cached / 559K reads) signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net 95% of mysqld-installations have no problem with innodb_file_per_table so DEFAULTS should not be for 5% There is no problem, and there is better practice - and if your system is I/O bound it makes sense to minimize on-disk fragmentation. However, given the need for file-per-table to benefit from compression, I admit that it might make sense to change the default now. Would you say compression should be enabled by default, too? If you're aiming at diskbound systems, I'd think that it might make sense, too. it is a surprising behavior and defaults should minimize surprises Changing the default is a surprise, no? :-) Also, everything is a surprise if you don't read the fine manual. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Am 22.05.2012 13:59, schrieb Johan De Meersman: - Original Message - From: Reindl Harald h.rei...@thelounge.net 95% of mysqld-installations have no problem with innodb_file_per_table so DEFAULTS should not be for 5% There is no problem, and there is better practice and if your system is I/O bound it makes sense to minimize on-disk fragmentation. many systems are not CPU-bound nor IO-bound because they are well designed for their load However, given the need for file-per-table to benefit from compression I admit that it might make sense to change the default now. yes, one reason more Would you say compression should be enabled by default, too? no because with sane defaults (file_per_table) you can do this with one command, with the current defaults only with many work and downtime to switch If you're aiming at diskbound systems, I'd think that it might make sense, too no, defaults should be as flexible as possible and not optimized for special workloads it is a surprising behavior and defaults should minimize surprises Changing the default is a surprise, no? :-) no problem in a minor update 5.5.0 was a good moment 5.6.0 is the next good one Also, everything is a surprise if you don't read the fine manual please do not tell us that you have seen any single option of any software you are using BEFORE beause your systems would still be in theoretical stages a default causing the user to dump out all his data, switch it and pray while re-import is running and system is down is a worst-case default signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) Ah, this is a mixed-use server that also receives data from several Cacti installs. [--] Data in InnoDB tables: 6G (Tables: 49) [--] Data in InnoDB tables: 17G (Tables: 276) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Up for: 11d 23h 27m 20s (200M q [193.511 qps], 8M conn, TX: 132B, RX: 35B) [--] Reads / Writes: 90% / 10% [--] Reads / Writes: 18% / 82% I guess it's reasonable that I get a lot more CPU overhead from compression, as you get a lot of reads from decompressed blocks in the cache :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Is you system READ intensive or WRITE intensive. If you have enable compression for WRITE intensive data, then CPU cost will be more. On Tue, May 22, 2012 at 5:41 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) Ah, this is a mixed-use server that also receives data from several Cacti installs. [--] Data in InnoDB tables: 6G (Tables: 49) [--] Data in InnoDB tables: 17G (Tables: 276) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Up for: 11d 23h 27m 20s (200M q [193.511 qps], 8M conn, TX: 132B, RX: 35B) [--] Reads / Writes: 90% / 10% [--] Reads / Writes: 18% / 82% I guess it's reasonable that I get a lot more CPU overhead from compression, as you get a lot of reads from decompressed blocks in the cache :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
or it could be that your buffer size is too small, as mysql is spending lot of CPU time for compress and uncompressing On Tue, May 22, 2012 at 5:45 PM, Ananda Kumar anan...@gmail.com wrote: Is you system READ intensive or WRITE intensive. If you have enable compression for WRITE intensive data, then CPU cost will be more. On Tue, May 22, 2012 at 5:41 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) Ah, this is a mixed-use server that also receives data from several Cacti installs. [--] Data in InnoDB tables: 6G (Tables: 49) [--] Data in InnoDB tables: 17G (Tables: 276) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Up for: 11d 23h 27m 20s (200M q [193.511 qps], 8M conn, TX: 132B, RX: 35B) [--] Reads / Writes: 90% / 10% [--] Reads / Writes: 18% / 82% I guess it's reasonable that I get a lot more CPU overhead from compression, as you get a lot of reads from decompressed blocks in the cache :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
From: Claudio Nanni claudio.na...@gmail.com No, as already explained, it is not possible, Innodb datafiles *never* shrink. That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
- Original Message - From: Jan Steinman j...@bytesmiths.com That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. In brief: convert all your tables to myisam, delete ibdatafile during a restart, convert tables back to compressed innodb. My first thought is mentioned, but will be adressed in next post: you lose your relations. I gotta run now, but I wonder what other incompatibilities between the engines one might run into. For simple databases this would probably work, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Jan, that's not common wisdom, Innodb datafiles ***never*** shrink, that in the blog from 22th of May is a workaround, one of the many. If you ask my my favourite is to use a stand by instance and work on that. Claudio 2012/5/22 Jan Steinman j...@bytesmiths.com From: Claudio Nanni claudio.na...@gmail.com No, as already explained, it is not possible, Innodb datafiles *never* shrink. That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
RE: Reducing ibdata1 file size
Despite the conventional wisdom, converting to innodb_file_per_table will not necessarily help you. It depends on your situation. If most of your growth is in a single table, you will only have transferred the problem from the ibdata1 file to a new file. The ibdata1 file may also continue to grow, since innodb uses it for several kinds of temporary storage such as the insert buffer and the undo logs (AKA rollback segment). Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Okay, my mistake. I should write precisely when communicating with precise people. :-) What I meant was, dumping and importing is the common knowledge way of virtually shrinking innodb files. So, now that I've conceded the meta-argument, what do you think of the linked procedure for reducing innodb files? On 22 May 12, at 06:40, Claudio Nanni wrote: Jan, that's not common wisdom, Innodb datafiles ***never*** shrink, that in the blog from 22th of May is a workaround, one of the many. If you ask my my favourite is to use a stand by instance and work on that. Claudio 2012/5/22 Jan Steinman j...@bytesmiths.com From: Claudio Nanni claudio.na...@gmail.com No, as already explained, it is not possible, Innodb datafiles *never* shrink. That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio No man is so foolish but he may sometimes give another good counsel, and no man so wise that he may not easily err if he takes no other counsel than his own. He that is taught only by himself has a fool for a master. -- Ben Johnson Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Reducing ibdata1 file size
To shrink ibdata1: 1. Dump everything 2. Stop mysql 3. change to innodb_file_per_table = 1 4. change allocation in my.cnf (my.ini) to something smaller, say 50M,AUTOEXTEND 5. remove (rm / delete) ibdata1 6. restart mysql 7. reload data Step 3: innodb_file_per_table gives you more fine-grained control -- you can rebuild (ALTER TABLE) individual tables, thereby giving back unused space to the OS. Step 4: ibdata1 is still needed, but will probably not grow nearly as much once you have file_per_table. -Original Message- From: Manivannan S. [mailto:manivanna...@spanservices.com] Sent: Monday, May 21, 2012 6:04 AM To: mysql@lists.mysql.com Subject: Reducing ibdata1 file size Hi , I am trying to reduce the ibdata1 data file in MySQL. In MySQL data directory the ibdata1 data file is always increasing whenever I am creating a new database and inserting some data into database. If I drop the existing database, the table structures only dropped from the server but data still exist in the ibdata1 data file. How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. Do you have any idea how to solve this problem. Thanks for any feedback. Thanks Manivannan S DISCLAIMER: This email message and all attachments are confidential and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error, please notify us immediately by return email or to mailad...@spanservices.com and destroy the original message. Opinions, conclusions and other information in this message that do not relate to the official business of SPAN, shall be understood to be neither given nor endorsed by SPAN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Need help for performance tuning with Mysql
Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Need help for performance tuning with Mysql
Also following is the free command result. total used free sharedbuffers cached Mem: 81623807843676 318704 0 956325970892 -/+ buffers/cache:17771526385228 Swap: 8032492 235608008932 Thanks, Yu Yu Watanabe さんは書きました: Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Need help for performance tuning with Mysql
Hello, I seem your mysqld doesn't use enough memory. Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 if your mysqld uses InnoDB oftenly, edit innodb_buffer_pool_size in you my.cnf. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. It is solution for only sql's large result,i think. if you doesn't recognize that problem causes large result, you should approach other way,too. regards, ts. tanaka// 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com: Also following is the free command result. total used free sharedbuffers cached Mem: 81623807843676 318704 0 956325970892 -/+ buffers/cache:17771526385228 Swap: 8032492 235608008932 Thanks, Yu Yu Watanabe さんは書きました: Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Need help for performance tuning with Mysql
I don't see any attachments. First, I would upgrade to 5.5 as 5.0 is very old. The upgrade process is painless. Second, make sure your Innodb buffer pool is allocating as much ram as possible. I'd even go as far as adding another 8gb of ram to the server. The buffer pool setting is going to give you the best performance increase. Also, what kind of hard disks do you have the data files on? Raid? No raid? Sent from my iPad On May 22, 2012, at 9:08 PM, Yu Watanabe yu.watan...@jp.fujitsu.com wrote: Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Need help for performance tuning with Mysql
Hello Tsubasa. Thank you for the reply. (返信ありがとうございます。) Our high loaded DB are both INNODB and MyISAM. Espicially , on MyISAM. I will consider the tuning of innodb_buffer_pool_size as well. Do you know the tips for how to tune the disk access for MyISAM? Thanks, Yu Tsubasa Tanaka さんは書きました: Hello, I seem your mysqld doesn't use enough memory. Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 if your mysqld uses InnoDB oftenly, edit innodb_buffer_pool_size in you my.cnf. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. It is solution for only sql's large result,i think. if you doesn't recognize that problem causes large result, you should approach other way,too. regards, ts. tanaka// 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com: Also following is the free command result. total used free sharedbuffers cached Mem: 81623807843676 318704 0 956325970892 -/+ buffers/cache:17771526385228 Swap: 8032492 235608008932 Thanks, Yu Yu Watanabe さんは書きました: Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Need help for performance tuning with Mysql
Hello, Yu-san, (へろへろな英語で申し訳ないです) Can I think that you already tweaked Index on the tables? if you yet,please create apt indexes. MyISAM caches only Index without data. i take way for decreasing disk seek, 1) create more indexes on the tables,if the tables doesn't update quite often. including data into index forcibly. this makes slow for insert and update,and this is dirty idea,i think. (よくSELECTされるカラムをINDEXに含めてしまいます。 ただし、SELECT * FROMで呼ばれることが多い場合には使えない上に かなり美しくない策です。。) 2) tune filesystem and disk drive parameter for datadir. MyISAM table's data caches only in the filesystem cache. But i regret that i don't have knowledge around filesystem. あまり力になれなくて申し訳ないです。 regards, ts. tanaka// 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com: Hello Tsubasa. Thank you for the reply. (返信ありがとうございます。) Our high loaded DB are both INNODB and MyISAM. Espicially , on MyISAM. I will consider the tuning of innodb_buffer_pool_size as well. Do you know the tips for how to tune the disk access for MyISAM? Thanks, Yu Tsubasa Tanaka さんは書きました: Hello, I seem your mysqld doesn't use enough memory. Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 if your mysqld uses InnoDB oftenly, edit innodb_buffer_pool_size in you my.cnf. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. It is solution for only sql's large result,i think. if you doesn't recognize that problem causes large result, you should approach other way,too. regards, ts. tanaka// 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com: Also following is the free command result. total used free sharedbuffers cached Mem: 81623807843676 318704 0 956325970892 -/+ buffers/cache:17771526385228 Swap: 8032492 235608008932 Thanks, Yu Yu Watanabe さんは書きました: Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: [Puppet Users] Re: Announce: PuppetDB 0.9.0 (first release) is available
On Tue, May 22, 2012 at 12:02 AM, Marc Zampetti marc.zampe...@gmail.com wrote: Is Puppet Labs saying they are ending support of MySQL and instead will only support PostgreSQL? That is going to be a big problems for shops that do not support PostgresSQL, or are only allowed to run DB systems on an approved list. Why wouldn't a DB-agnostic model be used? Right now, I can say that due to these types of issues, I cannot even evaluate PuppetDB, and will not be able to for the foreseeable future. (cc'd the mysql list as I'm pretty sure the boys over there have some interest in this) As a provider of puppet consulting I can say it will be a harder sell to clients if we need them to use postgres instead of MySQL in order to use PuppetDB. It's not impossible of course, but introducing an additional barrier for puppet will give us additional trouble convincing our clients :) You mentioned degraded performance, do you have any numbers on what kind of performance degradation we are talking about? I wouldn't mind some degraded performance if that means we can keep smaller clients on MySQL. Also, have you looked at MariaDB 5.5? it is a drop-in replacement for MySQL with much better performance for any query optimiser related things (which I'm pretty sure the nested joins are also part of). -- Walter Heck -- Check out my startup: Puppet training and consulting @ http://www.olindata.com Follow @olindata on Twitter and/or 'Like' our Facebook page at http://www.facebook.com/olindata -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
