Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
As I can see the changes in these values are use by percona cacti monitoring templates to graph InnoDB I/O. Can anyone answer the question finally? ;) 2013/6/21 Hartmut Holzgraefe hart...@skysql.com On 21.06.2013 13:59, Rafał Radecki wrote: Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ has a pretty good description of the SHOW ENGINE INNODB STATUS output, even though it is not too detailed in this specific section. -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
space gone after MyISAM REPAIR TABLE
Hi guys, any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE command. the space on the hard drive gone down from 165 Gig to 70 Gig. I understand that during repair process MySQL creates temp file and remove it after the job done. Or removal process executes on the server restart? how can I get that space back? I can't check the table directory as I don't have root perm on that box. Thanks Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
- Original Message - From: nixofortune nixofort...@gmail.com Hi guys, any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE command. the space on the hard drive gone down from 165 Gig to 70 Gig. I understand that during repair process MySQL creates temp file and remove it after the job done. Or removal process executes on the server restart? how can I get that space back? I can't check the table directory as I don't have root perm on that box. Oops... Can you run [show global variables like 'innodb_file_per_table';] ? I kind of expect it to be OFF, which means that the temp table would have been created in the main tablespace. If that's the case, that space has been permanently assimilated by the global tablespace; the only way to get it back would be a full dump of all your (innodb) tables, stop server, delete tablespace, start server and import the data again. Be sure to read the documentation carefully before doing such an intrusive operation. While you're doing that, use the opportunity to set innodb_file_per_table to ON :-p -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
Hello Rafał, On 6/24/2013 4:26 AM, Rafał Radecki wrote: As I can see the changes in these values are use by percona cacti monitoring templates to graph InnoDB I/O. Can anyone answer the question finally? ;) 2013/6/21 Hartmut Holzgraefe hart...@skysql.com On 21.06.2013 13:59, Rafał Radecki wrote: Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. I don't understand how Hartmut's answer was insufficient. The InnoDB engine must get data from the disk (reads), send data to the disk (writes), and occasionally tell the operating system that it must flush its buffers to disk to ensure durability (fsync). Why are you so interested in these numbers? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
Am 24.06.2013 18:47, schrieb Johan De Meersman: - Original Message - From: nixofortune nixofort...@gmail.com Hi guys, any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE command. the space on the hard drive gone down from 165 Gig to 70 Gig. I understand that during repair process MySQL creates temp file and remove it after the job done. Or removal process executes on the server restart? how can I get that space back? I can't check the table directory as I don't have root perm on that box. Oops... Can you run [show global variables like 'innodb_file_per_table';] ? I kind of expect it to be OFF, which means that the temp table would have been created in the main tablespace. If that's the case, that space has been permanently assimilated by the global tablespace; the only way to get it back would be a full dump of all your (innodb) tables, stop server, delete tablespace, start server and import the data again. Be sure to read the documentation carefully before doing such an intrusive operation. While you're doing that, use the opportunity to set innodb_file_per_table to ON :-p he spoke about MYISAM table the space on the hard drive gone down from 165 Gig to 70 Gig how can I get that space back? I can't check the table directory as I don't have root perm well, someone should look at the dadadir and error-log it is not uncommon that a repair to such large tables fails due too small myisam_sort_buffer_size and i suspect the operation failed and some temp file is laying around signature.asc Description: OpenPGP digital signature
Re: space gone after MyISAM REPAIR TABLE
Oops, totally missed that, thanks. Reindl Harald h.rei...@thelounge.net wrote: Am 24.06.2013 18:47, schrieb Johan De Meersman: - Original Message - From: nixofortune nixofort...@gmail.com Hi guys, any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE command. the space on the hard drive gone down from 165 Gig to 70 Gig. I understand that during repair process MySQL creates temp file and remove it after the job done. Or removal process executes on the server restart? how can I get that space back? I can't check the table directory as I don't have root perm on that box. Oops... Can you run [show global variables like 'innodb_file_per_table';] ? I kind of expect it to be OFF, which means that the temp table would have been created in the main tablespace. If that's the case, that space has been permanently assimilated by the global tablespace; the only way to get it back would be a full dump of all your (innodb) tables, stop server, delete tablespace, start server and import the data again. Be sure to read the documentation carefully before doing such an intrusive operation. While you're doing that, use the opportunity to set innodb_file_per_table to ON :-p he spoke about MYISAM table the space on the hard drive gone down from 165 Gig to 70 Gig how can I get that space back? I can't check the table directory as I don't have root perm well, someone should look at the dadadir and error-log it is not uncommon that a repair to such large tables fails due too small myisam_sort_buffer_size and i suspect the operation failed and some temp file is laying around -- Sent from Kaiten Mail. Please excuse my brevity.
Re: space gone after MyISAM REPAIR TABLE
On 24/06/13 19:57, Reindl Harald wrote: Am 24.06.2013 18:47, schrieb Johan De Meersman: - Original Message - From: nixofortune nixofort...@gmail.com Hi guys, any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE command. the space on the hard drive gone down from 165 Gig to 70 Gig. I understand that during repair process MySQL creates temp file and remove it after the job done. Or removal process executes on the server restart? how can I get that space back? I can't check the table directory as I don't have root perm on that box. Oops... Can you run [show global variables like 'innodb_file_per_table';] ? I kind of expect it to be OFF, which means that the temp table would have been created in the main tablespace. If that's the case, that space has been permanently assimilated by the global tablespace; the only way to get it back would be a full dump of all your (innodb) tables, stop server, delete tablespace, start server and import the data again. Be sure to read the documentation carefully before doing such an intrusive operation. While you're doing that, use the opportunity to set innodb_file_per_table to ON :-p he spoke about MYISAM table the space on the hard drive gone down from 165 Gig to 70 Gig how can I get that space back? I can't check the table directory as I don't have root perm well, someone should look at the dadadir and error-log it is not uncommon that a repair to such large tables fails due too small myisam_sort_buffer_size and i suspect the operation failed and some temp file is laying around Thanks Reindl, It looks like Repair operation completed successfully. Overall it took 2Hours to complete with OK massage and some other message related to the index size. Repair process went through Repair by sort. myisam_sort_buffer_size = 526M. Provider runs MySQL on FreeBSD + ZFS file system. Could it be up to snapshots as well? I will ask them to look inside of datadir as we migrated this DB from Solaris just day before. This is a new DB for me and I never worked with MyISAM tables of that size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
