打开general_log对性能的影响

原文: https://fromdual.com/general_query_log_vs_mysql_performance

Sometimes, it is required to enable the general query log (which is disabled by default). If the general query log is enabled the server writes to this log information when clients connect or disconnect, and each SQL statement received from the client.

有时，需要启用general_log(默认情况下禁用)。 如果启用了general_log，服务器将在客户端连接或断开连接以及从客户端接收到的每条SQL语句时写入此日志信息。

The question is, does enabling the general query log affects the MySQL performance ?

Also, it is possible to record the output of this log into either file or table in the mysql database (mysql.general_log), what is the performance impact of each one?

问题是，启用general_log会影响MySQL性能吗?  同样，可以将这个日志的输出记录到mysql数据库(mysql.general_log)的文件或表中，每个日志的性能影响是什么?

Let's do some simple benchmark for those scenarios to measure the actual impact on the mysql performance.

让我们对这些场景进行一些简单的基准测试，以衡量对mysql性能的实际影响。

SYSTEM INFORMATION系统信息:

HW CONFIGURATIONS:

• CPU: Intel® Core™ i7-3520M Processor (4M Cache, up to 3.60 GHz).

• 2 cores, 4 threads, HT enabled.

• Memory: 8GB RAM (1600).
• Storage: HDD 1TB/ 5400RPM.

SOFTWARE CONFIGURATIONS 软件配置:

• OS: Ubuntu 12.04
• MySQL Server: 5.6.17
• Sysbench: 0.4.12

TEST INFORMATION测试信息:

• Sysbench command: sysbench --num-threads=1 --max-requests=1000 --db-driver=mysql --test=oltp --mysql-table-engine=InnoDB --mysql-user=root run .
• Table structure which was used by sysbench tests:

mysql> show create table sbtest.sbtest\G CREATE TABLE `sbtest` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `k` int(10) unsigned NOT NULL DEFAULT '0',   `c` char(120) NOT NULL DEFAULT '',   `pad` char(60) NOT NULL DEFAULT '',   PRIMARY KEY (`id`),   KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=8574 DEFAULT CHARSET=latin1

注：

The test had been made against 1,2,4,8,16 and 32 threads, each throughput/response time value for each thread's number for each test case is generated by the average of ten (10) times execution.

测试是针对1、2、4、8、16和32个线程进行的，每个测试用例的每个线程数的每个吞吐量/响应时间值是由平均10(10)次执行生成的。

GENERAL LOG DISABLED禁用general_log:

To make sure that the general query log is disabled:


mysql> show global variables like'general_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log   | OFF   |+---------------+-------+

测试结果：

GENERAL LOG ENABLED启用general_log:

The general query log is a dynamic variable which means that it could be enabled or disabled ONLINE without restarting MySQL (since MySQL 5.1):

general_log是一个动态变量，这意味着它可以在不重启MySQL的情况下启用或禁用ONLINE(从MySQL 5.1开始):

mysql>SET GLOBAL general_log=ON;

We can choose the output for this log to be either in a log file (by default) or in a MySQL table (mysql.general_log).

我们可以选择将该日志的输出放在日志文件中(默认情况下)，或者放在MySQL表中(MySQL.general_log)。

What are the benefits that we might get if we have the log output to be stored in a table not in a file?

如果我们将日志输出存储在表中而不是文件中，我们可能会得到什么好处?

• We can use the normal SQL statements to access the log contents to get information about specific criteria (e.g. using WHERE condition) and this is a little harder when dealing with files.

• 我们可以使用普通的SQL语句来访问日志内容，以获得关于特定条件的信息(例如使用WHERE条件)，这在处理文件时有点困难。

• The log contents could be accessed remotely if someone can connect to the MySQL server.

• 如果有人可以连接到MySQL服务器，那么可以远程访问日志内容。

• Standard format for the log entries.

• 日志条目的标准格式。
  

• If the CSV engine is used for the log table, it will be easy to import the CSV file into spreadsheets.

• 如果CSV引擎用于日志表，那么很容易将CSV文件导入电子表格。

• It is easy to expire the logs by simply TRUNCATE the log table.

• 通过简单地截断日志表，很容易使日志过期。

• Log rotation is possible by using RENAME TABLE statement.

• 通过使用RENAME TABLE语句可以实现日志轮换。

• Log entries are not replicated to the slave because they are not written to the binary logs.

• 日志条目不会复制到从服务器，因为它们没有写入二进制日志。

• The mysqldump does not include the log tables contents (general_log or slow_log) in the backup even if --all-databases backup option is used.

• 即使使用了——all-databases备份选项，mysqldump也不包括备份中的日志表内容(general_log或slow_log)。

