Mysql – tool to check thesql/mariadb database is all OK

mariadbMySQL

Today, Mariadb went down on one of my servers. The specific issue was something to do with ibdata1 file being locked. I found a website that showed how to fix it, and so all was OK. So I thought.

30 minutes later it went down again, same issue, same fix.

Its now running OK but I am worried it will happen again. I dont know why this would just happen when its been running fine for so long. Its a Linode server that runs Virtualmin for LAMP stack website hosting. Quite straightforward.

So I wondered if there is some sort of tool that can check over the DB and files to check there is nothing corrupt or untoward. Much like you do for operating systems, checking the disk is OK, defragging windows etc. Anything that gives the DB a once over to check all is OK.

Best Answer

Verifying that a MariaDB or MySQL instance is "OK" is a complicated task, not least because there are different levels of "OK", and one man's "OK" is another man's "not OK". There are various tools you can use, though. To name a few in no particular order:

Percona Toolkit's pt-variable-advisor
SHOW ENGINE ... e.g. SHOW ENGINE INNODB STATUS\G,
Activate and check the slow query log
PMM - Percona Monitoring & Management
MONyog
mysql-tuner, see e.g. article here.

Related Solutions

MySQL – What Are User Connections and When Are They Created and Destroyed

Please notice the makeup of mysql.user:

mysql> show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

Notice the column max_user_connections. It puts a moratorium on the number of connections a user may make per hour. Check and make sure the user '557574_prod' isn't connecting to mysql a ton of times within less than an hour.

This is used in conjunction with the option max_user_connections that you can set in /etc/my.cnf

Please change either max_user_connections in /etc/my.cnf or, if the individual user has this column set, that column mysql.user.max_user_connections. If you have no config control over my.cnf and you cannot do SQL against the mysql schema, then you beg and implore your hosting company to raise those limits for you.

I believe you can set the max_user_connections as a session variable, but you may need certain privileges, probably SUPER.

As for establish connections, you need to keep those connections open. Perhaps sending a heartbeat of some kind by doing a SELECT. But guess what ??? The db columns max_questions monitor SELECT queries per hour and max_updates monitors INSERTs, UPDATEs, DELETEs per hour. YOu may need to check these columns in mysql.user. You can quickly check those number with:

SHOW GRANTS;

I am not sure if the the user's per-hour connection limits are per thread or per user. You could answer that quickly by connecting to the DB aggressively using 2 or 3 DB connecitons at the same time and the error message should manifest itself. It is up you how to count the number of connections, perhaps "SHOW STATUS LIKE 'Connections';" It should reach either the max limits of the connection within the one connections or it could be the sum of all connections' connection status value.

Please also check the wait_timeout and interactive_timeout values. The default is 28800 in a standalone server. They may be set already. You cannot set them within a current connection. You must be able to set them as follows: (for new conenctions coming)

SET GLOBAL interactive_timeout = 86400;
SET GLOBAL wait_timeout = 86400;

If you cannot run even these commands, my condolences !!! :(

Mysql – Should I use DynamoDB or stick with MySQL via webservice for the mobile app syncs

I don't have experience with DynamoDB so the best I can do is offer some pointers on optimizations to look for if you stay in the hosted MySQL world.

Memory allocation. If you're using innodb be sure you have adequate buffer pool size with two log files as the lesser of 25% buffer pool size or 1 Gig. In a perfect world your buffer pool would be as large as all the .ibd files (aka sum(data_length+index_length) from information_schema.tables where engine='innodb'). Understandably this is not always possible, in which case try to allocate 60-70% of your system memory to buffer pool while leaving the rest available for disk cache. (This of course assumes this is a dedicated MySQL server not hosting apache or running your php scripts).

If you're running MyISAM tables then at least make sure your key_buffer_size can accommodate the index_length (or size of your .MYI files).

Disks. If you're needing to fall back to disk you want those to be as fast as possible. In a perfect world you'd have PCI-E based SSD cards. I imagine this is out of your budget if you're worried about charging after switching to amazon, so maybe see if your hosting provider has any options above what you currently have. SSD based SAS drives > 15k hdd sas > nl-sas.
Service layer between mysql and clients. There is memory overhead to each connection to the database that could be used toward buffer pool or disk cache. Try to have a service layer that maintains a bounded connection pool and providing application layer caching where possible.
Distribute the work load. This is tantamount to going to the "cloud" just that you're managing all machines yourself. If your app is mostly read heavy and not always required to be up to the microsecond up to date you can setup replication between many physical machines and load balance the reads across them.

Look toward getting slaves setup at different physical datacenters across the country/world/what ever's applicable to your demographic. Load balance connections to the nearest one. (This assumes traffic is first routed to your front end in these locations otherwise the problem might just be worsened).

Partitioning. This is especially true if you're using myisam and dealing with table level locks. It's also particularly useful if you have a table that has patterns of pruning old rows periodically (you can just drop partitions instead of running individual delete queries which fragment the data).

Best Answer

Related Solutions

MySQL – What Are User Connections and When Are They Created and Destroyed

Mysql – Should I use DynamoDB or stick with MySQL via webservice for the mobile app syncs

Related Question