Bonjour,
J'ai créé une base de donnée de test sur un serveur avec quelques autres bases de données.
Lorsque je veux créer une table à l'interieur, cela est très lent. Voici un exemple avec le profiling activé :
mysql> CREATE TABLE test.T1 (id INT PRIMARY KEY NOT NULL,nom VARCHAR(100));
Query OK, 0 rows affected (8.02 sec)
mysql> SHOW PROFILES;
+----------+------------+-----------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------+
| 3 | 8.01408700 | CREATE TABLE test.T1 (id INT PRIMARY KEY NOT NULL,nom VARCHAR(100)) |
+----------+------------+-----------------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000087 |
| checking permissions | 0.000017 |
| Opening tables | 0.000088 |
| System lock | 0.000043 |
| creating table | 8.013756 |
| After create | 0.000022 |
| query end | 0.000007 |
| closing tables | 0.000022 |
| freeing items | 0.000031 |
| logging slow query | 0.000007 |
| cleaning up | 0.000007 |
+----------------------+----------+
11 rows in set (0.00 sec)
mysql> SHOW PROFILE CPU;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000087 | 0.000000 | 0.000000 |
| checking permissions | 0.000017 | 0.000000 | 0.000000 |
| Opening tables | 0.000088 | 0.000000 | 0.000000 |
| System lock | 0.000043 | 0.000000 | 0.000000 |
| creating table | 8.013756 | 0.012000 | 0.000000 |
| After create | 0.000022 | 0.000000 | 0.000000 |
| query end | 0.000007 | 0.000000 | 0.000000 |
| closing tables | 0.000022 | 0.000000 | 0.000000 |
| freeing items | 0.000031 | 0.000000 | 0.000000 |
| logging slow query | 0.000007 | 0.000000 | 0.000000 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
11 rows in set (0.00 sec)
mysql> SHOW PROFILE ALL;
+----------------------+-----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+----------------------------+--------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+-----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+----------------------------+--------------+-------------+
| starting | 0.000102 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 4833 |
| Opening tables | 0.000099 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | open_tables | sql_base.cc | 4888 |
| System lock | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 299 |
| creating table | 12.093964 | 0.052000 | 0.004000 | 462 | 9 | 0 | 384 | 0 | 0 | 0 | 153 | 0 | mysql_create_table_no_lock | sql_table.cc | 4295 |
| After create | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_create_table_no_lock | sql_table.cc | 4411 |
| query end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4512 |
| closing tables | 0.000058 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4564 |
| freeing items | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5840 |
| logging slow query | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 1512 |
| logging slow query | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 1521 |
| cleaning up | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1468 |
+----------------------+-----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+----------------------------+--------------+-------------+
12 rows in set (0.00 sec)
Je vois que la partie "creating table" dure 8 secondes mais je ne sais pas pourquoi ni ce que je peux investiguer de plus.
Pouvez-vous m'aider?
Merci
Fabrice
Requête CREATE TABLE très très lente
Répondre avec citation
Partager