Details
-
Improvement
-
Status: In Review
-
Medium
-
Resolution: Unresolved
-
8.0.13-4, 5.7.25-28, 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 ####################################################
-
Yes
Description
Per the documentation found here....
https://www.percona.com/doc/percona-server/5.7/myrocks/variables.html#rocksdb_wal_ttl_seconds
The rocksdb_wal_ttl_seconds variable is defined as....
Specifies the timeout in seconds before deleting archived WAL files
I checked my MyRocks data directory to check for archived WAL files to see when they would be purged, but I didn't find any as they weren't being created.
mysql> show global variables like 'rocksdb_wal_ttl_seconds';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| rocksdb_wal_ttl_seconds | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)mysql> exit
Bye
[root@centos7-1 .rocksdb]# ls -lh ./archive/
total 0
[root@centos7-1 .rocksdb]#
I tried various tests to see what would cause archiving, but it wasn't until I set rocksdb_wal_ttl_seconds to a non-zero value that WALs got archived.
mysql> show global variables like 'rocksdb_wal_ttl_seconds'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | rocksdb_wal_ttl_seconds | 300 | +-------------------------+-------+ 1 row in set (0.00 sec)mysql> show global variables like '%archive%'; Empty set (0.00 sec)mysql> exit Bye [root@centos7-1 .rocksdb]# ls -lh *.log; ls -lh ./archive/ -rw-r----- 1 mysql mysql 117 Jan 15 10:41 001911.log -rw-r----- 1 mysql mysql 74 Jan 15 10:41 001917.log total 0 [root@centos7-1 .rocksdb]# #started write [root@centos7-1 .rocksdb]# ls -lh *.log; ls -lh ./archive/ -rw-r----- 1 mysql mysql 117 Jan 15 10:41 001911.log -rw-r----- 1 mysql mysql 3.1M Jan 15 10:42 001917.log total 0 ... [root@centos7-1 .rocksdb]# ls -lh *.log; ls -lh ./archive/ -rw-r----- 1 mysql mysql 117 Jan 15 10:41 001911.log -rw-r----- 1 mysql mysql 3.4M Jan 15 10:43 001935.log total 33M -rw-r----- 1 mysql mysql 17M Jan 15 10:42 001917.log -rw-r----- 1 mysql mysql 17M Jan 15 10:43 001931.log
In conclusion, I don't think there is anything wrong with the code, and using a variable to enable archiving makes sense to me, but the documentation is ambiguous as to how archiving would be enabled and disabled. I believe that the documentation should be revised to not that archiving will not occur until rocksdb_wal_ttl_seconds is set to a non-zero value