So, let's check the performance impact then of each log output.

因此，让我们检查每个日志输出的性能影响。

OUTPUT IS FILE输出到文件:

要检查常规日志的输出目标，应该使用以下命令:

mysql> show global variables like'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  |+---------------+-------+

测试结果：

OUTPUT IS TABLE (CSV TABLE)输出到CSV表格:

To change the output destination of the general log from file to table (CSV by default), the following command should be used:

要将通用日志的输出目标从文件更改为表(默认为CSV)，应该使用以下命令:

mysql>SET GLOBAL log_output='TABLE';mysql> show global variables like'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    |TABLE|+---------------+-------+

测试结果：

OUTPUT IS TABLE (MYISAM TABLE)输出到MYISAM表:

Maybe due to the nature of the CSV storage ENGINE we faced performance issues in the previous case. Is it possible to change the table engine of the general_log table?

可能由于CSV存储引擎的特性，我们在前面的例子中遇到了性能问题。 是否有可能改变通用al_log表的表引擎?

The answer is yes, but unfortunately, we are restricted to use only MyISAM storage engine and no engines other than CSV or MyISAM are allowed. Check this link for more information.

答案是肯定的，但不幸的是，我们被限制只能使用MyISAM存储引擎，除了CSV或MyISAM之外不允许使用其他引擎。 查看此链接以获得更多信息。

To alter the log table, you must first disable the logging:

要改变日志表，你必须首先禁用日志:

mysql>altertable mysql.general_log engine=MYISAM;ERROR 1580(HY000): You cannot 'ALTER' a log table if logging is enabled
mysql>SET GLOBAL general_log=OFF;mysql>altertable mysql.general_log engine=MYISAM;mysql>SET GLOBAL general_log=ON;

测试结果：

OUTPUT IS TABLE (MYISAM TABLE WITH SOME STRUCTURES CHANGES)输出到改变表结构的MYISAM表:

In general, to make any SQL query work faster, we might need to optimize the table structure, add indexes, re-write the query, .. etc.

通常，为了使任何SQL查询工作得更快，我们可能需要优化表结构，添加索引，重写查询，.. 等。

The following is the general log table structure:

表结构如下：

