Knob Tuning (for ZZNode)
服务器信息
CentOS Linux release 7.1.1503
Architec | x86_64 |
CPU(s): | 8 |
MemTotal: | 64G |
/dev/sda | 1073.7 GB |
数据大小：2000.05MB
workload：测试数据中获取的query_time小于10秒的1108条SQL
workload_path: /root/four_order_workload.txt
1、并发数跟随max_connections变化
较差表现
Latency: 80.38
Throughput: 8.09
{
"binlog_cache_size":"4096",
"innodb_buffer_pool_size":"298928045.90000004",
"innodb_concurrency_tickets":"1",
"innodb_io_capacity":"265247804.8",
"innodb_thread_concurrency":"800.0",
"join_buffer_size":"265247810.4",
"max_connections":"900.1",
"max_length_for_sort_data":"4",
"max_prepared_stmt_count":"1258291.2000000002",
"max_sp_recursion_depth":"102.0",
"max_user_connections":"858993459.0",
"preload_buffer_size":"298403860.3",
"read_buffer_size":"66318499.8",
"sort_buffer_size":"99490856.9",
"table_open_cache":"209715.8"
}
较好表现
Latency: 12.16
Throughput: 7.97
{
"binlog_cache_size":"4096",
"innodb_buffer_pool_size":"233664675.70000002",
"innodb_concurrency_tickets":"165779866.0",
"innodb_io_capacity":"132623952.4",
"innodb_thread_concurrency":"800.0",
"join_buffer_size":"99468008.9",
"max_connections":"100.9",
"max_length_for_sort_data":"2516585.2",
"max_prepared_stmt_count":"419430.4",
"max_sp_recursion_depth":"0",
"max_user_connections":"858993459.0",
"preload_buffer_size":"99468636.1",
"read_buffer_size":"8192",
"sort_buffer_size":"265254338.4",
"table_open_cache":"471859.3"
}
2、并发数固定60测试，max_connections随机变化
Latency: 7.75
Throughput: 7.55
{
"binlog_cache_size":"2600",
"innodb_buffer_pool_size":"262144",
"innodb_concurrency_tickets":"32768",
"innodb_io_capacity":"262144",
"innodb_thread_concurrency":"32768",
"join_buffer_size":"4096",
"max_connections":"16382",
"max_length_for_sort_data":"0",
"max_prepared_stmt_count":"2560",
"max_sp_recursion_depth":"131072",
"max_user_connections":"200",
"preload_buffer_size":"134217728",
"read_buffer_size":"4000",
"sort_buffer_size":"0",
"table_open_cache":"5000"
}
较好表现
Latency: 1.27
Throughput: 5.33
{
"binlog_cache_size":"6275948.0",
"innodb_buffer_pool_size":"129470149.1",
"innodb_concurrency_tickets":"55617925.2",
"innodb_io_capacity":"24902.8",
"innodb_thread_concurrency":"988.0",
"join_buffer_size":"143075599.4",
"max_connections":"10",
"max_length_for_sort_data":"7544442.6",
"max_prepared_stmt_count":"1161554.6",
"max_sp_recursion_depth":"221.0",
"max_user_connections":"4171107307.0",
"preload_buffer_size":"198445776.2",
"read_buffer_size":"331353478.2",
"sort_buffer_size":"232645872.0",
"table_open_cache":"3870.0"
}
3、并发数固定60测试，排除max_connections影响
较差表现
Latency: 8.43
Throughput: 6.9
{
"binlog_cache_size":"80107935.6",
"innodb_buffer_pool_size":"236927843.5",
"innodb_concurrency_tickets":"207954262.4",
"innodb_io_capacity":"17241200.0",
"innodb_thread_concurrency":"7.6",
"join_buffer_size":"260075478.8",
"max_length_for_sort_data":"5001287.0",
"max_prepared_stmt_count":"452984.4",
"max_sp_recursion_depth":"66.0",
"max_user_connections":"2559800507.0",
"preload_buffer_size":"190647278.5",
"read_buffer_size":"303046298.0",
"sort_buffer_size":"302385357.0",
"table_open_cache":"398455.8"
}
较好表现
Latency: 7.33
Throughput: 7.93
{
"binlog_cache_size":"53052997.0",
"innodb_buffer_pool_size":"253243686.4",
"innodb_concurrency_tickets":"212198228.2",
"innodb_io_capacity":"23209274.099999998",
"innodb_thread_concurrency":"20.0",
"join_buffer_size":"265247810.0",
"max_length_for_sort_data":"2852128.8",
"max_prepared_stmt_count":"377487.0",
"max_sp_recursion_depth":"61.2",
"max_user_connections":"2748779068.8",
"preload_buffer_size":"132624506.6",
"read_buffer_size":"305035607.8",
"sort_buffer_size":"311668113.1",
"table_open_cache":"262144.2"
}
Last modified 1mo ago