Yay! My first blog post! As long as at least 1 person finds it useful, I’ve done my job. 😉 Recently, one of my long-term clients was noticing that while their INSERTs were succeeding, a particular column counter was not incrementing. A quick investigation determined the column was of type int(11) and they had reached the maximum value of 2147483647. We fixed this by using pt-online-schema-change to change the column to int(10) unsigned, thus allowing values up to 4294967295.
My client was now concerned about all his other integer-based columns and wanted me to check them all. So I wrote a quick-n-dirty script in Go to check all integer-based columns on their current value compared to the maximum allowed for that column type.
Here’s a quick overview; the code is pretty simple.
First we connect to MySQL and verify the connection:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:3306)/%s", mysqlUn, mysqlPw, hostToCheck, dbToCheck)) if err != nil { fmt.Printf("Error connecting to MySQL on '%s': n", hostToCheck, err) db.Close() os.Exit(1) } // Check connection is alive. err = db.Ping() if err != nil { fmt.Printf("Unable to ping mysql at '%s': %sn", hostToCheck, err) db.Close() os.Exit(1) } |
Next, we query the information_schema.columns table for the names of all integer-based columns and calculate what their maximum value can be (credit for the clever SQL goes to Peter Boros).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | // Construct our base i_s query var tableExtraSql string if tableToCheck != "" { tableExtraSql = fmt.Sprintf("AND TABLE_NAME = '%s'", tableToCheck) } baseSql := fmt.Sprintf(` SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, (CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)) AS MAX_VALUE FROM information_schema.columns WHERE TABLE_SCHEMA = '%s' %s AND DATA_TYPE IN ('tinyint', 'int', 'mediumint', 'bigint')`, dbToCheck, tableExtraSql) |
Now that we have this list of columns to check, we simply loop over this result set, get the MAX() of each column and print a pretty report.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | // Loop over rows received from i_s query above. for columnsToCheck.Next() { err := columnsToCheck.Scan(&tableName, &columnName, &columnType, &maxValue) if err != nil { log.Fatal("Scanning Row Error: ", err) } // Check this column query := fmt.Sprintf("SELECT MAX(%s), ROUND((MAX(%s)/%d)*100, 2) AS ratio FROM %s.%s", columnName, columnName, maxValue, dbToCheck, tableName) err = db.QueryRow(query).Scan(¤tValue, &ratio) if err != nil { fmt.Printf("Couldn't get MAX(%s.%s): %sn", tableName, columnName, err) fmt.Println("SQL: ", query) continue } // Print report if ratio.Valid && ratio.Float64 >= float64(reportPct) { fmt.Printf("'%s'.'%s' - Type: '%s' - ", tableName, columnName, columnType) fmt.Printf("ColumMax: '%d'", maxValue) fmt.Printf(" - CurVal: '%d'", currentValue.Int64) fmt.Printf(" - FillRatio: '%.2f'n", ratio.Float64) } } |
There are more options to the app that allow you to silence some of the verbosity and to only print report lines where the value-to-max ratio is > a user-defined threshold. If you have frequently changing schemas, this should allow you to cron the app and only receive email reports when there is a potential problem. Otherwise, this tool could be useful to run once a month/quarter, just to verify things are in good standing.
Like I said before, hopefully this helps at least 1 person catch a potential problem sooner rather than later.
Please also consider using common_schema’s auto_increment_columns view: https://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/auto_increment_columns.html
or just use this query: http://code.openark.org/blog/mysql/checking-for-auto_increment-capacity-with-single-query
or otherwise use this openark-kit tool: http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-show-limits.html
We’re using the common_schema view when monitoring our servers. We’re checking this view on a couple slaves (they all resolve the same obviously, and we don’t want to do I_S queries on the master if we don’t have to). We have threshold alerts on the AUTO_INCREMENT capacity and live quietly ever after.
On you first blog post, let me make my first ever comment.
On this topic it is also worth mentioning that MySQL has this obscure feature it called “Procedure Analyse”. It gives you current data type usage min and max values, null frequency ect. as well as optimal suggested field size.
http://dev.mysql.com/doc/refman/5.6/en/procedure-analyse.html
You could very easily use this to get this information without any external scripting. Like:
SELECT * FROM table1 PROCEDURE ANALYSE(10, 2000);
Or this stored proc that does it for a whole db:
CREATE PROCEDURE
table_field_size_analyse(IN$databaseVARCHAR(255))BEGIN
DECLARE $table_name VARCHAR(255);
DECLARE $table_cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.
TABLESWHERE TABLE_SCHEMA = $database;DECLARE CONTINUE HANDLER FOR NOT FOUND SET $table_name = NULL;
OPEN $table_cursor;
table_loop: LOOP
FETCH $table_cursor INTO $table_name;
IF $table_name IS NULL
THEN
LEAVE table_loop;
END IF;
SET @dsql = CONCAT(‘SELECT * FROM
', $database, '.', $table_name , 'PROCEDURE ANALYSE(10, 2000);’);PREPARE stmt1 FROM @dsql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END LOOP;
CLOSE $table_cursor;
END
Hi Charl,
Yes, you could certainly do that. However, your procedure would still require external intervention in the form of a script to execute the SP and parse the result. Also, your SP is doing “SELECT *” which will certainly incur a full-table-scan. With my script, only the relevant columns are SELECT’ed and in the case of that column being indexed, won’t incur the penalty.
Additionally, in the environment for my client, with 200+ MySQL servers, deploying a single binary to a central “admin server” and executing multiple copies was far easier than having to deploy a stored procedure to each system. Going the SP route would constitute “a change” and thus the client’s change management policies would have to be followed with approvals and meetings etc etc. My script wasn’t a change and thus easier to be implemented.
Matthew,
You are absolute correct, the “Procedure Analyse” route is horribly slow due to the full table scans. Definitely not something you want to use on production systems. You method is more suited for your described work case.
I use common_schema for this. If you have common_schema installed, the auto_increment_columns view exposes this data and is simpler to work with than information_schema.
I also blogged about this a while back: http://mechanics.flite.com/blog/2012/07/16/monitoring-auto-increment-capacity-in-mysql/
you say at the outset that you wanted to change all INT(11) to INT(10) UNSIGNED
sweet, but the numbers in parentheses are huge red herrings, especially as you seem to want to make the number smaller by 1
you could just as easily have said INT(937) UNSIGNED for all the difference it makes
@r937,
I think you’re confusing the x in INT(x) for the number of bits/bytes. You can read more about this here: http://stackoverflow.com/questions/7552223/int11-vs-intanything-else
@Matthew,
You are probably the first person I know of that uses Go for something that is not experimental. Oh, and congratulations on your new post. I love this blog and I visit it regularly.
@Ike – You are correct in that the auto_increment values are available in information_schema. But, unfortunately, the current (or MAX) value of other non-A_I integer-based columns is not, so you must SELECT that directly from the table.
@Fadi — me? me confused? on the contrary, i was trying to point out that the number in parentheses is a red herring
http://en.wikipedia.org/wiki/Red_herring
“… something that misleads or distracts from the relevant or important issue”
@r937,
Sorry – my bad. I seem to have skipped your second line in your original comment.
Hi,
We also faced the same situations last year which we weren’t aware of and never expected but it happened and it took us one hour to identify the problem. It was embarrassing moment as service was down for almost 1 hour.
Thanks Matthew for more insight.
Another way to catch these problems is to pay attention to MySQL warnings, by logging them.