Discussion :

[Fabritio2431]

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

[vttman]

Etonnant ce temps sur un Create !

Ma recherche du "Dimanche" m'indique 2 pistes :

1) la quantité de RAM sur le server ?

2) Et ceci ...

MySQL 5.6.22 (innodb_file_per_table=0 and innodb_stats_persistent=0)
Result: average execution time is back at (only)7.5 seconds!

Conclusion
For most application I would not consider this as an huge problem, this is something done once and then you start working on the database.
But for some applications where they CREATE/DROP tables as a part of normal work this might be important.
If you need to keep performance from MySQL 5.5 in your CREATE TABLE statements and new features like InnoDB files per table and persistent optimizer statistics are not important
disable these features and you have the performance from MySQL 5.5 back again!

Bon je laisse la place aux spécialistes ....

[Artemus24]

Salut à tous.

Bon je laisse la place aux spécialistes ....

On ne connait rien du matériel utilisé et l'on ne sait même pas si la lenteur est générale à toute manipulation sur son ordinateur.
On ne sait même pas si le fichier my.ini est correctement configuré.
Ni si le type de moteur est InnoDB ou MyIsam ?
Et avec ça, il faudrait faire des miracles !

Pouvez-vous m'aider?

Mettez-vous à la place de ceux qui peuvent vous aider, on ne sait rien de votre environnement.

@+

[vttman]

...
Et avec ça, il faudrait faire des miracles !

Pas d'obligation de résultat sur un forum, heureusement ...
Maintenant question/s imprécise/s => Pas ou peu de réponses => Pas de miracle donc je dirais