lundi 17 octobre 2011

Indexation et booléens

Voilà encore une raison pour laquelle il faut toujours tester vos requêtes!
En effet, il s'avère que l'optimiseur de MySQL 5.5 (vérifié aussi en 5.1.60) ne fait pas le même choix selon qu'un filtre sur la valeur d'un booléen indexé utilise l'opérateur = ou IS. Voici un exemple extrait du rapport de Bug que j'ai posté sur bugs.mysql.com :

mysql> CREATE TABLE t(id INT, b BOOLEAN DEFAULT FALSE);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t(id) SELECT 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 387 rows affected (0.00 sec)
Records: 387 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 1548 rows affected (0.02 sec)
Records: 1548 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 6192 rows affected (0.03 sec)
Records: 6192 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 24768 rows affected (0.18 sec)
Records: 24768 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 99072 rows affected (0.43 sec)
Records: 99072 Duplicates: 0 Warnings: 0
mysql> alter table t add index(b);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t values(10,TRUE);
Query OK, 1 row affected (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE b IS TRUE\G
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: b
key_len: 2
ref: NULL
rows: 131783
Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE b = TRUE\G
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: b
key: b
key_len: 2
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)

Comme vous pouvez le voir, lorsque l'on utilise l'opérateur "=" , MySQL effectue un accès unique dans l'index b, alors qu'avec l'opérateur "IS" il effectue un parcours total de l'index... Il est donc très important pour l'instant de privilégier l'opérateur d'égalité en attendant la correction du bug.
Lire la suite...

mercredi 12 octobre 2011

Sauvegarder physiquement certaines tables InnoDB

Avec les tables MyISAM, il est possible de sauvegarder directement les fichiers MYD et MYI lorsque le serveur MySQL tourne, en supposant bien sûr que vous avez verrouillé les tables concernées avec la commande LOCK TABLES ou qu'il n'y a aucune requête en cours. Cependant, il n'en est pas de même avec le moteur InnoDB. En effet, à la différence de MyISAM, il y a des threads qui continue à s'exécuter après que les modifications aient été faites et qui modifient les fichiers de données. Seules les journaux de transactions (iblogfiles) sont réellement écrits à chaque fin de transaction (je considère que vous ne jouez pas avec le paramètre innodb_flush_log_at_trx_commit). De plus, il y a un fichier spécial, nommé par défaut ibdata1, qui contient le dictionnaire de données et toutes les tables (et index) InnoDB ayant été créées avec le paramètre innodb_file_per_table désactivé).
C'est la raison pour laquelle il n'est pas possible de copier un fichier innodb d'un serveur à un autre. Il est par contre tout a fait possible de sauvegarder un fichier innodb et de le restaurer plus tard sur le même serveur ou sur un serveur hébergeant une sauvegarde physique, sans avoir à restaurer la base dans son ensemble.
Il faut savoir qu'InnoDB associe un identifiant à chaque fichier innoDB nommé tablespace (oui cela n'a rien à voir avec les tablespaces d'Oracle). Cet id est stocké dans le catalogue InnoDB ainsi que dans le fichier concerné. C'est ce qui empêche de restaurer le fichier sur un autre serveur sur lequel le même tablespace n'existe pas ou a un identifiant différent, ou sur le même serveur sur lequel on aurait fait un TRUNCATE de la table, et donc généré implicitement un nouvel identifiant !

La méthode à suivre est la suivante, détaillée dans la document MySQL :

mysql> USE test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE t1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT @i:=0;
+-------+
| @id:=0 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.03 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.00 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.00 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 594 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS\G ... Main thread process no. 5109, id 140547993949952, state: waiting for server activity
Number of rows inserted 594, updated 0, deleted 0, read 1193
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 12.91 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)

Une fois que le statut du moteur InnoDB est bien à "waiting for server activity", On sauvegarde le fichier InnoDB

cp -p /var/lib/mysql/test/t1.ibd /tmp/

La sauvegarde effectuée, on simule la perte de données

mysql> DELETE FROM t1 WHERE id>400;
Query OK, 194 rows affected (0.04 sec)
mysql> SELECT MAX(id) FROM t1;
+---------+
| MAX(id) |
+---------+
| 400 |
+---------+
1 row in set (0.00 sec)

- On tente maintenant de restaurer nos données en prévenant MySQL

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

- On restaure le fichier à sa place d'origine

root@lizzie:~# cp -p /tmp/t1.ibd /var/lib/mysql/test/t1.ibd

- On avertit MySQL qu'il peut utiliser à présent le fichier

mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT MAX(id) FROM t1;
+---------+
| MAX(id) |
+---------+
| 594 |
+---------+
1 row in set (0.02 sec)

Et voilà !
Par contre, comme je vous l'ai dit ça ne fonctionne plus quand l'id du tablespace a été modifié dans le catalogue :

mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
root@lizzie:~# cp -p /tmp/t1.ibd /var/lib/mysql/test/t1.ibd
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine

Vous trouverez un message plus explicite dans le fichier d'erreur :
grep -A 10 "InnoDB: Error" /var/lib/mysql/lizzie.err
111012 18:09:31 InnoDB: Error: tablespace id and flags in file './test/t1.ibd' are 209768 and 0, but in the InnoDB
InnoDB: data dictionary they are 209769 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
111012 18:09:31 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `test`.`t1`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE
Lire la suite...