I experimented with this and found something very interesting.
Running Fedora release 20 (Heisenbug) 3.11.10-301.fc20.x86_64, 2GB RAM (I know!), 2 processor Intel centrino.
If you enable the Performance Schema (P_S), the times seem to drop dramatically. Why this is, I simply don't know - the P_S is for monitoring, and not changing anything. I have put what I put in my my.cnf at the end.
Could you test this for your 3 scenarios (CREATE, TRUNCATE & DELETE) running your server using the new my.cnf. If you obtain similar results to mine, then we can investigate further.
Strangely, as far as I can see, the client reports a time of approx 0.3 seconds for me for all of my experiments - but the results from the profiling seem very different. Still puzzling through this one.
Below are my results just for the CREATE table statement before and after enabling the performance schema.
Before enabling P_S.
mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000290 |
| checking permissions | 0.000021 |
| Opening tables | 0.000141 |
| creating table | 0.365769 | <<====
| After create | 0.000037 |
| query end | 0.000023 |
| closing tables | 0.000018 |
| freeing items | 0.000114 |
| cleaning up | 0.000030 |
+----------------------+----------+
9 rows in set, 1 warning (0.00 sec)
After enabling P_S
mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000188 |
| checking permissions | 0.000131 |
| query end | 0.000015 |
| closing tables | 0.000020 |
| freeing items | 0.000068 |
| cleaning up | 0.000052 |
+----------------------+----------+
6 rows in set, 1 warning (0.00 sec)
MariaDB
Before enabling P_S
Query OK, 0 rows affected (0.33 sec)
MariaDB [test]> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000321 |
| checking permissions | 0.000024 |
| Opening tables | 0.000073 |
| After opening tables | 0.000017 |
| System lock | 0.000011 |
| Table lock | 0.000019 |
| After opening tables | 0.000056 |
| creating table | 0.328769 | <<======
| After create | 0.000043 |
| query end | 0.000026 |
| closing tables | 0.000022 |
| freeing items | 0.000043 |
| updating status | 0.000050 |
| cleaning up | 0.000039 |
+----------------------+----------+
14 rows in set (0.00 sec)
After enabling P_S on MariaDB
MariaDB [test]> show profile for CREATE TABLE cache_bootstrap
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000311 |
| checking permissions | 0.000024 |
| Opening tables | 0.000044 |
| After opening tables | 0.000038 |
| System lock | 0.000014 |
| Table lock | 0.000012 |
| After opening tables | 0.000347 |
| After create | 0.000015 |
| query end | 0.000014 |
| closing tables | 0.000021 |
| freeing items | 0.000014 |
| updating status | 0.000026 |
| cleaning up | 0.000034 |
+----------------------+----------+
13 rows in set (0.00 sec)
To be added in the [mysqld] section of my.cnf
[mysqld]
performance_schema
performance_schema_instrument = '%=on'
performance_schema_consumer_events_stages_current = ON
performance_schema_consumer_events_stages_history = ON
performance_schema_consumer_events_stages_history_long = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_waits_history_long = ON
I think best would be to remove the value of auto_increment from import script/dump - let it start at 1, then insert all the rows with fixed ID and let InnoDB do the work:
"If you insert a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value." - from manual
If this is not possible, then you can use a variable in the ALTER. You might do that using prepared statement:
SET @query = CONCAT('ALTER TABLE accounts AUTO_INCREMENT = ', @autoinc, ';');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Best Answer
The reason why you experience performance degradation or stall while executing
TRUNCATE TABLE
is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb stalls. There are other bug numbers opened for prior versions as well.You can use:
It gets tricky for tables with
AUTO_INCREMENT
values: new table is created with anAUTO_INCREMENT
value which is immediately taken in the working table. If you do no want to use same values, you can: