Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
Not 5.6.x
-
None
-
pt-summary
[root@centos7-1 ~]# pt-summary # Percona Toolkit System Summary Report ###################### Date | 2019-02-14 18:53:05 UTC (local TZ: EST -0500) Hostname | centos7-1.localdomain Uptime | 54 min, 1 user, load average: 0.47, 0.14, 0.08 System | innotek GmbH; VirtualBox; v1.2 (Other) Service Tag | 0 Platform | Linux Release | CentOS Linux release 7.5.1804 (Core) Kernel | 3.10.0-862.14.4.el7.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.17 SELinux | Disabled Virtualized | KVM # Processor ################################################## Processors | physical = 1, cores = 1, virtual = 1, hyperthreading = no Speeds | 1x2674.960 Models | 1xIntel(R) Core(TM) i5 CPU 750 @ 2.67GHz Caches | 1x8192 KB # Memory ##################################################### Total | 1.8G Free | 1.2G Used | physical = 259.2M, swap allocated = 3.9G, swap used = 0.0, virtual = 259.2M Shared | 8.5M Buffers | 366.1M Caches | 1.4G Dirty | 33088 kB UsedRSS | 281.7M Swappiness | 30 DirtyPolicy | 30, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== # Mounted Filesystems ######################################## Filesystem Size Used Type Opts Mountpoint /dev/mapper/centos-home 46G 1% xfs rw,relatime,attr2,inode64,noquota /home /dev/mapper/centos-root 50G 4% xfs rw,relatime,attr2,inode64,noquota / /dev/sda1 1014M 23% xfs rw,relatime,attr2,inode64,noquota /boot devtmpfs 908M 0% devtmpfs rw,nosuid,size=929568k,nr_inodes=232392,mode=755 /dev tmpfs 184M 0% tmpfs rw,nosuid,nodev /run/user/0 tmpfs 184M 0% tmpfs rw,nosuid,nodev,mode=755 /run/user/0 tmpfs 184M 0% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /run/user/0 tmpfs 184M 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /run/user/0 tmpfs 920M 0% tmpfs rw,nosuid,nodev /dev/shm tmpfs 920M 0% tmpfs rw,nosuid,nodev,mode=755 /dev/shm tmpfs 920M 0% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /dev/shm tmpfs 920M 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /dev/shm tmpfs 920M 0% tmpfs rw,nosuid,nodev /sys/fs/cgroup tmpfs 920M 0% tmpfs rw,nosuid,nodev,mode=755 /sys/fs/cgroup tmpfs 920M 0% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /sys/fs/cgroup tmpfs 920M 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /sys/fs/cgroup tmpfs 920M 1% tmpfs rw,nosuid,nodev /run tmpfs 920M 1% tmpfs rw,nosuid,nodev,mode=755 /run tmpfs 920M 1% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /run tmpfs 920M 1% tmpfs ro,nosuid,nodev,noexec,mode=755 /run # Disk Schedulers And Queue Size ############################# dm-0 | 128 dm-1 | 128 dm-2 | 128 sda | [deadline] 128 sr0 | [deadline] 128 # Disk Partioning ############################################ Device Type Start End Size ============ ==== ========== ========== ================== /dev/dm-0 Disk 53687091200 /dev/dm-1 Disk 4160749568 /dev/dm-2 Disk 48444211200 /dev/sda Disk 107374182400 /dev/sda1 Part 2048 2099199 1073741312 /dev/sda2 Part 2099200 209715199 106299391488 # Kernel Inode State ######################################### dentry-state | 23836 14184 45 0 0 0 file-nr | 800 0 183744 inode-nr | 18232 310 # LVM Volumes ################################################ LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home centos -wi-ao---- <45.12g root centos -wi-ao---- 50.00g swap centos -wi-ao---- <3.88g # LVM Volume Groups ########################################## VG VSize VFree centos <99.00g 4.00m # RAID Controller ############################################ Controller | No RAID controller detected # Network Config ############################################# FIN Timeout | 60 Port Range | 60999 # Interface Statistics ####################################### interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors ========= ========= ========== ========== ========== ========== ========== lo 0 0 0 0 0 0 enp0s3 15000000 10000 0 500000 3000 0 # Network Devices ############################################ Device Speed Duplex ========= ========= ========= enp0s3 1000Mb/s Full # Network Connections ######################################## Connections from remote IP addresses 10.0.2.2 1 23.194.109.169 4 74.121.199.234 4 104.28.4.119 2 107.161.35.6 1 129.97.134.71 3 137.82.116.42 2 142.58.101.156 1 144.217.74.2 1 162.221.206.179 1 162.253.53.24 1 173.209.34.179 2 192.75.96.254 1 192.175.120.169 1 199.87.154.255 3 207.210.46.249 2 208.90.99.253 4 216.110.253.148 3 Connections to local IP addresses 10.0.2.12 35 Connections to top 10 local ports 54956 1 54958 1 55058 1 55130 1 56028 1 56930 1 56934 1 56942 1 56950 1 57180 1 States of connections ESTABLISHED 1 LISTEN 3 TIME_WAIT 35 # Top Processes ############################################## PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 10613 root 20 0 161840 2080 1524 R 6.2 0.1 0:00.01 top 1 root 20 0 125312 3796 2572 S 0.0 0.2 0:00.99 systemd 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd 3 root 20 0 0 0 0 S 0.0 0.0 0:00.06 ksoftirqd/0 5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H 6 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kworker/u2:0 7 root rt 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh 9 root 20 0 0 0 0 S 0.0 0.0 0:00.24 rcu_sched # Notable Processes ########################################## PID OOM COMMAND 800 -17 sshd # Memory mamagement ########################################## Transparent huge pages are enabled. # The End ####################################################
pt-mysql-summary
[root@centos7-1 ~]# pt-mysql-summary --user=root --password=password mysql: [Warning] Using a password on the command line interface can be insecure. # Percona Toolkit MySQL Summary Report ####################### System time | 2019-02-14 18:53:39 UTC (local TZ: EST -0500) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== 0 0 # MySQL Executable ########################################### Path to executable | /usr/sbin/mysqld Has symbols | No # Slave Hosts ################################################ No slaves found # Report On Port 3306 ######################################## User | root@localhost Time | 2019-02-14 13:53:39 (EST) Hostname | centos7-1.localdomain Version | 5.7.24-27 Percona Server (GPL), Release 27, Revision bd42700 Built On | Linux x86_64 Started | 2019-02-14 13:03 (up 0+00:50:08) Databases | 4 Datadir | /var/lib/mysql/ Processes | 1 connected, 1 running Replication | Is not a slave, has 0 slaves connected Pidfile | /var/run/mysqld/mysqld.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Query 1 1 0 0 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- root 1 1 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 1 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 1 1 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- starting 1 1 0 0# Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 11 secs Bytes_received 40000 450 Bytes_sent 1250000 15 3000 Com_select 200 2 Com_show_status 90 Com_show_variables 30 Connections 175 1 Created_tmp_files 175 Created_tmp_tables 125 8 Flush_commands 30 Handler_commit 150 Handler_external_lock 6000 Handler_read_first 225 Handler_read_key 175 Handler_read_next 60 Handler_read_rnd_next 80000 100 Handler_write 40000 50 Innodb_background_log_sync 90000 1 Innodb_buffer_pool_bytes_data 100000000 1250 3000 Innodb_buffer_pool_pages_flushed 1000 Innodb_buffer_pool_read_requests 125000 1 25 Innodb_buffer_pool_reads 6000 Innodb_buffer_pool_write_requests 9000 25 Innodb_checkpoint_age 250 Innodb_checkpoint_max_age 2250000000 25000 Innodb_data_fsyncs 225 Innodb_data_read 100000000 1250 Innodb_data_reads 7000 Innodb_data_writes 1750 Innodb_data_written 17500000 200 6000 Innodb_dblwr_pages_written 60 Innodb_dblwr_writes 30 Innodb_ibuf_segment_size 60 Innodb_log_writes 60 Innodb_lsn_current 70000000 900 Innodb_lsn_flushed 70000000 900 Innodb_lsn_last_checkpoint 70000000 900 Innodb_master_thread_active_loops 60 Innodb_master_thread_idle_loops 90000 Innodb_max_trx_id 80000 Innodb_mem_adaptive_hash 60000000 700 Innodb_mem_dictionary 20000000 225 Innodb_os_log_fsyncs 125 Innodb_os_log_written 30000 Innodb_pages_created 1000 Innodb_pages_read 6000 Innodb_pages0_read 500 Innodb_pages_written 1000 Innodb_rows_read 600 6 Innodb_num_open_files 600 Innodb_available_undo_logs 3500 Key_read_requests 175 Key_reads 90 Open_table_definitions 3000 Opened_files 17500 Opened_table_definitions 3000 Opened_tables 3000 Qcache_not_cached 200 2 Queries 450 4 Questions 350 4 Select_scan 200 Table_locks_immediate 3000 Table_open_cache_hits 90 Table_open_cache_misses 3000 Threads_created 30 Uptime 90000 1 1 rocksdb_memtable_total 50000 rocksdb_memtable_unflushed 50000 rocksdb_block_cache_add 90 rocksdb_block_cache_bytes_read 25000 rocksdb_block_cache_bytes_write 4500 rocksdb_block_cache_data_add 90 rocksdb_block_cache_data_bytes_insert 4500 rocksdb_block_cache_data_hit 500 rocksdb_block_cache_data_miss 90 rocksdb_block_cache_hit 500 rocksdb_block_cache_miss 90 rocksdb_bytes_read 350 rocksdb_bytes_written 1750 rocksdb_iter_bytes_read 400 rocksdb_memtable_hit 60 rocksdb_memtable_miss 60 rocksdb_no_file_opens 90 rocksdb_number_db_seek 150 rocksdb_number_db_seek_found 30 rocksdb_number_keys_read 125 rocksdb_number_keys_written 60 rocksdb_number_superversion_acquires 60 rocksdb_wal_bytes 1750 rocksdb_wal_synced 60 rocksdb_write_self 60 rocksdb_write_wal 125 # Table cache ################################################ Size | 2000 Usage | 5% # Key Percona Server features ################################ Table & Index Stats | Disabled Multiple I/O Threads | Enabled Corruption Resilient | Enabled Durable Replication | Not Supported Import InnoDB Tables | Not Supported Fast Server Restarts | Not Supported Enhanced Logging | Disabled 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 | 1.0M Usage | 1% 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.7.24-27 Buffer Pool Size | 128.0M Buffer Pool Fill | 2% Buffer Pool Dirty | 0% File Per Table | ON Page Size | 16k Log File Size | 2 * 48.0M = 96.0M Log Buffer Size | 16M Flush Method | Flush Log At Commit | 1 XA Support | ON Checksums | ON Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0 Concurrency Tickets | 5000 Commit Concurrency | 0 Txn Isolation Level | REPEATABLE-READ Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 9 InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue Oldest Transaction | 0 Seconds History List Len | 0 Read Views | 0 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo Pending I/O Reads | 0 buf pool reads, 0 normal 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 # RocksDB #################################################### Block Cache Size | 512M Block Size | 4k Bytes Per Sync | 0 Compaction Seq Deletes | 0 Compaction Seq Deletes Count SD | OFF Compaction Seq Deletes Window | 0 Default CF Options | compression=kLZ4Compression;bottommost_compression=kLZ4Compression Max Background Jobs | 2 Max Block Cache Size | 0.00 Max Block Size | 0.00 Max Open Files | 2k Max Total Wal Size | 0 Rate Limiter Bytes Per Second | 0 Rate Limiter Bytes Per Sync | 0 Rate Limiter Wal Bytes Per Sync | 0 Table Cache NumHardBits | 6 Wal Bytes per Sync | 0 # MyISAM ##################################################### Key Cache | 8.0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | 2 users, 0 anon, 0 w/o pw, 0 old pw Old Passwords | 0 # Encryption ################################################# mysql: [Warning] Using a password on the command line interface can be insecure. No keyring plugins found # Binary Logging ############################################# # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 1/1 default_storage_engine | InnoDB flush_time | 0 init_connect | init_file | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION join_buffer_size | 256k sort_buffer_size | 256k read_buffer_size | 128k read_rnd_buffer_size | 256k bulk_insert_buffer | 0.00 max_heap_table_size | 16M tmp_table_size | 16M max_allowed_packet | 4M thread_stack | 256k log | log_error | /var/log/mysqld.log log_warnings | 2 log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | OFF # Configuration File ######################################### Config File | /etc/my.cnf[mysqld] rocksdb_cache_index_and_filter_blocks = OFF # Memory management library ################################## jemalloc is not enabled in mysql config for process with id 1279 # The End ####################################################
pt-summary [root@centos7-1 ~]# pt-summary # Percona Toolkit System Summary Report ###################### Date | 2019-02-14 18:53:05 UTC (local TZ: EST -0500) Hostname | centos7-1.localdomain Uptime | 54 min, 1 user, load average: 0.47, 0.14, 0.08 System | innotek GmbH; VirtualBox; v1.2 (Other) Service Tag | 0 Platform | Linux Release | CentOS Linux release 7.5.1804 (Core) Kernel | 3.10.0-862.14.4.el7.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.17 SELinux | Disabled Virtualized | KVM # Processor ################################################## Processors | physical = 1, cores = 1, virtual = 1, hyperthreading = no Speeds | 1x2674.960 Models | 1xIntel(R) Core(TM) i5 CPU 750 @ 2.67GHz Caches | 1x8192 KB # Memory ##################################################### Total | 1.8G Free | 1.2G Used | physical = 259.2M, swap allocated = 3.9G, swap used = 0.0, virtual = 259.2M Shared | 8.5M Buffers | 366.1M Caches | 1.4G Dirty | 33088 kB UsedRSS | 281.7M Swappiness | 30 DirtyPolicy | 30, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== # Mounted Filesystems ######################################## Filesystem Size Used Type Opts Mountpoint /dev/mapper/centos-home 46G 1% xfs rw,relatime,attr2,inode64,noquota /home /dev/mapper/centos-root 50G 4% xfs rw,relatime,attr2,inode64,noquota / /dev/sda1 1014M 23% xfs rw,relatime,attr2,inode64,noquota /boot devtmpfs 908M 0% devtmpfs rw,nosuid,size=929568k,nr_inodes=232392,mode=755 /dev tmpfs 184M 0% tmpfs rw,nosuid,nodev /run/user/0 tmpfs 184M 0% tmpfs rw,nosuid,nodev,mode=755 /run/user/0 tmpfs 184M 0% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /run/user/0 tmpfs 184M 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /run/user/0 tmpfs 920M 0% tmpfs rw,nosuid,nodev /dev/shm tmpfs 920M 0% tmpfs rw,nosuid,nodev,mode=755 /dev/shm tmpfs 920M 0% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /dev/shm tmpfs 920M 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /dev/shm tmpfs 920M 0% tmpfs rw,nosuid,nodev /sys/fs/cgroup tmpfs 920M 0% tmpfs rw,nosuid,nodev,mode=755 /sys/fs/cgroup tmpfs 920M 0% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /sys/fs/cgroup tmpfs 920M 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /sys/fs/cgroup tmpfs 920M 1% tmpfs rw,nosuid,nodev /run tmpfs 920M 1% tmpfs rw,nosuid,nodev,mode=755 /run tmpfs 920M 1% tmpfs rw,nosuid,nodev,relatime,size=188284k,mode=700 /run tmpfs 920M 1% tmpfs ro,nosuid,nodev,noexec,mode=755 /run # Disk Schedulers And Queue Size ############################# dm-0 | 128 dm-1 | 128 dm-2 | 128 sda | [deadline] 128 sr0 | [deadline] 128 # Disk Partioning ############################################ Device Type Start End Size ============ ==== ========== ========== ================== /dev/dm-0 Disk 53687091200 /dev/dm-1 Disk 4160749568 /dev/dm-2 Disk 48444211200 /dev/sda Disk 107374182400 /dev/sda1 Part 2048 2099199 1073741312 /dev/sda2 Part 2099200 209715199 106299391488 # Kernel Inode State ######################################### dentry-state | 23836 14184 45 0 0 0 file-nr | 800 0 183744 inode-nr | 18232 310 # LVM Volumes ################################################ LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home centos -wi-ao---- <45.12g root centos -wi-ao---- 50.00g swap centos -wi-ao---- <3.88g # LVM Volume Groups ########################################## VG VSize VFree centos <99.00g 4.00m # RAID Controller ############################################ Controller | No RAID controller detected # Network Config ############################################# FIN Timeout | 60 Port Range | 60999 # Interface Statistics ####################################### interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors ========= ========= ========== ========== ========== ========== ========== lo 0 0 0 0 0 0 enp0s3 15000000 10000 0 500000 3000 0 # Network Devices ############################################ Device Speed Duplex ========= ========= ========= enp0s3 1000Mb/s Full # Network Connections ######################################## Connections from remote IP addresses 10.0.2.2 1 23.194.109.169 4 74.121.199.234 4 104.28.4.119 2 107.161.35.6 1 129.97.134.71 3 137.82.116.42 2 142.58.101.156 1 144.217.74.2 1 162.221.206.179 1 162.253.53.24 1 173.209.34.179 2 192.75.96.254 1 192.175.120.169 1 199.87.154.255 3 207.210.46.249 2 208.90.99.253 4 216.110.253.148 3 Connections to local IP addresses 10.0.2.12 35 Connections to top 10 local ports 54956 1 54958 1 55058 1 55130 1 56028 1 56930 1 56934 1 56942 1 56950 1 57180 1 States of connections ESTABLISHED 1 LISTEN 3 TIME_WAIT 35 # Top Processes ############################################## PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 10613 root 20 0 161840 2080 1524 R 6.2 0.1 0:00.01 top 1 root 20 0 125312 3796 2572 S 0.0 0.2 0:00.99 systemd 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd 3 root 20 0 0 0 0 S 0.0 0.0 0:00.06 ksoftirqd/0 5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H 6 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kworker/u2:0 7 root rt 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh 9 root 20 0 0 0 0 S 0.0 0.0 0:00.24 rcu_sched # Notable Processes ########################################## PID OOM COMMAND 800 -17 sshd # Memory mamagement ########################################## Transparent huge pages are enabled. # The End #################################################### pt-mysql-summary [root@centos7-1 ~]# pt-mysql-summary --user=root --password=password mysql: [Warning] Using a password on the command line interface can be insecure. # Percona Toolkit MySQL Summary Report ####################### System time | 2019-02-14 18:53:39 UTC (local TZ: EST -0500) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== 0 0 # MySQL Executable ########################################### Path to executable | /usr/sbin/mysqld Has symbols | No # Slave Hosts ################################################ No slaves found # Report On Port 3306 ######################################## User | root@localhost Time | 2019-02-14 13:53:39 (EST) Hostname | centos7-1.localdomain Version | 5.7.24-27 Percona Server (GPL), Release 27, Revision bd42700 Built On | Linux x86_64 Started | 2019-02-14 13:03 (up 0+00:50:08) Databases | 4 Datadir | / var /lib/mysql/ Processes | 1 connected, 1 running Replication | Is not a slave, has 0 slaves connected Pidfile | / var /run/mysqld/mysqld.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Query 1 1 0 0 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- root 1 1 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 1 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 1 1 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- starting 1 1 0 0# Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 11 secs Bytes_received 40000 450 Bytes_sent 1250000 15 3000 Com_select 200 2 Com_show_status 90 Com_show_variables 30 Connections 175 1 Created_tmp_files 175 Created_tmp_tables 125 8 Flush_commands 30 Handler_commit 150 Handler_external_lock 6000 Handler_read_first 225 Handler_read_key 175 Handler_read_next 60 Handler_read_rnd_next 80000 100 Handler_write 40000 50 Innodb_background_log_sync 90000 1 Innodb_buffer_pool_bytes_data 100000000 1250 3000 Innodb_buffer_pool_pages_flushed 1000 Innodb_buffer_pool_read_requests 125000 1 25 Innodb_buffer_pool_reads 6000 Innodb_buffer_pool_write_requests 9000 25 Innodb_checkpoint_age 250 Innodb_checkpoint_max_age 2250000000 25000 Innodb_data_fsyncs 225 Innodb_data_read 100000000 1250 Innodb_data_reads 7000 Innodb_data_writes 1750 Innodb_data_written 17500000 200 6000 Innodb_dblwr_pages_written 60 Innodb_dblwr_writes 30 Innodb_ibuf_segment_size 60 Innodb_log_writes 60 Innodb_lsn_current 70000000 900 Innodb_lsn_flushed 70000000 900 Innodb_lsn_last_checkpoint 70000000 900 Innodb_master_thread_active_loops 60 Innodb_master_thread_idle_loops 90000 Innodb_max_trx_id 80000 Innodb_mem_adaptive_hash 60000000 700 Innodb_mem_dictionary 20000000 225 Innodb_os_log_fsyncs 125 Innodb_os_log_written 30000 Innodb_pages_created 1000 Innodb_pages_read 6000 Innodb_pages0_read 500 Innodb_pages_written 1000 Innodb_rows_read 600 6 Innodb_num_open_files 600 Innodb_available_undo_logs 3500 Key_read_requests 175 Key_reads 90 Open_table_definitions 3000 Opened_files 17500 Opened_table_definitions 3000 Opened_tables 3000 Qcache_not_cached 200 2 Queries 450 4 Questions 350 4 Select_scan 200 Table_locks_immediate 3000 Table_open_cache_hits 90 Table_open_cache_misses 3000 Threads_created 30 Uptime 90000 1 1 rocksdb_memtable_total 50000 rocksdb_memtable_unflushed 50000 rocksdb_block_cache_add 90 rocksdb_block_cache_bytes_read 25000 rocksdb_block_cache_bytes_write 4500 rocksdb_block_cache_data_add 90 rocksdb_block_cache_data_bytes_insert 4500 rocksdb_block_cache_data_hit 500 rocksdb_block_cache_data_miss 90 rocksdb_block_cache_hit 500 rocksdb_block_cache_miss 90 rocksdb_bytes_read 350 rocksdb_bytes_written 1750 rocksdb_iter_bytes_read 400 rocksdb_memtable_hit 60 rocksdb_memtable_miss 60 rocksdb_no_file_opens 90 rocksdb_number_db_seek 150 rocksdb_number_db_seek_found 30 rocksdb_number_keys_read 125 rocksdb_number_keys_written 60 rocksdb_number_superversion_acquires 60 rocksdb_wal_bytes 1750 rocksdb_wal_synced 60 rocksdb_write_self 60 rocksdb_write_wal 125 # Table cache ################################################ Size | 2000 Usage | 5% # Key Percona Server features ################################ Table & Index Stats | Disabled Multiple I/O Threads | Enabled Corruption Resilient | Enabled Durable Replication | Not Supported Import InnoDB Tables | Not Supported Fast Server Restarts | Not Supported Enhanced Logging | Disabled 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 | 1.0M Usage | 1% 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.7.24-27 Buffer Pool Size | 128.0M Buffer Pool Fill | 2% Buffer Pool Dirty | 0% File Per Table | ON Page Size | 16k Log File Size | 2 * 48.0M = 96.0M Log Buffer Size | 16M Flush Method | Flush Log At Commit | 1 XA Support | ON Checksums | ON Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0 Concurrency Tickets | 5000 Commit Concurrency | 0 Txn Isolation Level | REPEATABLE-READ Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 9 InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue Oldest Transaction | 0 Seconds History List Len | 0 Read Views | 0 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo Pending I/O Reads | 0 buf pool reads, 0 normal 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 # RocksDB #################################################### Block Cache Size | 512M Block Size | 4k Bytes Per Sync | 0 Compaction Seq Deletes | 0 Compaction Seq Deletes Count SD | OFF Compaction Seq Deletes Window | 0 Default CF Options | compression=kLZ4Compression;bottommost_compression=kLZ4Compression Max Background Jobs | 2 Max Block Cache Size | 0.00 Max Block Size | 0.00 Max Open Files | 2k Max Total Wal Size | 0 Rate Limiter Bytes Per Second | 0 Rate Limiter Bytes Per Sync | 0 Rate Limiter Wal Bytes Per Sync | 0 Table Cache NumHardBits | 6 Wal Bytes per Sync | 0 # MyISAM ##################################################### Key Cache | 8.0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | 2 users, 0 anon, 0 w/o pw, 0 old pw Old Passwords | 0 # Encryption ################################################# mysql: [Warning] Using a password on the command line interface can be insecure. No keyring plugins found # Binary Logging ############################################# # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 1/1 default_storage_engine | InnoDB flush_time | 0 init_connect | init_file | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION join_buffer_size | 256k sort_buffer_size | 256k read_buffer_size | 128k read_rnd_buffer_size | 256k bulk_insert_buffer | 0.00 max_heap_table_size | 16M tmp_table_size | 16M max_allowed_packet | 4M thread_stack | 256k log | log_error | / var /log/mysqld.log log_warnings | 2 log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | OFF # Configuration File ######################################### Config File | /etc/my.cnf[mysqld] rocksdb_cache_index_and_filter_blocks = OFF # Memory management library ################################## jemalloc is not enabled in mysql config for process with id 1279 # The End ####################################################
Description
The page I am referring to can be found here
https://www.percona.com/doc/percona-server/5.7/myrocks/variables.html#rocksdb_db_write_buffer_size
The text currently states....
Specifies the size of the memtable used to store writes in MyRocks. This is the size per column family. When this size is reached, the memtable is flushed to persistent media.
Based on my testing this appears to be maximum allowed size for all write buffers in the MyRocks instance.
For my test I configured rocksdb_db_write_buffer_size to be 32 Mb
mysql> show global variables like 'Rocksdb_db_write_buffer_size';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| rocksdb_db_write_buffer_size | 33554432 |
+------------------------------+----------+
1 row in set (0.01 sec)
I also created two tables, each with their own column family. When I checked the information_schema.ROCKSDB_CF_OPTIONS table, they both showed as having a 64 Mb write buffer per column family.
mysql> create database localtest -> ; Query OK, 1 row affected (0.00 sec)mysql> use localtest;mysql> CREATE TABLE `t1` ( -> `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `c2` char(255) NOT NULL, -> `c3` char(255) NOT NULL, -> `c4` char(255) NOT NULL, -> `c5` char(255) NOT NULL, -> `c6` char(255) NOT NULL, -> `c7` char(255) NOT NULL, -> `c8` char(255) NOT NULL, -> `c9` char(255) NOT NULL, -> `c10` char(255) NOT NULL, -> `ci` int(11) NOT NULL, -> PRIMARY KEY (`c1`) COMMENT 'cf_t1' -> ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.11 sec)mysql> CREATE TABLE `t2` ( -> `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `c2` char(255) NOT NULL, -> `c3` char(255) NOT NULL, -> `c4` char(255) NOT NULL, -> `c5` char(255) NOT NULL, -> `c6` char(255) NOT NULL, -> `c7` char(255) NOT NULL, -> `c8` char(255) NOT NULL, -> `c9` char(255) NOT NULL, -> `c10` char(255) NOT NULL, -> `ci` int(11) NOT NULL, -> PRIMARY KEY (`c1`) COMMENT 'cf_t2' -> ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.32 sec)mysql> select * from information_schema.ROCKSDB_CF_OPTIONS where option_type = 'WRITE_BUFFER_SIZE'; +------------+-------------------+----------+ | CF_NAME | OPTION_TYPE | VALUE | +------------+-------------------+----------+ | __system__ | WRITE_BUFFER_SIZE | 67108864 | | cf_t1 | WRITE_BUFFER_SIZE | 67108864 | | cf_t2 | WRITE_BUFFER_SIZE | 67108864 | | default | WRITE_BUFFER_SIZE | 67108864 | +------------+-------------------+----------+ 4 rows in set (0.01 sec)
I then used a tool that I have to write data into the target schema at 1000 records per tables and repeated this process and checked the information_schema.ROCKSDB_CFSTATS page to watch the size of the memtables in memory to see at what point they would rotate to immutable and initiate a flush. It got to about 8 Mb total before flushing.
mysql> select sum(value) from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2'); +------------+ | sum(value) | +------------+ | 9353024 | +------------+ 1 row in set (0.00 sec)mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2'); +--------------+ | memtablemegs | +--------------+ | 8.91973877 | +--------------+ 1 row in set (0.00 sec)....mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2'); +--------------+ | memtablemegs | +--------------+ | 4.01597595 | +--------------+ 1 row in set (0.00 sec)mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2'); +--------------+ | memtablemegs | +--------------+ | 0.78307343 | +--------------+ 1 row in set (0.00 sec)
Needless to say, neither of the column family memtables got anywhere close to the 64 Mb limit.
I then tested again, changing only the rocksdb_db_write_buffer_size variable to 128 Mb. When I performed the same write test the combined memtable usage made it all the way to 94 Mb before starting a flush.
mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2'); +--------------+ | memtablemegs | +--------------+ | 0.00325012 | +--------------+ 1 row in set (0.00 sec)mysql> #started data load mysql> #started data load mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2'); +--------------+ | memtablemegs | +--------------+ | 3.46221161 | +--------------+ 1 row in set (0.00 sec)....mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2'); +--------------+ | memtablemegs | +--------------+ | 94.33660889 | +--------------+ 1 row in set (0.00 sec)
I then performed one additional test leaving the variable at its default setting of 0, which would imply that no limit should be placed on the size of the memtable. In this case it made it all the way to 118 Mb before the flush occured
mysql> show global variables like 'Rocksdb_db_write_buffer_size'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | rocksdb_db_write_buffer_size | 0 | +------------------------------+-------+ 1 row in set (0.00 sec)mysql> #data write starts mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE'; +--------------+ | memtablemegs | +--------------+ | 2.68215942 | +--------------+ 1 row in set (0.01 sec)......mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE'; +--------------+ | memtablemegs | +--------------+ | 118.19731903 | +--------------+ 1 row in set (0.00 sec)
In conclusion, I believe that the documentation for this variable is wrong and this does NOT set the write buffer size for each column family. In order to set the write buffer size for each column family I had to use the variable rocksdb_default_cf_options and configure the column family option write_buffer_size like so
rocksdb_default_cf_options = write_buffer_size={value}
I believe that the rocksdb_db_write_buffer_size sets a maximum cap for the amount of memory being used by active memtables across all column families. If the amount of memory being used across all column families approaches this value it will initiate a rotation to immutable and then flush.