CODEWITHSUNDEEP
mysqlamazon-rds
wukaiqiang
wukaiqiang

Reputation: 21

how to change Amamon RDS mysql.slow_log table's engine to innodb

mysql.slow_log table default engine is CSV, CSV engine have no index; when data size to very big ，this engine performance is very slow；

how to change Amamon RDS mysql.slow_log table's engine to innodb

Upvotes: 2

Views: 283

Answers (1)

Shadow
Shadow

Reputation: 34285

The short answer is that you cannot do that. Long answer is that as MySQL documentation on log destinations says:

The log tables can be altered to use the MyISAM storage engine. You cannot use ALTER TABLE to alter a log table that is in use. The log must be disabled first. No engines other than CSV or MyISAM are legal for the log tables.

To disable logging so that you can alter (or drop) a log table, you can use the following strategy. The example uses the general query log; the procedure for the slow query log is similar but uses the slow_log table and slow_query_log system variable.

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;

Obviously, you may try to change the table engine to innodb to see if the restriction has been lifted in the amazon version, but do that only in a test environment.

Upvotes: 2

Related Questions