Mysql 5.5: Optimizing all tables with thesqlcheck – is it useful to run the -check option along with -optimize

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 !!! :(

The first thing I see is the MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded' in the WHERE clause. That might trigger a bypass of the MySQL Query Optimizer and issue a full table scan to accomplish locating the row.

Also, try to find out if the guid column is indexed in the wp_rb_posts table.

Also, what jumps out at me when I see 'WordPress' is this question: Is all your data MyISAM or InnoDB ??? If all your data is MyISAM (in this case, the wp_rb_posts table), ALWAYS expect full table locks upon each INSERT, UPDATE, or DELETE on a MyISAM table. You may want to consider converting all your WordPress data into InnoDB. This will alleviate table locking.

The reason I switched gears into converting MyISAM to InnoDB ? When there are a lot of INSERTs, UPDATES, or DELETEs against wp_rb_posts (if it currently MyISAM), each will create a full table lock on a first-come, first-server basis. Any SELECT query, regardless of being a good or bad performing query, simply waits its turn until all queries accessing wp_rb_posts see the wp_rb_posts table unlock and access is granted.

While such SELECT queries wait, you may realize that the running time is climbing, not because the query is necessarily bad, but because it spent most of its lifetime waiting. Thus, the running time of the query may be deceptive because of external factors such as number of DB Connections running the same query, number of DB connections running different queries involving wp_rb_posts, overall server load, and so forth. Also worth noting is the number of rows in wp_rb_posts. You need to find out if the running time of this query is bad in a standalone test environment.

On the other hand, if wp_rb_posts is already InnoDB, now you can explore the query's EXPLAIN plan and look for indexes being selected or ignored.

Here is how you can convert all MyISAM tables to InnoDB

As a MySQL DBA, I trust MySQL to do the conversion by having MySQL write the script for me.

Form the Linux command run this query

mysql -h... -u... -p... -A --skip-column-names -e"SELECT db,tb FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql

The script will convert the smallest tables first. This script was also bypass any MyISAM tables that have FULLTEXT indexes.

Ater looking over the script, you can simply run it in MySQL as follows:

mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql

or if you want to see the timing of each conversion, login to mysql and run this:

mysql> source /root/ConvertMyISAM2InnoDB.sql

This should not get messed up because a full table lock happens when the conversion is being executed.

Once all tables are converted you need to tune the MySQL settings for InnoDB usage and scale down the key_buffer.

Please read this for setting the InnoDB Buffer Pool : What are the main differences between InnoDB and MyISAM?

Give it a Try !!!