top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

munin MyISAM & InnoDB issues

0 votes
659 views

I have 4 out of 6 mysql graphs working in munin. "MySQL isam/myisam table-space usage" is a broken image and "MySQL InnoDB free tablespace" says:

"This service is in CRITICAL state because one of the values reported is outside the allowed range.
Field Internal name Type Warn Crit Info Bytes free free gauge **********: **********:"

I don't know enough about mysql (or munin) to figure out what's going on. I'm using both MyISAM and InnoDB tables. Can anyone help me out?

posted Jun 16, 2013 by anonymous

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button
I should add that I get the following:

    # munin-run mysql_isam_space_
    # munin-run mysql_innodb

free.value 0

1 Answer

+1 vote

I seem to understand this is the check on the innodb main tablespace.

Sounds like an overflow in munin check that builds the graph, due to the type of check ("MySQL InnoDB free tablespace") I would not mind too much(There's no such critical thing like too much free space!),
apart from patching the munin check to deal with numbers > 2^32.

To double check, look at:

mysql> SHOW TABLE STATUS LIKE ''G

Look at the "Data_free: " line, it is probably bigger than 2^31.

In this case it is basically not a problem, apart from Munin not able to represent that number.

For "MySQL isam/myisam table-space usage" broken image I have no idea on how that check is built,
moreover MyISAM does not have a real tablespace.
If it makes you feel better in http://demo.munin.jp/munin2/mysql-day.html that is broken too ;)

answer Jun 16, 2013 by anonymous
Similar Questions
0 votes

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.

+1 vote

Below table contains billion of rows,

CREATE TABLE `Sample1` (
  `c1` bigint(20) NOT NULL AUTO_INCREMENT,
  `c2` varchar(45) NOT NULL,
  `c3` tinyint(4) DEFAULT NULL,
  `c4` tinyint(4) DEFAULT NULL,
  `time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `varchar_time_idx` (`c2`,`Time`),
  KEY `varchar_c3_time_idx` (`c2`,`c3`,`Time`),
  KEY `varchar_c4_time_idx` (`c2`,`c4`,`Time`),
  KEY `varchar_c3_c4_time_idx` (`c2`,'c3', `c4`,`Time`),
) ENGINE=InnoDB AUTO_INCREMENT=10093495 DEFAULT CHARSET=utf8

Four multi column index created because having below conditions in where

1) c2 and time
2) c2 and c3 and time
3) c2 and c4 and time
4) c2 and c3 and c4 and time

Cardinality wise c2, c3 and c4 are very low. (ex: Out of one million c2, c3 and c4 have 50 unique column in each)

Column time contains mostly unique fields.

Select, insert and update happened frequently.

Tables has 5 indexing fields(4 multi column). Due to this, 1) Insert and update on index fields become costlier. 2) As the table keep on growing (Nearly one billion rows), Index size also increase more rapidly.

Kindly suggest good approach in mysql to solve this use case.

+1 vote

I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables.

SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND Data_free > 0

After that, I have seen that there are 49 fragmented tables. With one table, I have executed "optimize table table_name;" and "analyze table table_name;". The result is the same, the table continuous fragmented.

Any suggestions? I have followed the mysqltuner recommendations...

...