Conde Bond Post

Sólo existe un tipo de conocimiento, aquel que se transmite.

MySQL 5.6 Testing Optimization InnoDB Tables


After reading an experiment published about optimizing InnoDB, I tried to check the result during the MySQL Administration Training at Oracle I attendthis week…

We create the table a in order to test the trick for InnoDB tables optimizatio provided by Peter in this post (1) .

CREATE TABLE `a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c` char(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1

At mysqlprompt we create a function for generate random string (2)

DROP function if exists genstring;
delimiter $$
CREATE FUNCTION genstring(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
set @var:='';
while(in_strlen>0) do
set @var:=concat(@var,ELT(1+FLOOR(RAND() * 26),

'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'));
set in_strlen:=in_strlen-1;
end while;
RETURN @var;
END $$

we create a procedure to populate the table a

DROP PROCEDURE IF EXISTs InsertTableA;
DELIMITER $$
CREATE PROCEDURE InsertTableA ( IN pRecs INT)
BEGIN
SET @count=1; SET @max=pRecs;
WHILE  @count < @max  DO
SELECT genstring(ROUND((RAND() * 63)+1)) INTO @cins;
INSERT INTO a (C) VALUES (@cins);
SET @count = @count +1;
END WHILE;
END;
$$

and next we put some records to the table

CALL InsertTableA (60067);

First, we execute the direct optimization of the a InnoDB Table…

mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|    60067 |
+----------+
1 row in set (0.03 sec)
mysql>  optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2.34 sec)

And the other optimization approach as shown:

mysql> alter table a drop key c;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.96 sec)

mysql> alter table a add key(c);
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

The second optimization runs faster than the previous one, no doubt. And you see how changes the table files as shown:

[root@EDLVC2R10P5 test]# ls -tlar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 15:06 a.frm
-rw-rw---- 1 mysql mysql 22020096 Jul 24 16:44 a.ibd

[root@EDLVC2R10P5 test]# ls -tlar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 16:52 a.frm
-rw-rw---- 1 mysql mysql 14680064 Jul 24 16:53 a.ibd

We load more data in the table in order to reproduce one more realistic test scenario:

mysql> CALL InsertTableA (100230);
Query OK, 0 rows affected (8 min 38.92 sec)

mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|   160296 |
+----------+
mysql> optimize table a;
1 row in set (0.07 sec)
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (5.05 sec)

And no change in the file size in ther first optimization execution:

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 16:52 a.frm
-rw-rw---- 1 mysql mysql 46137344 Jul 24 17:10 a.ibd

The second optimization approach:

alter table a drop key c;
mysql> alter table a drop key c;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2.23 sec)

mysql> alter table a add key(c);
Query OK, 0 rows affected (4.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

And the size is half more or less prior the new optimization execution:

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 17:15 a.frm
-rw-rw---- 1 mysql mysql 28311552 Jul 24 17:15 a.ibd

In this new scenario we see that time execution of both optimizations are very similar.

We carry on loading more data on the table:

mysql> CALL InsertTableA (426793);
Query OK, 0 rows affected (38 min 25.12 sec)

After the data load we review the file size:

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql      8580 Jul 24 17:15 a.frm
-rw-rw---- 1 mysql mysql 134217728 Jul 24 17:57 a.ibd

And now we repeat both optimizations processes

mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|   587088 |
+----------+
1 row in set (0.28 sec)
mysql> optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (15.36 sec)

Note that the file size is increased after the optimization execution…

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql      8580 Jul 25 07:32 a.frm
-rw-rw---- 1 mysql mysql 142606336 Jul 25 07:32 a.ibd

And now, we execute the second optimization approach:

mysql> alter table a drop key c;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (7.17 sec)

mysql> alter table a add key(c);
Query OK, 0 rows affected (11.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

After review the file size wee see that no changes occurs, respect the original file size, prior the optimization execution.

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql      8580 Jul 25 07:35 a.frm
-rw-rw---- 1 mysql mysql 104857600 Jul 25 07:35 a.ibd

What really happens? I will continue writing about this…

NOTES:
Thinking about running OPTIMIZE on your Innodb Table ? Stop!
http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/

MySQL Generate Random String, Email Address, URL
http://ready2gosoft.com/php/mysql-generate-random-string.html

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: