MySQL General Query Log
General Query Log
1
Mysql
40
White

Quăng viết ngày 01/09/2015

Nhật ký truy vấn log (general query log) sẽ ghi lại mọi truy vấn SQL nhận từ client bao gồm cả các kết nối hoặc ngắt kết nối từ client tới server. Chính vì thế mà log sẽ tăng rất nhanh chóng về kích thước.

General log bị tắt ở chế độ mặc định và thường được bật cho mục đích audit. Ví dụ muốn kiểm tra xem ai xóa, thay đổi dữ liệu. General log hỗ trợ nhiều kiểu ghi như ghi ra file, ra một table (CSV, MyISAM ...). Việc ghi log tất cả các câu truy vấn có thể ảnh hưởng đến hiệu suất của máy chủ MySQL, để kiểm tra việc nay ta thực hiện một số bài test như sau

System Information

  • Hardware: VM, CPU 1 core, RAM 512, HDD
  • Software: CentOS 6.6, MySQL 5.5, sysbench

B1: Ta sẽ tạo một table để chứa dữ liệu test

mysql> SHOW CREATE TABLE 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

B2: Command sysbench để test (ta sẽ test vài lần với thread thay đổi 1, 3, 6, 12, 24, 48, 96)

~$ sysbench --test=oltp --db-driver=mysql --mysql-table-engine=InnoDB --mysql-user=root --mysql-db=sysbench --mysql-user=root --mysql-password=xyz@123 --num-threads=1 --max-requests=1000 --mysql-socket=/var/run/mysqld/mysqld.sock run

B3: Generate graph, dùng gnuplot

Từ log ta parse ra file CSV

~$ cat sysbench.log | egrep " cat|threads:|transactions:" | tr -d "\n" | sed 's/Number of threads: /\n/g' | sed 's/\[/\n/g' | sed 's/[A-Za-z\/]\{1,\}://g'| sed 's/ \.//g' | awk {'print $1 $3'} | sed 's/(/\t/g' > sysbench.csv

gnuplot

cat > sysgraph<<EOF
# output as png image
set terminal png

# save file to "benchmark.png"
set output "benchmark.png"

# graph title
set title "Benchmark for Sysbench"

# aspect ratio for image size
set size 1,1

# enable grid on y and x axis
set grid y
set grid x

# x-axis label
set xlabel "Threads"

# y-axis label
set ylabel "Transactions (tps)"

# plot data from sysbench.csv
plot "sysbench.csv" using (log($1)):2:xtic(1) with linesp notitle
EOF

Lưu ý:

  • Sau mỗi lần benchmark, TRUNCATE lại table sbtest
  • Giải phóng memory cache và buffer cache

1. Disable general log

B1: Kiểm tra

mysql> SHOW GLOBAL VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

B2: Chạy benchmark

~$ for t in 1 3 6 12 24 48 96; do sysbench --test=oltp --db-driver=mysql --mysql-table-engine=InnoDB --mysql-user=root --mysql-db=sysbench --mysql-user=root --mysql-password=xyz@123 --num-threads=$t --max-requests=1000 --mysql-socket=/var/run/mysqld/mysqld.sock run >> sysbench.log; done

B2: generate graph

File CSV kết quả

~$ cat sysbench_off.csv

1       540.79
3       538.70
6       486.58
12      506.37
24      472.52
48      477.38
96      428.21

Và graph

off_general

2. General log to file

B1: Cấu hình và kiểm tra

general_log = 1
general_log_file = /var/log/mysql/mysqld.general.log
log_output = file
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

B2: Chạy lại benchmark ở trên

~$ cat sysbench.csv
1       436.86
3       506.47
6       457.33
12      467.72
24      388.88
48      375.33
96      377.73

B3: Graph

benchmark_file

3. General log to default table (CSV)

B1: Cấu hình và kiểm tra

general_log = 1
general_log_file = /var/log/mysql/mysqld.general.log
log_output = table
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

mysql> SHOW CREATE TABLE general_log\G

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'

B2: Chạy lại benchmark

~$ cat sysbench.csv

1       425.73
3       460.41
6       407.36
12      415.17
24      410.52
48      327.02
96      351.81

B3: Graph

benchmark_csv

4. General log to table MyIsam

B1: Cấu hình và kiểm tra (giống 3)

mysql> alter table 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> alter table mysql.general_log engine=MYISAM;

mysql> SET GLOBAL general_log=ON;

B2: Benchmark

~$ cat sysbench.csv

