schema系统库的改进(3)

4、mysql5.7和mysql8.0 主要配置

mysql5.7.22 5.7.22-log innodb_buffer_pool_size 128M innodb_log_buffer_size 64M innodb_log_file_size 48M binlog_format ROW innodb_flush_log_at_trx_commit 1 sync_binlog 1 mysql8.0.15 8.0.15 innodb_buffer_pool_size 128M innodb_log_buffer_size 64M innodb_log_file_size 48M binlog_format ROW innodb_flush_log_at_trx_commit 1 sync_binlog 1

5、mysql5.7开启测试
bash test_I_S_acess.sh 57222 60

Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! mysql version 5.7.22-log Variable_name Value Created_tmp_tables 1 sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 40 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 40 tps: 4765.73 qps: 4765.73 (r/w/o: 4765.73/0.00/0.00) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 40 tps: 5409.00 qps: 5409.00 (r/w/o: 5409.00/0.00/0.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 40 tps: 5154.45 qps: 5154.45 (r/w/o: 5154.45/0.00/0.00) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 40 tps: 5383.50 qps: 5383.50 (r/w/o: 5383.50/0.00/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 40 tps: 5456.11 qps: 5456.11 (r/w/o: 5456.11/0.00/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 40 tps: 5458.66 qps: 5458.66 (r/w/o: 5458.66/0.00/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 316322 write: 0 other: 0 total: 316322 transactions: 316322 (5270.99 per sec.) queries: 316322 (5270.99 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 5270.9892 time elapsed: 60.0119s total number of events: 316322 Latency (ms): min: 0.42 avg: 7.59 max: 858.18 95th percentile: 17.95 sum: 2399738.58 Threads fairness: events (avg/stddev): 7908.0500/53.41 execution time (avg/stddev): 59.9935/0.00 mysql version 5.7.22-log Variable_name Value Created_tmp_tables 316327

cpu使用率接近100%

mysql5.7.22 40个并发线程 压力测试60秒，访问I_S.tables 平均qps为5k，并创建了31w个临时表!

6、mysql8.0开启测试

Shutting down MySQL.. SUCCESS! Starting MySQL.... SUCCESS! mysql version 8.0.15 Variable_name Value Created_tmp_tables 1 sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 40 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 40 tps: 1283.76 qps: 1283.76 (r/w/o: 1283.76/0.00/0.00) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 40 tps: 1286.43 qps: 1286.43 (r/w/o: 1286.43/0.00/0.00) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 40 tps: 1333.00 qps: 1333.00 (r/w/o: 1333.00/0.00/0.00) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 40 tps: 1280.79 qps: 1280.79 (r/w/o: 1280.79/0.00/0.00) lat (ms,95%): 40.37 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 40 tps: 1313.57 qps: 1313.57 (r/w/o: 1313.57/0.00/0.00) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 40 tps: 1222.95 qps: 1222.95 (r/w/o: 1222.95/0.00/0.00) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 77248 write: 0 other: 0 total: 77248 transactions: 77248 (1286.95 per sec.) queries: 77248 (1286.95 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 1286.9486 time elapsed: 60.0242s total number of events: 77248 Latency (ms): min: 1.88 avg: 31.08 max: 692.98 95th percentile: 40.37 sum: 2400505.33 Threads fairness: events (avg/stddev): 1931.2000/51.17 execution time (avg/stddev): 60.0126/0.00 mysql version 8.0.15 Variable_name Value Created_tmp_tables 2

cpu使用率接近100%

mysql8.0.15 40个并发线程，压力测试60秒，访问I_S.tables 平均qps为1.2k，并创建了1个临时表!

结论

mysql8.0开始查询I_S中表不会再从文件系统(FRM, TRG，OPT文件)和myisam、innodb系统表读取元数据信息（8.0开始元数据信息统一存放在数据字典表中）

mysql8.0 访问I_S.tables不会创建临时表，这减少了内存暴涨的可能，但访问I_S.tables的qps大约是mysql5.7.22的1/5，访问速度没有mysql5.7.22的快

mysql8.0 访问I_S.tables 用户空间cpu消耗没有mysql5.7的高（没有创建临时表的缘故吧），但系统空间cpu消耗高于mysql5.7！

Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx