TPCC-MySQL
来源：互联网 发布：开票软件金税盘版 编辑：程序博客网 时间：2024/05/17 02:45
1. 概述
测试Shannon Flash卡在MySQL 5.5,5.6版本的性能表现，采用tpcc-mysql测试。
2. 软硬件选择
2.1. 服务器
测试服务器我们采用了普通的PC服务器，PowerEdge R720。
主机型号
CPU
内存
磁盘
PowerEdge R720
Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
32G
1*sas300G
2.2. 操作系统
系统：CentOS release 6.5 (Final)
Kernel：2.6.32-431.el6.x86_64
2.3. 文件系统
使用ext3文件系统。
2.4. MySQL版本
MySQL 5.5.24 和MySQL 5.6.17
3. 测试结果
测试数据1000 data warehouse，数据量在100GB左右，由于机器内存不够，分配了9G的innodb buffer pool, 8 个innodb buffer pool instances, innodb_flush_log_at_trx_commit 为1,即每次commit 都会 flush logbuffer，16个thread.
3.1. NOTP
3.2. 90% Latency
图中显示，innodb log file size 越大，latency 越低。
4. 测试配置
使用percona-toolkit收集MySQL 和系统信息。
4.1. pt-summary
# Percona Toolkit SystemSummary Report ######################
Date | 2014-07-28 07:44:03 UTC (localTZ: CST +0800)
Hostname | rac2
Uptime | 2 days, 23:31, 9 users, load average: 9.50, 9.65, 9.49
System | Dell Inc.; PowerEdge R720; vNotSpecified (<OUT OF SPEC>)
Service Tag | 4TR4R12
Platform | Linux
Release | CentOS release 6.5 (Final)
Kernel | 2.6.32-431.el6.x86_64
Architecture | CPU = 64-bit, OS= 64-bit
Threading | NPTL 2.12
Compiler | GNU CC version 4.4.7 20120313(Red Hat 4.4.7-4).
SELinux | Disabled
Virtualized | No virtualization detected
# Processor##################################################
Processors | physical = 2, cores = 8, virtual= 8, hyperthreading = no
Speeds | 8x2499.851
Models | 8xIntel(R) Xeon(R) CPU E5-2609v2 @ 2.50GHz
Caches | 8x10240 KB
# Memory#####################################################
Total | 31.3G
Free | 192.5M
Used | physical = 31.1G, swap allocated= 3.9G, swap used = 2.6G, virtual = 33.7G
Buffers | 109.7M
Caches | 1.1G
Dirty | 143628 kB
UsedRSS | 9.6G
Swappiness | 60
DirtyPolicy | 20, 10
DirtyStatus | 0, 0
Locator Size Speed Form Factor Type Type Detail
========= ======== ============================== ============= ===========
1333 MHz
1333 MHz
1333 MHz
1333 MHz
DIMM_A1 8192 MB 1600 MHz DIMM DDR3 Synchronous Registered (Buffered)
DIMM_A2 8192 MB 1600 MHz DIMM DDR3 Synchronous Registered (Buffered)
DIMM_B1 8192 MB 1600 MHz DIMM DDR3 Synchronous Registered (Buffered)
DIMM_B2 8192 MB 1600 MHz DIMM DDR3 Synchronous Registered (Buffered)
DIMM_A10 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A11 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A12 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A3 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A4 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A5 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A6 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A7 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A8 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_A9 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B10 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B11 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B12 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B3 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B4 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B5 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B6 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B7 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B8 {EMPTY} Unknown DIMM DDR3 Synchronous
DIMM_B9 {EMPTY} Unknown DIMM DDR3 Synchronous
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
Unknown
# Mounted Filesystems########################################
Filesystem Size Used Type Opts Mountpoint
/dev/mapper/shannon-mysql 493G 35% ext3 rw /data1
/dev/sda1 9.7G 3% ext4 rw /boot
/dev/sda3 262G 11% ext4 rw /
tmpfs 16G 1% tmpfs rw /dev/shm
# Disk Schedulers And QueueSize #############################
dfa | 128
dm-0 | 128
dm-1 | 128
sda | [cfq] 128
sr0 | [cfq] 128
# Disk Partioning############################################
Device Type Start End Size
============ ==== ==================== ==================
/dev/dfa Disk 1200000860160
/dev/dm-0 Disk 107374182400
/dev/dm-1 Disk 536870912000
/dev/sda Disk 300000000000
/dev/sda1 Part 1 1275 10479006720
/dev/sda2 Part 1275 1785 4194892800
/dev/sda3 Part 1785 36473 285318512640
# Kernel Inode State#########################################
dentry-state | 11910 4886 45 0 0 0
file-nr | 3040 0 6815744
inode-nr | 12235 1356
# LVM Volumes################################################
LV VG Attr LSize Pool Origin Data% Move LogCpy%Sync Convert
mysql shannon -wi-ao---- 500.00g
orion shannon -wi-a----- 100.00g
# LVM Volume Groups##########################################
VG VSize VFree
shannon 1.09t 517.59g
# RAID Controller############################################
Controller | LSI Logic MegaRAID SAS
RAID controller software not found; trygetting it from
your package repository or themanufacturer's website
# Network Config#############################################
Controller | Broadcom Corporation NetXtremeBCM5720 Gigabit Ethernet PCIe
Controller | Broadcom Corporation NetXtremeBCM5720 Gigabit Ethernet PCIe
Controller | Broadcom Corporation NetXtremeBCM5720 Gigabit Ethernet PCIe
Controller | Broadcom Corporation NetXtremeBCM5720 Gigabit Ethernet PCIe
FIN Timeout | 60
Port Range | 65500
# Interface Statistics#######################################
interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ==================== ========== ==========
lo 2500000000 4000000000 02500000000 4000000000 0
em1 300000000 1500000 0 125000000 350000 0
em2 0 0 0 0 0 0
em3 0 0 0 0 0 0
em4 0 0 0 0 0 0
# Network Devices ############################################
Device Speed Duplex
========= ========= =========
em2 Unknown! Unknown!
em3 Unknown! Unknown!
em4 Unknown! Unknown!
em1 1000Mb/s Full
# Network Connections ########################################
Connections from remote IP addresses
127.0.0.1 15
192.168.0.189 1
192.168.0.236 9
Connections to local IP addresses
127.0.0.1 15
192.168.0.65 10
Connections to top 10 local ports
22 10
33346 1
33347 1
33348 1
33349 1
33350 1
33351 1
33352 1
33353 1
33354 1
States of connections
ESTABLISHED 40
LISTEN 15
TIME_WAIT 2
# Top Processes##############################################
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
44674 mysql 20 0 14.9g 9.5g 4124 S 545.4 30.2 156:15.44 mysqld
44904 root 20 0 1084m 7424 988 S 73.1 0.0 20:05.30 tpcc_start
657 root 20 0 0 0 0 R 31.6 0.0 121:57.48 shannon_comp_wq
659 root 20 0 0 0 0 S 4.0 0.0 9:14.40 handle_luna/1
660 root 20 0 0 0 0 S 4.0 0.0 7:45.10 handle_luna/2
661 root 20 0 0 0 0 S 4.0 0.0 7:32.51 handle_luna/3
31643 root 20 0 15168 1156 800 S 4.0 0.0 0:12.31 top
658 root 20 0 0 0 0 S 2.0 0.0 9:29.46 handle_luna/0
687 root 20 0 0 0 0 S 2.0 0.0 8:46.99 shn_gc_threada
# Notable Processes##########################################
PID OOM COMMAND
2292 -17 sshd
# The End####################################################
5.2. pt-mysql-summary
# Percona Toolkit MySQL SummaryReport #######################
System time | 2014-07-28 07:57:44UTC (local TZ: CST +0800)
# Instances##################################################
Port Data Directory NiceOOM Socket
===== ========================== ==== =========
3306 /data1 0 0 /tmp/mysqld.sock
# MySQL Executable###########################################
Path to executable |/usr/local/mysql-5.6.17/bin/mysqld
Has symbols | Yes
# Report On Port 3306########################################
User | root@localhost
Time | 2014-07-28 15:57:44(CST)
Hostname | rac2
Version | 5.6.17-log Sourcedistribution
Built On | Linux x86_64
Started | 2014-07-28 15:57(up 0+00:00:06)
Databases | 6
Datadir | /data1/
Processes | 1 connected, 1running
Replication | Is not a slave, has0 slaves connected
Pidfile | /data1/rac2.pid(exists)
# Processlist################################################
Command COUNT(*) WorkingSUM(Time) MAX(Time)
------------------------------ --------------- --------- ---------
Query 1 1 0 0
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ --------------- --------- ---------
root 1 1 0 0
Host COUNT(*) WorkingSUM(Time) MAX(Time)
------------------------------ --------------- --------- ---------
localhost 1 1 0 0
db COUNT(*) WorkingSUM(Time) MAX(Time)
------------------------------ --------------- --------- ---------
NULL 1 1 0 0
State COUNT(*) WorkingSUM(Time) MAX(Time)
------------------------------ --------------- --------- ---------
init 1 1 0 0
# Status Counters (Wait 10Seconds) ##########################
Variable Per day Per second 10 secs
Bytes_received 6000000 70 200
Bytes_sent 200000000 2500 3500
Com_select 60000 1
Com_show_status 15000
Com_show_variables 15000
Connections 60000 1
Created_tmp_files 90000 1
Created_tmp_tables 30000 6
Flush_commands 15000
Handler_external_lock 1000000 10
Handler_read_first 45000
Handler_read_rnd_next 6000000 70 40
Handler_write 6000000 70 40
Innodb_buffer_pool_bytes_data 5000000000000 60000000 90000000
Innodb_buffer_pool_bytes_dirty 1500000000000 17500000 -4936499
Innodb_buffer_pool_pages_flushed 200000000 2500 7000
Innodb_buffer_pool_read_requests 7000000000 80000 150000
Innodb_buffer_pool_reads 100000000 1000 900
Innodb_buffer_pool_write_requests 3500000000 40000 70000
Innodb_data_fsyncs 8000000 90 250
Innodb_data_read 5000000000000 60000000 90000000
Innodb_data_reads 300000000 3500 5000
Innodb_data_writes 225000000 2500 7000
Innodb_data_written 7000000000000 90000000 225000000
Innodb_dblwr_pages_written 225000000 2500 7000
Innodb_dblwr_writes 2000000 25 60
Innodb_log_write_requests 600000000 7000 12500
Innodb_log_writes 450000 5 20
Innodb_os_log_fsyncs 450000 5 20
Innodb_os_log_written 150000000000 1750000 3000000
Innodb_pages_read 300000000 3500 5000
Innodb_pages_written 225000000 2500 7000
Innodb_num_open_files 100000 1
Innodb_available_undo_logs 1750000 20
Open_table_definitions 450000 5
Opened_files 1750000 20 9
Opened_table_definitions 450000 5
Opened_tables 450000 5
Queries 125000 1 4
Questions 125000 1 4
Select_scan 30000
Table_locks_immediate 500000 5
Table_open_cache_hits 45000
Table_open_cache_misses 450000 5
Threads_created 15000
Uptime 90000 1 1
# Table cache################################################
Size | 5120
Usage | 0%
# Key Percona Server features################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster#####################################
# Plugins####################################################
InnoDB compression | ACTIVE
# Query cache################################################
query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
# Schema#####################################################
Specify --databases or--all-databases to dump and summarize schemas
# Noteworthy Technologies####################################
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
# InnoDB#####################################################
Version | 5.6.17
Buffer Pool Size | 9.0G
Buffer Pool Fill | 3%
Buffer Pool Dirty | 1%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 7.8G = 15.6G
Log Buffer Size | 16M
Flush Method | O_DIRECT
Flush Log At Commit | 1
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 8 16
I/O Capacity | 10000
Thread Concurrency | 0
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | READ-COMMITTED
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 11M
InnoDB Queue | 0 queries insideInnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 661
Read Views | 0
Undo Log Entries | 0 transactions, 0total undo, 0 max undo
Pending I/O Reads | 2 buf pool reads, 0normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU,0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 1xnot started
Semaphore Waits
5 btr0cur.cc line 554
1ibuf0ibuf.cc line 4508
1 btr0cur.cc line 278
1 btr0cur.cc line 1025
Semaphore Holders
7 thread id 140458356610816
Mutexes/Locks Waited For
6 lock on RW-latch at 0x11b8d148 createdin file dict0dict.cc line 2420
1 Mutex at 0x134a6a0 created fileibuf0ibuf.cc line 533
# MyISAM#####################################################
Key Cache | 32.0M
Pct Used | 20%
Unflushed | 0%
# Security###################################################
Users | 6 users, 2 anon, 5w/o pw, 5 old pw
Old Passwords | 0
# Binary Logging#############################################
Binlogs | 46
Zero-Sized | 0
Total Size | 39.0G
binlog_format | ROW
expire_logs_days | 1
sync_binlog | 0
server_id | 1
binlog_do_db |
binlog_ignore_db |
# Noteworthy Variables#######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect | set names latin1
init_file |
sql_mode |NO_ENGINE_SUBSTITUTION
join_buffer_size | 2M
sort_buffer_size | 2M
read_buffer_size | 2M
read_rnd_buffer_size | 16M
bulk_insert_buffer | 0.00
max_heap_table_size | 2G
tmp_table_size | 128M
max_allowed_packet | 1G
thread_stack | 192k
log |
log_error | /data1/mysql.err
log_warnings | 1
log_slow_queries |
log_queries_not_using_indexes |OFF
log_slave_updates | ON
# Configuration File#########################################
Config File | /etc/my.cnf
[client]
port = 3306
socket =/tmp/mysqld.sock
[mysqld]
port = 3306
socket = /tmp/mysqld.sock
back_log = 300
max_connections = 10000
max_connect_errors = 10
transaction_isolation = READ-COMMITTED
max_allowed_packet = 1024M
open_files_limit = 8192
read_only
init_connect = "set nameslatin1"
query_cache_size = 0
query_cache_limit = 2M
query_cache_type = 0
log_bin = /data1/mysql-bin
binlog_format = row
log_slave_updates = on
sync_binlog = 0
expire_logs_days = 1
slow-query-log
long_query_time = 2
slow_query_log_file = mysql-slow.log
binlog_cache_size = 2M
sort_buffer_size = 2M
join_buffer_size = 2M
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
thread_stack = 192K
myisam_sort_buffer_size = 128M
innodb_additional_mem_pool_size = 50M
innodb_buffer_pool_size = 9G
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 8000M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 60
innodb_stats_on_metadata = 0
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 8
thread_cache_size = 32
table_open_cache = 5120 #MySQL 5.6
innodb_write_io_threads = 16
innodb_read_io_threads = 8
innodb_io_capacity = 10000
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_file_per_table = 1
datadir = /data1
innodb_log_group_home_dir = /data1/
innodb_data_home_dir = /data1
innodb_data_file_path = ibdata1:2048M:autoextend
log_error = mysql.err
pid_file = mysql.pid
server-id = 1
relay_log_space_limit = 10G
skip-slave-start
relay_log = binlog/relay
relay_log_info_file = binlog/relay.info
master_info_file = binlog/master.info
tmp_table_size = 128M
ft_min_word_len = 4
tmp_table_size = 128M
max_heap_table_size = 2048M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
# The End####################################################
- TPCC-MySQL
- tpcc mysql 基准测试
- mysql-tpcc使用方法
- TPCC-MySQL使用说明
- tpcc-mysql 安装
- TPCC-MySQL使用手册
- tpcc-mysql 性能测试
- TPCC-MySQL基准测试
- tpcc-mysql安装
- tpcc-mysql的使用
- TPCC-MySQL基准测试
- TPCC-MySQL基准测试
- tpcc-mysql使用
- Tpcc-MySQL测试
- Tpcc-MySQL测试详解
- ubuntu安装tpcc-mysql
- MySQL压测sysbench/tpcc
- tpcc-mysql安装与使用
- XDM标准XDM_BufDesc数据结构定义
- 用Spring提高java观察者模式灵活性
- RedHat Linux9下用bootsplash制作全图形启动界面
- 基于dedecms 递归
- deeplearning 相关网址~
- TPCC-MySQL
- IllegalStateException: Can not perform this action after onSaveInstanceState
- 前端工程与性能优化
- insight-6.8调试工具安装
- Js数组里删除指定的元素（不是指定的位置）
- 把CentOS 系统里的网卡em1还原为eth0
- 解决Android SDK Manager下载太慢问题
- dispose与close()的区别
- windows下 管理员身份启动java进程