mysql> show createtable mysql.general_log\G
CREATETABLE `general_log` (  `event_time` timestampNOTNULL DEFAULT CURRENT_TIMESTAMP ONUPDATE CURRENT_TIMESTAMP,  `user_host` mediumtextNOTNULL,  `thread_id` bigint(21)unsignedNOTNULL,  `server_id` int(10)unsignedNOTNULL,  `command_type` varchar(64)NOTNULL,  `argument` mediumtextNOTNULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'

Let's check what can we do to optimize the general_log table structure (other suggestions are welcomed):

让我们检查一下我们可以做什么来优化generalal_log表结构(欢迎其他建议):

• Can we create partitions on that table which might boost our search?
  Although this is a normal MyISAM table but partitioning IS NOT allowed for the log tables (by the way, partitioning is not permitted also for CSV tables).

• 我们是否可以在该表上创建分区，从而提高搜索效率?  虽然这是一个普通的MyISAM表，但是不允许对日志表进行分区(顺便说一下，对CSV表也不允许分区)。

• Can we change the datatype of the `user_host` column from mediumtext to e.g. varchar(100)? (The max length of this column's data on my machine didn't exceed 50 characters)
  While it is - syntax wise - accepted but no logs will be stored in the table after that and the following error will be printed in the error log file:

• 我们可以将' user_host '列的数据类型从mediumtext改为例如varchar(100)吗? (该列数据在我的机器上的最大长度不超过50个字符)  虽然它是-语法明智-接受，但没有日志将存储在表之后，以下错误将打印在错误日志文件:

2014-03-0618:44:216987[ERROR] Failed to write to mysql.general_log:2014-03-0618:44:236987[ERROR] Incorrect definition of table mysql.general_log: expected column 'user_host' at position 1 to have type mediumtext, found type varchar(100).

• What about creating indexes on the columns we will use for most of our searches (`event_time` and `argument`)? lets try that!

• Add index on `event_time` column:
  

• 如何在我们大多数搜索(' event_time '和' argument ')中使用的列上创建索引? 让试试!

mysql>SET GLOBAL general_log=OFF;mysql>altertable mysql.general_log add index ev_tm_idx(`event_time`);mysql> show createtable mysql.general_log\G
CREATETABLE `general_log` (  `event_time` timestampNOTNULL DEFAULT CURRENT_TIMESTAMP ONUPDATE CURRENT_TIMESTAMP,  `user_host` mediumtextNOTNULL,  `thread_id` bigint(21)unsignedNOTNULL,  `server_id` int(10)unsignedNOTNULL,  `command_type` varchar(64)NOTNULL,  `argument` mediumtextNOTNULL,  KEY `ev_tm_idx` (`event_time`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'mysql>SET GLOBAL general_log=ON;

测试结果是：

• Add FULLTEXT index on `argument` column:

mysql>SET GLOBAL general_log=OFF;mysql>altertable mysql.general_log add fulltext index (`argument`);mysql> show createtable mysql.general_log\G
CREATETABLE `general_log` (  `event_time` timestampNOTNULL DEFAULT CURRENT_TIMESTAMP ONUPDATE CURRENT_TIMESTAMP,  `user_host` mediumtextNOTNULL,  `thread_id` bigint(21)unsignedNOTNULL,  `server_id` int(10)unsignedNOTNULL,  `command_type` varchar(64)NOTNULL,  `argument` mediumtextNOTNULL,  KEY `ev_tm_idx` (`event_time`),  FULLTEXT KEY `argument` (`argument`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'mysql>SET GLOBAL general_log=ON;

测试结果：

To make it more clear, the following is the combination of all results in one graph followed by response time comparison:

为了更清楚地说明这一点，下面是一张图中所有结果的组合，然后是响应时间比较:

The raw results in Transactions / Sec might be useful:

CONCLUSION结论:

• The best MySQL performance - among all above test cases - could be achieved by disabling the general query log, e.g. if we compared the above results for the case 4 concurrent threads (the highest value for most scenarios) we would find that:
• MySQL最好的性能-在所有以上的测试用例中-可以通过禁用一般的查询日志来实现，例如，如果我们比较上述4个并发线程的结果(大多数情况下的最高值)，我们会发现:

• Using the general log enabled (general_log = ON) and the log destination is file (log_output = FILE) decreased the throughput by 13.4% and increased the response time by 17.5%.

使用启用的general_log(general_log = ON)和日志目标是文件(log_output = file)的情况下，吞吐量降低了13.4%，响应时间增加了17.5%。

• Using the general log enabled and the log destination is CSV table decreased the throughput by 44.6% and increased the response time by 90%.

使用启用general_log和日志目标为CSV表的情况下，吞吐量降低了44.6%，响应时间增加了90%。

• Using the general log enabled and the log destination is MyISAM table decreased the throughput by 34.3% and increased the response time by 59%.

使用启用了general_log且日志目的是MyISAM表的情况下，吞吐量降低了34.3%，响应时间增加了59%。

• Using the general log enabled and the log destination is MyISAM having an index added on column `event_time` decreased the throughput by 38.4% and increased the response time by 73%.

如果启用了general_log，并且日志目标是MyISAM，并在' event_time '列上添加了索引，则吞吐量降低了38.4%，响应时间增加了73%。

• Using the general log enabled and the log destination is MyISAM having an index added on column `event_time` and FULLTEXT index added on column `argument` decreased the throughput by 85% and increased the response time by 542%.

使用general_log启用和日志目标是MyISAM在列' event_time '上添加索引，在列' argument '上添加FULLTEXT索引，吞吐量降低了85%，响应时间增加了542%。

• Although using table as a log output destination has many benefits - as described above - but it has more negative impact on the MySQL performance as compared to log file.

尽管使用表作为日志输出目标有很多好处(如上所述)，但与日志文件相比，它对MySQL性能有更多的负面影响。

• Increasing the number of concurrently running threads - in the case of log_output=TABLE - will increase the general_log table contention which is controlled by the table locking level for MyISAM or CSV ENGINES.

增加并发运行线程的数量——在log_output=TABLE的情况下——将增加由MyISAM或CSV引擎的表锁定级别控制的general_log表争用。

• Like any other MySQL table - as many rows inserted in the log table as more negative performance impact.

与其他MySQL表一样，插入日志表的行越多，对性能的影响就越大。

• Although mysqldump does not include the log tables contents in the backup but this is not the case when having full physical backup using Xtrabackup or any other physical backup based tools.

虽然mysqldump在备份中不包括日志表的内容，但当使用xtrabbackup或任何其他基于物理备份的工具进行完全物理备份时，情况就不同了。

• Finally, it is preferred to only enable the general query log when it is really needed and it is not recommended to enable it in a production system. It could be enabled (dynamically) for a while then should be disabled again once we have got what we are searching for.

最后，最好只在真正需要时启用 general log，不建议在生产系统中启用它。 它可以启用(动态)一段时间，然后应该再次禁用，一旦我们得到了我们正在搜索的东西。