1       418.43
3       453.76
6       412.48
12      412.83
24      343.32
48      376.76
96      299.09

B3: Graph

benchmark_table_myisam

5 Kết luận

Việc bật general log là có ảnh hưởng tới hiệu suất của mysql. Trong đó log ra file vẫn đỡ hơn nhiều so với log vào table. Các kết quả trên có thể không chính xác lắm ở khoảng chênh lệch giữa các lần test (do server ảo, lược bớt một số lần test như MyIsam có đánh Index, MyIsam FullText index). Chi tiết có thể xem ở bài benchmark của tác giả ABDEL-MAWLA'S tại link

Nói chung là không có nhu cầu gì đặc biệt thì đừng bật general log vừa giảm hiệu suất mà còn nhanh đầy đĩa (LƯU Ý là general log sẽ ghi lại tất cả các truy vấn SELECT nhé, còn các truy vấn INSERT, DELETE, UPDATE ... thì tất nhiên)

6. Troubleshoot

6.1 General log ghi lại gì?

Theo tài liệu thì general log ghi lại tất cả mọi truy vấn và mọi kết nối, nhắt kết nối tới server. Để kiểm tra ta sẽ thử thực hiện các câu truy vấn và kiểm tra lại log. Ở đây ta bật chế độ log general log vào table.

Ta sẽ thực hiện các câu truy vấn làm thay đổi dữ liệu trên table sbtest ở trên

INSERT INTO `sbtest` (`id`, `pad`) VALUES ('599234', 'pppppppppppppppppppppppp')
DELETE FROM `sbtest` WHERE (`id`='389768')
UPDATE `sbtest` SET `pad`='svasda;sdsa;dl;sald;sald;lsa;dlsa;dlsa;' WHERE (`id`='373083')

Và kiểm tra kết quả trong mysql.general_log

USE mysql;
SELECT event_time, user_host, argument 
FROM general_log
WHERE argument LIKE 'DELETE %' OR argument LIKE 'UPDATE%'

+---------------------+----------------------------+--------------------------------------------------------------------------------------------+
| event_time          | user_host                  | argument                                                                                   |
+---------------------+----------------------------+--------------------------------------------------------------------------------------------+
| 2015-08-22 09:03:13 | root[root] @  [10.20.30.1] | DELETE FROM `sbtest` WHERE id LIKE '6%'                                                    |
| 2015-08-22 09:06:51 | root[root] @  [10.20.30.1] | UPDATE `sbtest` SET `pad`='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' WHERE (`id`='553640') |
| 2015-08-22 09:11:02 | root[root] @  [10.20.30.1] | DELETE FROM `sbtest` WHERE (`id`='389768')                                                 |
| 2015-08-22 09:11:29 | root[root] @  [10.20.30.1] | UPDATE `sbtest` SET `pad`='svasda;sdsa;dl;sald;sald;lsa;dlsa;dlsa;' WHERE (`id`='373083')  |
+---------------------+----------------------------+--------------------------------------------------------------------------------------------

6.2 Fonts vs gnuplot

Default gnuplot dùng Aria, ta đổi lại bằng 1 font opensoure như Dejavu

~$ yum install dejavu-serif-fonts -y

export GDFONTPATH=/usr/share/fonts/dejavu
export GNUPLOT_DEFAULT_GDFONT=DejaVuSerif.ttf

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

Quăng

22 bài viết.
173 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
120 13
Gần đây mình có present về chủ đề __Một số thao tác và lệnh đơn giản nhưng hữu ích khi sử dụng Linux__ cho một vài bạn bè. Mặc dù nó khá là đơn giả...
Quăng viết hơn 1 năm trước
120 13
White
74 12
1. Giới thiệu Right tool for right job. Trước tiên phải hiểu là MySQL Replication không phải là giải pháp giải quyết mọi bài toán về quá tải hệ th...
Quăng viết hơn 2 năm trước
74 12
White
51 7
Với những ai làm Linux System Admin hoặc DevOps thì việc quản lí vài chục, vài trăm server là chuyện bình thường. Việc nhớ các địa chỉ IP, port để ...
Quăng viết gần 3 năm trước
51 7
Bài viết liên quan
Male avatar
19 0
Mysql innodb internal là một chủ đề khá sâu. Bản thân tôi cũng chưa bao quát hết. Bài viết này chỉ cung cấp một cái nhìn sơ lược. Để các bạn khôn...
manhdung viết gần 3 năm trước
19 0
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
22 bài viết.
173 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!