mysql general_log评测

最新推荐文章于 2025-03-05 18:32:00 发布

wzm112

最新推荐文章于 2025-03-05 18:32:00 发布

阅读量637

点赞数

分类专栏： MYSQL

MYSQL 专栏收录该内容

36 篇文章

订阅专栏

开启general_log
general log默认是记录到文本文件里，不过可以通过修改
log_output='TABLE'更改为记录到数据库里，在mysql db里会增加一个表
general_log. 在init文件里开启如下：
---------
general_log=1
log_output='TABLE'
---------
通过查看表结构，发现是一个外部的csv文件。
mysql> show create table general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)
可以通过命令把csv引擎修改为myisam
set global general_log=0;
alter table general_log engine=myisam;
set global general_log=1;
试一下对性能的影响。运行同一个sql一万次。对表插入一万条数据
myisam:
C:\Users\dell>tcsql perf test "select sql_no_cache count(*) from t1" 10000 1
Benchmark
        Average number of seconds to run all queries: 1.967 seconds
        Minimum number of seconds to run all queries: 1.967 seconds
        Maximum number of seconds to run all queries: 1.967 seconds
        Number of clients running queries: 1
        Average number of queries per client: 10000
经过多次的试验，一般在2秒内完成。
csv:
C:\Users\dell>tcsql perf test "select sql_no_cache count(*) from t1" 10000 1
Benchmark
        Average number of seconds to run all queries: 1.967 seconds
        Minimum number of seconds to run all queries: 1.967 seconds
        Maximum number of seconds to run all queries: 1.967 seconds
        Number of clients running queries: 1
        Average number of queries per client: 10000
看起来效率是差不多的
对general_log表的查询：
csv:
C:\Users\dell>tcsql perf mysql "select sql_no_cache count(*) from general_log" 10 1
Benchmark
        Average number of seconds to run all queries: 9.462 seconds
        Minimum number of seconds to run all queries: 9.462 seconds
        Maximum number of seconds to run all queries: 9.462 seconds
        Number of clients running queries: 1
        Average number of queries per client: 10
十次查询基本时间在9秒左右。
myisam:
C:\Users\dell>tcsql perf mysql "select sql_no_cache count(*) from general_log" 10 1
Benchmark
        Average number of seconds to run all queries: 0.000 seconds
        Minimum number of seconds to run all queries: 0.000 seconds
        Maximum number of seconds to run all queries: 0.000 seconds
        Number of clients running queries: 1
        Average number of queries per client: 10
查询的效率差别非常的大。

如果要开启general_log，还是换成myisam。对性能的压力较小。

http://blog.163.com/zf_zhouf/blog/static/200098026201110179196468/