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
As KJavalik pointed out in the Comments, it it safe, but Disk IO will increase and may choke other operations. So in case the Server is not serving any traffic/queries, you may try, else be careful.
Also keep an eye on the free disk space as optimize command will copy the remaining data of the table being optimized to a new table, and if the slave is in replication, new data will also take up some space.
So in case you are optimizing 2 tables of 50Gb each, for example, and prior experience suggests that post optimization each table will be around 35 Gb, then its good to have at least 90Gb of free space (35 Gb + 35 Gb + 20 Gb of extra free space for other tables to grow in case the database is in replication)
Best Answer
OPTION 1:
To find the size of this table you can use the follow query:
SELECT DATA_LENGTH+INDEX_LENGTH AS
totalTable, TABLE_ROWS from information_schema.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_NAME = 'TABLE_NAME';
Now you have to calculate the difference from the actual size
totalTable
+ what you will add, see the list on [MySQL Documentation] about the size of each field.Let's say the first query give you the follow result:
And you are going to add an BIGINT(8 bytes) field, you will have to multiply the new field size * number of rows + actual size in this case will be:
8 * 10 + 3014740
which will give us3014820
OPTION 2
Create a new table before changing the live one
According to Documentation MySQL does this process.
Then change the size of
tmp_table_size
and alsomax_heap_table_size
Have in mind, that other parts of MySQL may using the same shared memory space, then will be save to put a number higher then you receive as result from
SELECT
OPTION 3:
On MySQL 5.6 if you table is innoDB you can explore the new Online DDL for InnoDB Tables