I was doing some database sizing in MySQL 5.1.30 GA for memory tables. Generally I have used INFORMATION_SCHEMA.TABLES data_length,index_length as a reasonable guide.

However working with a MEMORY table, after deleting rows, the size did not decrease as expected. I deleted 10% of rows, and saw 0% reduction. This was confirmed by doing a subsequent ALTER where I saw the 10% reduction in memory size.

It requires more investigation, however I found these results unexpected and worthy of publishing.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.30    |
+-----------+


+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1111000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |


mysql> delete from location_ex4 limit 111000;
Query OK, 111000 rows affected (0.16 sec)


+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |


mysql> alter table location_ex4 engine=memory;
Query OK, 1000000 rows affected (2.95 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 53.75530243 | 45.97259521 | 7.78270721

My presentation from the 2008 MySQL Conference on Top 20 Design Tips for Data Architects has been receiving a lot of traffic lately.

Most recently this presentation was featured on the front page of Slideshare, as well as last month I made the top 10 of Hacker News.

Configuring a production environment using a pair of MySQL servers in a Master/Fail Over Master situation is a common process to provide many benefits including supporting failover, backup/recovery, higher availability for software & database upgrades. This is also a common method for database shards. One of the key hidden benefits is by performing regular controlled failovers for example with software upgrades you are actively testing your disaster recovery procedures. Most organizations have a partial plan, some don’t have any, but rarely do people test their disaster recovery.

There is no one way to configure and manage such an environment. There are a number of options including:

• Develop your own home grown scripts
• Flipper by Proven Scaling
• MMM by Percona
• Heatbeat by the Linux High Availability Project

I have started a detailed review of a number of these technologies and will be providing my findings for review.

This is not the only way to solve the problems of course. Google for example have provided MySQL Patches that include features such as semi-sync replication and mirrored binary logs. Red Hat Cluster suite, and MySQL/DRBD are other technologies but less idea for various reasons specifically the “cold” nature of the failover environment.

I had this perplexing problem yesterday where a mysql dump and restore was producing different results when using MaatKit mk-table-checksum.

mk-table-checksum --algorithm=BIT_XOR h=192.168.X.XX,u=user,p=password --databases=db1 --tables=c
DATABASE TABLE   CHUNK HOST         ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db1      c           0 192.168.X.XX InnoDB     215169         d1d52a31    2    0 NULL NULL
mk-table-checksum --algorithm=BIT_XOR h=localhost,u=user,p=password --databases=db1 --tables=c
DATABASE TABLE   CHUNK HOST      ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db1      c           0 localhost InnoDB     215169         91e7f182    0    0 NULL NULL

It was rather crazy until I reviewed the mysqldump settings I was using, and I realized I was using –no-set-names.

So just what does this option remove. Here is a diff of mysqldump with and without.

5a6,10
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
> /*!40101 SET NAMES utf8 */;
153a159,161
> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
> /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
> /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
156c164

As you can see it executes a SET NAMES utf8. The problem here is I’m exporting a table, and it is DEFAULT CHARSET=latin1, and no columns are defined as utf8.

I’m not expert in character sets, but this strikes me as strange, and a problem that remains unresolved to my satisfaction, resolved, but not to my comfort level.

We all know with MySQL you can use ORDER BY with a list of columns to return an ordered set, e.g. ORDER BY name, type, state;
I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.

However I needed to do some selective ordering of a type field, I didn’t want to for example have a lookup table just to join for ordering. While contemplating a means of achieving this, I asked a work colleague, who I figured may have just experienced this problem before. Lone behold I became the student as I discovered there is a third syntax with ORDER BY, using expressions.

mysql> create table test(name varchar(10) not null, type varchar(10) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test(name,type) values
('Apples','Fruit'),
('Bananas','Fruit'),
('Carrots','Veg'),
('Onions','Veg'),
('Beer','Liquid'),
('Water','Liquid'),
('Crackers','Food');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select name from test
order by type='Veg' DESC,
         type='Fruit' DESC,
         type='Food' DESC,
         type='Liquid' DESC;
+----------+
| name     |
+----------+
| Carrots  |
| Onions   |
| Apples   |
| Bananas  |
| Crackers |
| Beer     |
| Water    |
+----------+
7 rows in set (0.00 sec)

Of course, reading the MySQL Manual confirms this on the SELECT command.
I’ve not read the MySQL manual from cover to cover, since 4.x days. Perhaps it’s time.

Thanks to Nick Pisarro of Blog Revolution for this most valuable tip.

Indexes are a great way to improve performed in a MySQL database, when used appropriately.
When used in-appropriately the impact can be a degradation of performance.

The following example from Movable Type shows how when reviewing the slow query log I found numerous occurrences of Inserts take 3 or more seconds, with no reported lock contention time for this insert.

# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SET insert_id=6281;
INSERT INTO mt_comment
(comment_author, comment_blog_id, comment_commenter_id, comment_created_by,
 comment_created_on, comment_email, comment_entry_id, comment_ip, comment_junk_log,
comment_junk_score, comment_junk_status, comment_last_moved_on, comment_modified_by,
comment_modified_on, comment_parent_id, comment_text, comment_url, comment_visible)
VALUES (...)

The impact here, is that SELECT statements to the mt_comment table are also blocked because this table is in MyISAM. It was reviewing slow running SELECT statements that the cause of the slow inserts was easily determined.

mysql> explain SELECT comment_id
    -> FROM mt_comment
    -> WHERE (comment_visible = '1') AND (comment_blog_id = '3') AND (comment_entry_id = '276')
    -> ORDER BY comment_created_on DESC;


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt_comment
         type: ref
possible_keys: mt_comment_visible,mt_comment_entry_id,mt_comment_blog_id,mt_comment_blog_stat,mt_comment_visible_date,mt_comment_entry_visible,mt_comment_blog_visible,mt_comment_blog_ip_date,mt_comment_blog_url
          key: mt_comment_entry_visible
      key_len: 6
          ref: const,const
         rows: 99
        Extra: Using where
1 row in set (0.00 sec)


CREATE TABLE `mt_comment` (
  `comment_id` int(11) NOT NULL auto_increment,
  `comment_author` varchar(100) default NULL,
  `comment_blog_id` int(11) NOT NULL default '0',
  `comment_commenter_id` int(11) default NULL,
  `comment_created_by` int(11) default NULL,
  `comment_created_on` datetime default NULL,
  `comment_email` varchar(75) default NULL,
  `comment_entry_id` int(11) NOT NULL default '0',
  `comment_ip` varchar(16) default NULL,
  `comment_junk_log` mediumtext,
  `comment_junk_score` float default NULL,
  `comment_junk_status` smallint(6) default '0',
  `comment_last_moved_on` datetime NOT NULL default '2000-01-01 00:00:00',
  `comment_modified_by` int(11) default NULL,
  `comment_modified_on` datetime default NULL,
  `comment_parent_id` int(11) default NULL,
  `comment_text` mediumtext,
  `comment_url` varchar(255) default NULL,
  `comment_visible` tinyint(4) default NULL,
  PRIMARY KEY  (`comment_id`),
  KEY `mt_comment_commenter_id` (`comment_commenter_id`),
  KEY `mt_comment_visible` (`comment_visible`),
  KEY `mt_comment_junk_score` (`comment_junk_score`),
  KEY `mt_comment_ip` (`comment_ip`),
  KEY `mt_comment_parent_id` (`comment_parent_id`),
  KEY `mt_comment_entry_id` (`comment_entry_id`),
  KEY `mt_comment_email` (`comment_email`),
  KEY `mt_comment_last_moved_on` (`comment_last_moved_on`),
  KEY `mt_comment_created_on` (`comment_created_on`),
  KEY `mt_comment_junk_status` (`comment_junk_status`),
  KEY `mt_comment_blog_id` (`comment_blog_id`),
  KEY `mt_comment_blog_stat` (`comment_blog_id`,`comment_junk_status`,`comment_created_on`),
  KEY `mt_comment_visible_date` (`comment_visible`,`comment_created_on`),
  KEY `mt_comment_entry_visible` (`comment_entry_id`,`comment_visible`,`comment_created_on`),
  KEY `mt_comment_blog_visible` (`comment_blog_id`,`comment_visible`,`comment_created_on`,`comment_id`),
  KEY `mt_comment_blog_ip_date` (`comment_blog_id`,`comment_ip`,`comment_created_on`),
  KEY `mt_comment_junk_date` (`comment_junk_status`,`comment_created_on`),
  KEY `mt_comment_blog_url` (`comment_blog_id`,`comment_visible`,`comment_url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

As you can see, the table has 18 indexes. This means that for every row inserted, 18 separate index inserts are required.

When adding an Index to a table, first determine the usage patterns that will use the index, consolidating indexes when possible and removing obvious duplicates (in the above example, the single column comment_blog_id is a classic duplicate index).

Adding an index will generally help SELECT performance, depending on cardinality, but will always impact INSERT,UPDATE and DELETE performance.
Another down side of too many indexes is the MySQL optimizer has much more work to do to eliminate beneficial indexes for every Query Execution Plan (QEP) that is undertaken.

Indeed I have seen worse, in one case a table with ~120 columns, move then 20 single column indexes AND a 3 part primary key summing 40 bytes in InnoDB. The impact was terrible for performance, with the Index size being 3x times the data size.

About the Author

Ronald Bradford, Principal of 42SQL provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of specialized consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.

Why you do not use GRANT ALL ON *.*?

I was with a client today, and after rebooting a MySQL 5.0.22 instance cleanly with /etc/init.d/mysqld service, I observed the following error, because you always check the log file after starting MySQL.

080923 16:16:24  InnoDB: Started; log sequence number 0 406173600
080923 16:16:24 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.22-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
080923 16:16:24 [ERROR] /usr/libexec/mysqld: Table './schema_name/table_name' is marked as crashed and should be repaired
080923 16:16:24 [Warning] Checking table:   './schema_name/table_name'

Now, I’d just added to the /etc/my.cnf a number of settings including:

myisam_recovery=FORCE,BACKUP

which explains the last line of the log file. When attempting to connect to the server via the mysql client I got the error:

“To many connections”

So now, I’m in a world of hurt, I can’t connect to the database as the ‘root’ user to observe what’s going on. I know that table it’s decided to repair is 1.4G in size, and the server is madly reading from disk. Shutting down the apache server that was connecting to the database is not expected to solve the problem, and does not, because connections must wait to timeout.

MySQL reserves a single super privileged connection, i.e. ‘root’ to the mysql server specifically for this reason, unless all the connections have this privilege. The problem, as often experienced with clients, is the permissions of the application user is simply unwarranted.

mysql> select host,user,password from mysql.user;
+-----------+-------------+------------------+
| host      | user        | password         |
+-----------+-------------+------------------+
| localhost | root        | 76bec9cc7dd32bc0 |
| xxxxxx    | root        |                  |
| xxxxxx    |             |                  |
| localhost |             |                  |
| %         | xxxxxxxxxxx | 0716d6776318d605 |
| localhost | xxxxxxxxxxx | 0716d6776318d605 |
| localhost | xxxxxxx     | 6885269c4a550a03 |
+-----------+-------------+------------------+
7 rows in set (0.00 sec)

mysql> show grants for xxxxxxx@localhost;
+---------------------------------------------------------------------------------------+
| Grants for xxxxxxx@localhost                                                          |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO xxxxxxx'@'localhost' IDENTIFIED BY PASSWORD '6885269c4a550a03'  |
| GRANT ALL PRIVILEGES ON `xxxxxxx`.* TO 'xxxxxxx'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

So the problem is ALL PRIVILEGES is granted to an application user. Never do this!

The solution is to remove all unused users, anonymous users, and create the application user with just the privileges needed.

DROP USER xxxxxxxxxxx@localhost;
DROP USER xxxxxxxxxxx@'%';

DELETE FROM mysql.user WHERE user='';
FLUSH PRIVILEGES;
DROP USER xxxxxxx@localhost;
CREATE USER xxxxxxx@localhost IDENTIFIED BY 'xxxxxxx';

GRANT SELECT,INSERT,UPDATE,DELETE ON xxxxxxx.* TOxxxxxxx@localhost;

While working for a client, I had need to produce canned results of certain different criteria, recording the result in a table for later usage, and keep the position within each result.

Knowing no way to do this via a single INSERT INTO … SELECT statement, I reverted to using a MySQL Stored Procedure. For example, using a sample I_S query and the following snippet:

  ...
  DECLARE list CURSOR FOR SELECT select table_name from information_schema.tables where table_schema='INFORMATION_SCHEMA';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;

  OPEN list;
  SET result_position = 1;
  SET done = FALSE;
  lab: LOOP
    FETCH list INTO table_name;
    IF done THEN
      CLOSE list;
      LEAVE lab;
    END IF;
    INSERT INTO  summary_table(val,pos) VALUES (table_name,result_position);
    SET result_position = result_position + 1;
  END LOOP;

However, in reviewing with another colleague after writing some 10+ different queries and SP loops, I realized that it is possible to record the position of each row in a result set using session variables, negating the need for all that code.

SET @rowcount = 0;
SELECT table_name, @rowcount := @rowcount + 1 FROM information_schema.tables WHERE table_schema = 'INFORMATION_SCHEMA';
+---------------------------------------+----------------------------+
| table_name                            | @rowcount := @rowcount + 1 |
+---------------------------------------+----------------------------+
| CHARACTER_SETS                        |                          1 |
| COLLATIONS                            |                          2 |
| COLLATION_CHARACTER_SET_APPLICABILITY |                          3 |
| COLUMNS                               |                          4 |
| COLUMN_PRIVILEGES                     |                          5 |
| ENGINES                               |                          6 |
| EVENTS                                |                          7 |
| FILES                                 |                          8 |
| GLOBAL_STATUS                         |                          9 |
| GLOBAL_VARIABLES                      |                         10 |
| KEY_COLUMN_USAGE                      |                         11 |
| PARTITIONS                            |                         12 |
| PLUGINS                               |                         13 |
| PROCESSLIST                           |                         14 |
| PROFILING                             |                         15 |
| REFERENTIAL_CONSTRAINTS               |                         16 |
| ROUTINES                              |                         17 |
| SCHEMATA                              |                         18 |
| SCHEMA_PRIVILEGES                     |                         19 |
| SESSION_STATUS                        |                         20 |
| SESSION_VARIABLES                     |                         21 |
| STATISTICS                            |                         22 |
| TABLES                                |                         23 |
| TABLE_CONSTRAINTS                     |                         24 |
| TABLE_PRIVILEGES                      |                         25 |
| TRIGGERS                              |                         26 |
| USER_PRIVILEGES                       |                         27 |
| VIEWS                                 |                         28 |
+---------------------------------------+----------------------------+
28 rows in set (0.01 sec)

Of course you need the all important SET before each query, if not specified however, the subsequent query does not result in an error, just NULL.

So all I needed was:

INSERT INTO summary_table(val,pos)
SELECT table_name, @rowcount := @rowcount + 1
FROM information_schema.tables
WHERE table_schema = 'INFORMATION_SCHEMA';

A simple and trivial solution.

DISCLAIMER:
How this performs under load, and how it is supported in different and future versions of MySQL is not determined.

I came across another MySQL Monitoring product today, DBTuna. I’ve got a trial license of this commercial product so I’ll be looking into its installation, options and support and will be reporting back.