Mixing
Can't reduce mysql 5.7 VIRT Ram Use Too High Mysql
0
I am having trouble understanding how to reduce the max memory used by mysql. You will see here it is reserving approximately 9.8G even though it says 6.7 in the RES column. But the 9.8G seems to be really what is set aside:
I want the max VIRT to be around 8G max for the 16G ram server so that the server doesn't run out of ram. According to mysqltuner and percona tools the max used should be what I'm targeting, how do I get it to not use up more than it says? Here is the full output from mysql tuner
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.23-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 165 warning(s).
[!!] /var/log/mysqld.log contains 936 error(s).
[--] 47 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:32.547059Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-11-16T03:34:17.625819Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-11-14T10:22:40.815455Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-11-13T05:30:39.431447Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-11-13T02:24:39.126222Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-11-12T17:33:31.280532Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-11-12T16:26:08.192382Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-11-12T16:17:48.393508Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-11-12T14:59:38.463289Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-11-12T14:58:02.659350Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 46 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:30.131899Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-11-16T03:34:15.133264Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-11-14T10:22:38.285742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-11-13T05:30:35.410744Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-11-13T02:21:23.386572Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-11-12T17:33:27.568367Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-11-12T16:26:04.182657Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-11-12T16:17:37.240357Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-11-12T14:59:33.687687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-11-12T14:49:06.301982Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 555.6M (Tables: 2477)
[--] Data in InnoDB tables: 9.0G (Tables: 20153)
[--] Data in MEMORY tables: 7.4M (Tables: 816)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 2h 51m 1s (28M q [106.213 qps], 481K conn, TX: 56G, RX: 9G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 7.1G
[--] Other process memory: 4.1G
[--] Total buffers: 4.6G global + 16.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.9G (31.90% of installed RAM)
[OK] Maximum possible memory usage: 7.1G (45.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/28M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.01% (41/481831)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 80506
[!!] Temporary tables created on disk: 65% (2M on disk / 3M total)
[OK] Thread cache hit rate: 99% (26 created / 481K connections)
[!!] Table cache hit rate: 0% (6K open / 1M opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.5% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/55.9M
[OK] Read Key buffer hit rate: 99.9% (39M cached / 55K reads)
[!!] Write Key buffer hit rate: 41.9% (344K cached / 144K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.5G/9.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.4444444444444 %): 1.0G * 2/4.5G should be equal 25%
[!!] InnoDB buffer pool instances: 6
[--] Number of InnoDB Buffer Pool Chunk : 36 for 6 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (3703840401 hits/ 3703944355 total)
[!!] InnoDB Write Log efficiency: 70.96% (2254480 hits/ 3176899 total)
[OK] InnoDB log waits: 0.00% (0 waits / 922419 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (50000) variable
should be greater than table_open_cache (6000)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
join_buffer_size (> 16.0M, or always use indexes with JOINs)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 6000)
innodb_buffer_pool_size (>= 9.0G) if possible.
innodb_log_file_size should be (=576M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)and pt-mysql-summary
pt-mysql-summary [OPTIONS]
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-11-22 06:01:56 UTC (local TZ: UTC +0000)
# 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 | 2018-11-22 06:01:56 (UTC)
Version | 5.7.23-cll-lve MySQL Community Server - (GPL)
Built On | Linux x86_64
Started | 2018-11-19 03:15 (up 3+02:46:26)
Databases | 100
Datadir | /var/lib/mysql/
Processes | 2 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
Sleep 1 0 50 50
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 2 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 2 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
information_schema 1 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
1 0 0 0
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 100
Aborted_connects 15
Bytes_received 3500000000 40000 15000
Bytes_sent 20000000000 225000 50000
Com_admin_commands 1500
Com_alter_table 4000
Com_begin 150000 1
Com_change_db 12500
Com_check 40
Com_commit 150000 1
Com_create_table 3500
Com_delete 40000
Com_delete_multi 2500
Com_drop_table 4000
Com_enable_governor_lve 1500
Com_flush 2
Com_grant 3
Com_insert 125000 1
Com_insert_select 1500
Com_lock_tables 7
Com_rename_table 2
Com_replace 100
Com_rollback 20
Com_select 8000000 90 40
Com_set_option 300000 3 2
Com_show_create_db 6
Com_show_create_table 1750
Com_show_create_trigger 15
Com_show_databases 200
Com_show_fields 40000
Com_show_grants 150
Com_show_keys 70
Com_show_master_status 1
Com_show_processlist 20000
Com_show_slave_status 1
Com_show_status 1750
Com_show_table_status 40000
Com_show_tables 1500
Com_show_triggers 1750
Com_show_variables 1000
Com_show_create_user 150
Com_stmt_execute 60000
Com_stmt_close 60000
Com_stmt_prepare 60000
Com_truncate 4
Com_unlock_tables 7
Com_update 175000 1
Com_update_multi 20000
Connections 150000 1 3
Created_tmp_disk_tables 800000 8 3
Created_tmp_files 500
Created_tmp_tables 1250000 15 10
Handler_commit 7000000 90 40
Handler_delete 175000 2
Handler_external_lock 35000000 400 150
Handler_read_first 1500000 15 4
Handler_read_key 125000000 1500 400
Handler_read_last 900
Handler_read_next 225000000 2500 250
Handler_read_prev 300000 3
Handler_read_rnd 30000000 350 125
Handler_read_rnd_next 800000000 9000 5000
Handler_rollback 20
Handler_update 2250000 25
Handler_write 8000000 90 60
Innodb_buffer_pool_bytes_data 1250000000 15000
Innodb_buffer_pool_pages_flushed 600000 6 4
Innodb_buffer_pool_read_ahead 15000
Innodb_buffer_pool_read_requests 1250000000 15000 2250
Innodb_buffer_pool_reads 35000
Innodb_buffer_pool_write_requests 35000000 400 100
Innodb_data_fsyncs 600000 6 5
Innodb_data_read 800000000 9000
Innodb_data_reads 60000
Innodb_data_writes 1000000 10 7
Innodb_data_written 17500000000 200000 125000
Innodb_dblwr_pages_written 500000 5 3
Innodb_dblwr_writes 80000 1
Innodb_log_write_requests 1000000 10 4
Innodb_log_writes 300000 3 1
Innodb_os_log_fsyncs 300000 3 2
Innodb_os_log_written 800000000 9000 4000
Innodb_pages_created 25000
Innodb_pages_read 50000
Innodb_pages_written 600000 6 4
Innodb_row_lock_time 6000
Innodb_row_lock_waits 600
Innodb_rows_deleted 175000 1
Innodb_rows_inserted 30000000 350 125
Innodb_rows_read 1000000000 10000 5000
Innodb_rows_updated 225000 2
Innodb_num_open_files 2000
Innodb_available_undo_logs 40
Key_read_requests 12500000 150
Key_reads 17500
Key_write_requests 100000 1
Key_writes 45000
Open_table_definitions 700
Opened_files 300000 3 3
Opened_table_definitions 175000 2
Opened_tables 600000 6 9
Performance_schema_digest_lost 150000 1 3
Performance_schema_file_instances_lost 2500
Queries 9000000 100 50
Questions 9000000 100 50
Select_full_join 25000
Select_full_range_join 125000 1
Select_range 700000 8 3
Select_range_check 6
Select_scan 1250000 15 4
Sort_merge_passes 800
Sort_range 500000 6
Sort_rows 30000000 350 125
Sort_scan 1000000 15 3
Table_locks_immediate 1250000 15
Table_locks_waited 100
Table_open_cache_hits 17500000 200 60
Table_open_cache_misses 600000 6 9
Table_open_cache_overflows 600000 6 9
Threads_created 8
Uptime 90000 1 1
# Table cache ################################################
Size | 6000
Usage | 100%
# 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 | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.23
Buffer Pool Size | 4.5G
Buffer Pool Fill | 80%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
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 | 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 | 9
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
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 35%
Unflushed | 0%
# Security ###################################################
Users | 2936 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Encryption #################################################
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 | NO_ENGINE_SUBSTITUTION
join_buffer_size | 16M
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
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]
performance-schema = On
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_file_per_table = 1
sql_mode = NO_ENGINE_SUBSTITUTION
innodb_buffer_pool_size = 4G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
open_files_limit = 40000
table_open_cache = 6000
max_heap_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
tmp_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
query_cache_limit = 16M
join_buffer_size = 16M
innodb_buffer_pool_instances = 6
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 16
wait_timeout = 300
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 819535
# The End ####################################################mysql performance ram
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
add a comment |
0
I am having trouble understanding how to reduce the max memory used by mysql. You will see here it is reserving approximately 9.8G even though it says 6.7 in the RES column. But the 9.8G seems to be really what is set aside:
I want the max VIRT to be around 8G max for the 16G ram server so that the server doesn't run out of ram. According to mysqltuner and percona tools the max used should be what I'm targeting, how do I get it to not use up more than it says? Here is the full output from mysql tuner
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.23-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 165 warning(s).
[!!] /var/log/mysqld.log contains 936 error(s).
[--] 47 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:32.547059Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-11-16T03:34:17.625819Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-11-14T10:22:40.815455Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-11-13T05:30:39.431447Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-11-13T02:24:39.126222Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-11-12T17:33:31.280532Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-11-12T16:26:08.192382Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-11-12T16:17:48.393508Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-11-12T14:59:38.463289Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-11-12T14:58:02.659350Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 46 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:30.131899Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-11-16T03:34:15.133264Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-11-14T10:22:38.285742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-11-13T05:30:35.410744Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-11-13T02:21:23.386572Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-11-12T17:33:27.568367Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-11-12T16:26:04.182657Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-11-12T16:17:37.240357Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-11-12T14:59:33.687687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-11-12T14:49:06.301982Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 555.6M (Tables: 2477)
[--] Data in InnoDB tables: 9.0G (Tables: 20153)
[--] Data in MEMORY tables: 7.4M (Tables: 816)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 2h 51m 1s (28M q [106.213 qps], 481K conn, TX: 56G, RX: 9G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 7.1G
[--] Other process memory: 4.1G
[--] Total buffers: 4.6G global + 16.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.9G (31.90% of installed RAM)
[OK] Maximum possible memory usage: 7.1G (45.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/28M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.01% (41/481831)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 80506
[!!] Temporary tables created on disk: 65% (2M on disk / 3M total)
[OK] Thread cache hit rate: 99% (26 created / 481K connections)
[!!] Table cache hit rate: 0% (6K open / 1M opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.5% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/55.9M
[OK] Read Key buffer hit rate: 99.9% (39M cached / 55K reads)
[!!] Write Key buffer hit rate: 41.9% (344K cached / 144K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.5G/9.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.4444444444444 %): 1.0G * 2/4.5G should be equal 25%
[!!] InnoDB buffer pool instances: 6
[--] Number of InnoDB Buffer Pool Chunk : 36 for 6 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (3703840401 hits/ 3703944355 total)
[!!] InnoDB Write Log efficiency: 70.96% (2254480 hits/ 3176899 total)
[OK] InnoDB log waits: 0.00% (0 waits / 922419 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (50000) variable
should be greater than table_open_cache (6000)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
join_buffer_size (> 16.0M, or always use indexes with JOINs)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 6000)
innodb_buffer_pool_size (>= 9.0G) if possible.
innodb_log_file_size should be (=576M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)and pt-mysql-summary
pt-mysql-summary [OPTIONS]
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-11-22 06:01:56 UTC (local TZ: UTC +0000)
# 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 | 2018-11-22 06:01:56 (UTC)
Version | 5.7.23-cll-lve MySQL Community Server - (GPL)
Built On | Linux x86_64
Started | 2018-11-19 03:15 (up 3+02:46:26)
Databases | 100
Datadir | /var/lib/mysql/
Processes | 2 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
Sleep 1 0 50 50
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 2 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 2 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
information_schema 1 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
1 0 0 0
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 100
Aborted_connects 15
Bytes_received 3500000000 40000 15000
Bytes_sent 20000000000 225000 50000
Com_admin_commands 1500
Com_alter_table 4000
Com_begin 150000 1
Com_change_db 12500
Com_check 40
Com_commit 150000 1
Com_create_table 3500
Com_delete 40000
Com_delete_multi 2500
Com_drop_table 4000
Com_enable_governor_lve 1500
Com_flush 2
Com_grant 3
Com_insert 125000 1
Com_insert_select 1500
Com_lock_tables 7
Com_rename_table 2
Com_replace 100
Com_rollback 20
Com_select 8000000 90 40
Com_set_option 300000 3 2
Com_show_create_db 6
Com_show_create_table 1750
Com_show_create_trigger 15
Com_show_databases 200
Com_show_fields 40000
Com_show_grants 150
Com_show_keys 70
Com_show_master_status 1
Com_show_processlist 20000
Com_show_slave_status 1
Com_show_status 1750
Com_show_table_status 40000
Com_show_tables 1500
Com_show_triggers 1750
Com_show_variables 1000
Com_show_create_user 150
Com_stmt_execute 60000
Com_stmt_close 60000
Com_stmt_prepare 60000
Com_truncate 4
Com_unlock_tables 7
Com_update 175000 1
Com_update_multi 20000
Connections 150000 1 3
Created_tmp_disk_tables 800000 8 3
Created_tmp_files 500
Created_tmp_tables 1250000 15 10
Handler_commit 7000000 90 40
Handler_delete 175000 2
Handler_external_lock 35000000 400 150
Handler_read_first 1500000 15 4
Handler_read_key 125000000 1500 400
Handler_read_last 900
Handler_read_next 225000000 2500 250
Handler_read_prev 300000 3
Handler_read_rnd 30000000 350 125
Handler_read_rnd_next 800000000 9000 5000
Handler_rollback 20
Handler_update 2250000 25
Handler_write 8000000 90 60
Innodb_buffer_pool_bytes_data 1250000000 15000
Innodb_buffer_pool_pages_flushed 600000 6 4
Innodb_buffer_pool_read_ahead 15000
Innodb_buffer_pool_read_requests 1250000000 15000 2250
Innodb_buffer_pool_reads 35000
Innodb_buffer_pool_write_requests 35000000 400 100
Innodb_data_fsyncs 600000 6 5
Innodb_data_read 800000000 9000
Innodb_data_reads 60000
Innodb_data_writes 1000000 10 7
Innodb_data_written 17500000000 200000 125000
Innodb_dblwr_pages_written 500000 5 3
Innodb_dblwr_writes 80000 1
Innodb_log_write_requests 1000000 10 4
Innodb_log_writes 300000 3 1
Innodb_os_log_fsyncs 300000 3 2
Innodb_os_log_written 800000000 9000 4000
Innodb_pages_created 25000
Innodb_pages_read 50000
Innodb_pages_written 600000 6 4
Innodb_row_lock_time 6000
Innodb_row_lock_waits 600
Innodb_rows_deleted 175000 1
Innodb_rows_inserted 30000000 350 125
Innodb_rows_read 1000000000 10000 5000
Innodb_rows_updated 225000 2
Innodb_num_open_files 2000
Innodb_available_undo_logs 40
Key_read_requests 12500000 150
Key_reads 17500
Key_write_requests 100000 1
Key_writes 45000
Open_table_definitions 700
Opened_files 300000 3 3
Opened_table_definitions 175000 2
Opened_tables 600000 6 9
Performance_schema_digest_lost 150000 1 3
Performance_schema_file_instances_lost 2500
Queries 9000000 100 50
Questions 9000000 100 50
Select_full_join 25000
Select_full_range_join 125000 1
Select_range 700000 8 3
Select_range_check 6
Select_scan 1250000 15 4
Sort_merge_passes 800
Sort_range 500000 6
Sort_rows 30000000 350 125
Sort_scan 1000000 15 3
Table_locks_immediate 1250000 15
Table_locks_waited 100
Table_open_cache_hits 17500000 200 60
Table_open_cache_misses 600000 6 9
Table_open_cache_overflows 600000 6 9
Threads_created 8
Uptime 90000 1 1
# Table cache ################################################
Size | 6000
Usage | 100%
# 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 | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.23
Buffer Pool Size | 4.5G
Buffer Pool Fill | 80%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
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 | 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 | 9
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
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 35%
Unflushed | 0%
# Security ###################################################
Users | 2936 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Encryption #################################################
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 | NO_ENGINE_SUBSTITUTION
join_buffer_size | 16M
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
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]
performance-schema = On
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_file_per_table = 1
sql_mode = NO_ENGINE_SUBSTITUTION
innodb_buffer_pool_size = 4G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
open_files_limit = 40000
table_open_cache = 6000
max_heap_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
tmp_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
query_cache_limit = 16M
join_buffer_size = 16M
innodb_buffer_pool_instances = 6
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 16
wait_timeout = 300
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 819535
# The End ####################################################mysql performance ram
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, cat /proc/meminfo includes VMallocUused, for server tuning analysis.
– Wilson Hauck
Nov 22 '18 at 16:16
Hoping you have applied my suggestions and seeing positive results. Let us know of any success or additional troubles, please. A more in-depth analysis could be accomplished by posting the info requested above.
– Wilson Hauck
Dec 7 '18 at 19:47
Merry Christmas. Would still like to assist - but without information, can not proceed with analysis of your system.
– Wilson Hauck
Dec 18 '18 at 20:32
Have you implemented any of the suggestions from my Nov 23, 18 Answer? Were the results positive or negative? Please view my profile, Network profile for contact info and get in touch via Skype Invite, please.
– Wilson Hauck
Jan 22 at 15:46
add a comment |
0
0
0
I am having trouble understanding how to reduce the max memory used by mysql. You will see here it is reserving approximately 9.8G even though it says 6.7 in the RES column. But the 9.8G seems to be really what is set aside:
I want the max VIRT to be around 8G max for the 16G ram server so that the server doesn't run out of ram. According to mysqltuner and percona tools the max used should be what I'm targeting, how do I get it to not use up more than it says? Here is the full output from mysql tuner
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.23-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 165 warning(s).
[!!] /var/log/mysqld.log contains 936 error(s).
[--] 47 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:32.547059Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-11-16T03:34:17.625819Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-11-14T10:22:40.815455Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-11-13T05:30:39.431447Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-11-13T02:24:39.126222Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-11-12T17:33:31.280532Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-11-12T16:26:08.192382Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-11-12T16:17:48.393508Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-11-12T14:59:38.463289Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-11-12T14:58:02.659350Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 46 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:30.131899Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-11-16T03:34:15.133264Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-11-14T10:22:38.285742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-11-13T05:30:35.410744Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-11-13T02:21:23.386572Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-11-12T17:33:27.568367Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-11-12T16:26:04.182657Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-11-12T16:17:37.240357Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-11-12T14:59:33.687687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-11-12T14:49:06.301982Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 555.6M (Tables: 2477)
[--] Data in InnoDB tables: 9.0G (Tables: 20153)
[--] Data in MEMORY tables: 7.4M (Tables: 816)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 2h 51m 1s (28M q [106.213 qps], 481K conn, TX: 56G, RX: 9G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 7.1G
[--] Other process memory: 4.1G
[--] Total buffers: 4.6G global + 16.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.9G (31.90% of installed RAM)
[OK] Maximum possible memory usage: 7.1G (45.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/28M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.01% (41/481831)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 80506
[!!] Temporary tables created on disk: 65% (2M on disk / 3M total)
[OK] Thread cache hit rate: 99% (26 created / 481K connections)
[!!] Table cache hit rate: 0% (6K open / 1M opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.5% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/55.9M
[OK] Read Key buffer hit rate: 99.9% (39M cached / 55K reads)
[!!] Write Key buffer hit rate: 41.9% (344K cached / 144K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.5G/9.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.4444444444444 %): 1.0G * 2/4.5G should be equal 25%
[!!] InnoDB buffer pool instances: 6
[--] Number of InnoDB Buffer Pool Chunk : 36 for 6 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (3703840401 hits/ 3703944355 total)
[!!] InnoDB Write Log efficiency: 70.96% (2254480 hits/ 3176899 total)
[OK] InnoDB log waits: 0.00% (0 waits / 922419 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (50000) variable
should be greater than table_open_cache (6000)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
join_buffer_size (> 16.0M, or always use indexes with JOINs)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 6000)
innodb_buffer_pool_size (>= 9.0G) if possible.
innodb_log_file_size should be (=576M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)and pt-mysql-summary
pt-mysql-summary [OPTIONS]
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-11-22 06:01:56 UTC (local TZ: UTC +0000)
# 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 | 2018-11-22 06:01:56 (UTC)
Version | 5.7.23-cll-lve MySQL Community Server - (GPL)
Built On | Linux x86_64
Started | 2018-11-19 03:15 (up 3+02:46:26)
Databases | 100
Datadir | /var/lib/mysql/
Processes | 2 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
Sleep 1 0 50 50
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 2 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 2 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
information_schema 1 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
1 0 0 0
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 100
Aborted_connects 15
Bytes_received 3500000000 40000 15000
Bytes_sent 20000000000 225000 50000
Com_admin_commands 1500
Com_alter_table 4000
Com_begin 150000 1
Com_change_db 12500
Com_check 40
Com_commit 150000 1
Com_create_table 3500
Com_delete 40000
Com_delete_multi 2500
Com_drop_table 4000
Com_enable_governor_lve 1500
Com_flush 2
Com_grant 3
Com_insert 125000 1
Com_insert_select 1500
Com_lock_tables 7
Com_rename_table 2
Com_replace 100
Com_rollback 20
Com_select 8000000 90 40
Com_set_option 300000 3 2
Com_show_create_db 6
Com_show_create_table 1750
Com_show_create_trigger 15
Com_show_databases 200
Com_show_fields 40000
Com_show_grants 150
Com_show_keys 70
Com_show_master_status 1
Com_show_processlist 20000
Com_show_slave_status 1
Com_show_status 1750
Com_show_table_status 40000
Com_show_tables 1500
Com_show_triggers 1750
Com_show_variables 1000
Com_show_create_user 150
Com_stmt_execute 60000
Com_stmt_close 60000
Com_stmt_prepare 60000
Com_truncate 4
Com_unlock_tables 7
Com_update 175000 1
Com_update_multi 20000
Connections 150000 1 3
Created_tmp_disk_tables 800000 8 3
Created_tmp_files 500
Created_tmp_tables 1250000 15 10
Handler_commit 7000000 90 40
Handler_delete 175000 2
Handler_external_lock 35000000 400 150
Handler_read_first 1500000 15 4
Handler_read_key 125000000 1500 400
Handler_read_last 900
Handler_read_next 225000000 2500 250
Handler_read_prev 300000 3
Handler_read_rnd 30000000 350 125
Handler_read_rnd_next 800000000 9000 5000
Handler_rollback 20
Handler_update 2250000 25
Handler_write 8000000 90 60
Innodb_buffer_pool_bytes_data 1250000000 15000
Innodb_buffer_pool_pages_flushed 600000 6 4
Innodb_buffer_pool_read_ahead 15000
Innodb_buffer_pool_read_requests 1250000000 15000 2250
Innodb_buffer_pool_reads 35000
Innodb_buffer_pool_write_requests 35000000 400 100
Innodb_data_fsyncs 600000 6 5
Innodb_data_read 800000000 9000
Innodb_data_reads 60000
Innodb_data_writes 1000000 10 7
Innodb_data_written 17500000000 200000 125000
Innodb_dblwr_pages_written 500000 5 3
Innodb_dblwr_writes 80000 1
Innodb_log_write_requests 1000000 10 4
Innodb_log_writes 300000 3 1
Innodb_os_log_fsyncs 300000 3 2
Innodb_os_log_written 800000000 9000 4000
Innodb_pages_created 25000
Innodb_pages_read 50000
Innodb_pages_written 600000 6 4
Innodb_row_lock_time 6000
Innodb_row_lock_waits 600
Innodb_rows_deleted 175000 1
Innodb_rows_inserted 30000000 350 125
Innodb_rows_read 1000000000 10000 5000
Innodb_rows_updated 225000 2
Innodb_num_open_files 2000
Innodb_available_undo_logs 40
Key_read_requests 12500000 150
Key_reads 17500
Key_write_requests 100000 1
Key_writes 45000
Open_table_definitions 700
Opened_files 300000 3 3
Opened_table_definitions 175000 2
Opened_tables 600000 6 9
Performance_schema_digest_lost 150000 1 3
Performance_schema_file_instances_lost 2500
Queries 9000000 100 50
Questions 9000000 100 50
Select_full_join 25000
Select_full_range_join 125000 1
Select_range 700000 8 3
Select_range_check 6
Select_scan 1250000 15 4
Sort_merge_passes 800
Sort_range 500000 6
Sort_rows 30000000 350 125
Sort_scan 1000000 15 3
Table_locks_immediate 1250000 15
Table_locks_waited 100
Table_open_cache_hits 17500000 200 60
Table_open_cache_misses 600000 6 9
Table_open_cache_overflows 600000 6 9
Threads_created 8
Uptime 90000 1 1
# Table cache ################################################
Size | 6000
Usage | 100%
# 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 | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.23
Buffer Pool Size | 4.5G
Buffer Pool Fill | 80%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
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 | 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 | 9
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
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 35%
Unflushed | 0%
# Security ###################################################
Users | 2936 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Encryption #################################################
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 | NO_ENGINE_SUBSTITUTION
join_buffer_size | 16M
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
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]
performance-schema = On
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_file_per_table = 1
sql_mode = NO_ENGINE_SUBSTITUTION
innodb_buffer_pool_size = 4G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
open_files_limit = 40000
table_open_cache = 6000
max_heap_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
tmp_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
query_cache_limit = 16M
join_buffer_size = 16M
innodb_buffer_pool_instances = 6
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 16
wait_timeout = 300
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 819535
# The End ####################################################mysql performance ram
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
I am having trouble understanding how to reduce the max memory used by mysql. You will see here it is reserving approximately 9.8G even though it says 6.7 in the RES column. But the 9.8G seems to be really what is set aside:
I want the max VIRT to be around 8G max for the 16G ram server so that the server doesn't run out of ram. According to mysqltuner and percona tools the max used should be what I'm targeting, how do I get it to not use up more than it says? Here is the full output from mysql tuner
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.23-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 165 warning(s).
[!!] /var/log/mysqld.log contains 936 error(s).
[--] 47 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:32.547059Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-11-16T03:34:17.625819Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-11-14T10:22:40.815455Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-11-13T05:30:39.431447Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-11-13T02:24:39.126222Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-11-12T17:33:31.280532Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-11-12T16:26:08.192382Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-11-12T16:17:48.393508Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-11-12T14:59:38.463289Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-11-12T14:58:02.659350Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 46 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:30.131899Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-11-16T03:34:15.133264Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-11-14T10:22:38.285742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-11-13T05:30:35.410744Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-11-13T02:21:23.386572Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-11-12T17:33:27.568367Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-11-12T16:26:04.182657Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-11-12T16:17:37.240357Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-11-12T14:59:33.687687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-11-12T14:49:06.301982Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 555.6M (Tables: 2477)
[--] Data in InnoDB tables: 9.0G (Tables: 20153)
[--] Data in MEMORY tables: 7.4M (Tables: 816)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 2h 51m 1s (28M q [106.213 qps], 481K conn, TX: 56G, RX: 9G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 7.1G
[--] Other process memory: 4.1G
[--] Total buffers: 4.6G global + 16.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.9G (31.90% of installed RAM)
[OK] Maximum possible memory usage: 7.1G (45.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/28M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.01% (41/481831)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 80506
[!!] Temporary tables created on disk: 65% (2M on disk / 3M total)
[OK] Thread cache hit rate: 99% (26 created / 481K connections)
[!!] Table cache hit rate: 0% (6K open / 1M opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.5% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/55.9M
[OK] Read Key buffer hit rate: 99.9% (39M cached / 55K reads)
[!!] Write Key buffer hit rate: 41.9% (344K cached / 144K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.5G/9.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.4444444444444 %): 1.0G * 2/4.5G should be equal 25%
[!!] InnoDB buffer pool instances: 6
[--] Number of InnoDB Buffer Pool Chunk : 36 for 6 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (3703840401 hits/ 3703944355 total)
[!!] InnoDB Write Log efficiency: 70.96% (2254480 hits/ 3176899 total)
[OK] InnoDB log waits: 0.00% (0 waits / 922419 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (50000) variable
should be greater than table_open_cache (6000)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
join_buffer_size (> 16.0M, or always use indexes with JOINs)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 6000)
innodb_buffer_pool_size (>= 9.0G) if possible.
innodb_log_file_size should be (=576M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)and pt-mysql-summary
pt-mysql-summary [OPTIONS]
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-11-22 06:01:56 UTC (local TZ: UTC +0000)
# 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 | 2018-11-22 06:01:56 (UTC)
Version | 5.7.23-cll-lve MySQL Community Server - (GPL)
Built On | Linux x86_64
Started | 2018-11-19 03:15 (up 3+02:46:26)
Databases | 100
Datadir | /var/lib/mysql/
Processes | 2 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
Sleep 1 0 50 50
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 2 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 2 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
information_schema 1 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
1 0 0 0
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 100
Aborted_connects 15
Bytes_received 3500000000 40000 15000
Bytes_sent 20000000000 225000 50000
Com_admin_commands 1500
Com_alter_table 4000
Com_begin 150000 1
Com_change_db 12500
Com_check 40
Com_commit 150000 1
Com_create_table 3500
Com_delete 40000
Com_delete_multi 2500
Com_drop_table 4000
Com_enable_governor_lve 1500
Com_flush 2
Com_grant 3
Com_insert 125000 1
Com_insert_select 1500
Com_lock_tables 7
Com_rename_table 2
Com_replace 100
Com_rollback 20
Com_select 8000000 90 40
Com_set_option 300000 3 2
Com_show_create_db 6
Com_show_create_table 1750
Com_show_create_trigger 15
Com_show_databases 200
Com_show_fields 40000
Com_show_grants 150
Com_show_keys 70
Com_show_master_status 1
Com_show_processlist 20000
Com_show_slave_status 1
Com_show_status 1750
Com_show_table_status 40000
Com_show_tables 1500
Com_show_triggers 1750
Com_show_variables 1000
Com_show_create_user 150
Com_stmt_execute 60000
Com_stmt_close 60000
Com_stmt_prepare 60000
Com_truncate 4
Com_unlock_tables 7
Com_update 175000 1
Com_update_multi 20000
Connections 150000 1 3
Created_tmp_disk_tables 800000 8 3
Created_tmp_files 500
Created_tmp_tables 1250000 15 10
Handler_commit 7000000 90 40
Handler_delete 175000 2
Handler_external_lock 35000000 400 150
Handler_read_first 1500000 15 4
Handler_read_key 125000000 1500 400
Handler_read_last 900
Handler_read_next 225000000 2500 250
Handler_read_prev 300000 3
Handler_read_rnd 30000000 350 125
Handler_read_rnd_next 800000000 9000 5000
Handler_rollback 20
Handler_update 2250000 25
Handler_write 8000000 90 60
Innodb_buffer_pool_bytes_data 1250000000 15000
Innodb_buffer_pool_pages_flushed 600000 6 4
Innodb_buffer_pool_read_ahead 15000
Innodb_buffer_pool_read_requests 1250000000 15000 2250
Innodb_buffer_pool_reads 35000
Innodb_buffer_pool_write_requests 35000000 400 100
Innodb_data_fsyncs 600000 6 5
Innodb_data_read 800000000 9000
Innodb_data_reads 60000
Innodb_data_writes 1000000 10 7
Innodb_data_written 17500000000 200000 125000
Innodb_dblwr_pages_written 500000 5 3
Innodb_dblwr_writes 80000 1
Innodb_log_write_requests 1000000 10 4
Innodb_log_writes 300000 3 1
Innodb_os_log_fsyncs 300000 3 2
Innodb_os_log_written 800000000 9000 4000
Innodb_pages_created 25000
Innodb_pages_read 50000
Innodb_pages_written 600000 6 4
Innodb_row_lock_time 6000
Innodb_row_lock_waits 600
Innodb_rows_deleted 175000 1
Innodb_rows_inserted 30000000 350 125
Innodb_rows_read 1000000000 10000 5000
Innodb_rows_updated 225000 2
Innodb_num_open_files 2000
Innodb_available_undo_logs 40
Key_read_requests 12500000 150
Key_reads 17500
Key_write_requests 100000 1
Key_writes 45000
Open_table_definitions 700
Opened_files 300000 3 3
Opened_table_definitions 175000 2
Opened_tables 600000 6 9
Performance_schema_digest_lost 150000 1 3
Performance_schema_file_instances_lost 2500
Queries 9000000 100 50
Questions 9000000 100 50
Select_full_join 25000
Select_full_range_join 125000 1
Select_range 700000 8 3
Select_range_check 6
Select_scan 1250000 15 4
Sort_merge_passes 800
Sort_range 500000 6
Sort_rows 30000000 350 125
Sort_scan 1000000 15 3
Table_locks_immediate 1250000 15
Table_locks_waited 100
Table_open_cache_hits 17500000 200 60
Table_open_cache_misses 600000 6 9
Table_open_cache_overflows 600000 6 9
Threads_created 8
Uptime 90000 1 1
# Table cache ################################################
Size | 6000
Usage | 100%
# 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 | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.23
Buffer Pool Size | 4.5G
Buffer Pool Fill | 80%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
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 | 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 | 9
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
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 35%
Unflushed | 0%
# Security ###################################################
Users | 2936 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Encryption #################################################
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 | NO_ENGINE_SUBSTITUTION
join_buffer_size | 16M
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
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]
performance-schema = On
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_file_per_table = 1
sql_mode = NO_ENGINE_SUBSTITUTION
innodb_buffer_pool_size = 4G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
open_files_limit = 40000
table_open_cache = 6000
max_heap_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
tmp_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
query_cache_limit = 16M
join_buffer_size = 16M
innodb_buffer_pool_instances = 6
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 16
wait_timeout = 300
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 819535
# The End ####################################################[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.23-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 165 warning(s).
[!!] /var/log/mysqld.log contains 936 error(s).
[--] 47 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:32.547059Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-11-16T03:34:17.625819Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-11-14T10:22:40.815455Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-11-13T05:30:39.431447Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-11-13T02:24:39.126222Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-11-12T17:33:31.280532Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-11-12T16:26:08.192382Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-11-12T16:17:48.393508Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-11-12T14:59:38.463289Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-11-12T14:58:02.659350Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 46 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:30.131899Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-11-16T03:34:15.133264Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-11-14T10:22:38.285742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-11-13T05:30:35.410744Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-11-13T02:21:23.386572Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-11-12T17:33:27.568367Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-11-12T16:26:04.182657Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-11-12T16:17:37.240357Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-11-12T14:59:33.687687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-11-12T14:49:06.301982Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 555.6M (Tables: 2477)
[--] Data in InnoDB tables: 9.0G (Tables: 20153)
[--] Data in MEMORY tables: 7.4M (Tables: 816)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 2h 51m 1s (28M q [106.213 qps], 481K conn, TX: 56G, RX: 9G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 7.1G
[--] Other process memory: 4.1G
[--] Total buffers: 4.6G global + 16.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.9G (31.90% of installed RAM)
[OK] Maximum possible memory usage: 7.1G (45.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/28M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.01% (41/481831)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 80506
[!!] Temporary tables created on disk: 65% (2M on disk / 3M total)
[OK] Thread cache hit rate: 99% (26 created / 481K connections)
[!!] Table cache hit rate: 0% (6K open / 1M opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.5% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/55.9M
[OK] Read Key buffer hit rate: 99.9% (39M cached / 55K reads)
[!!] Write Key buffer hit rate: 41.9% (344K cached / 144K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.5G/9.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.4444444444444 %): 1.0G * 2/4.5G should be equal 25%
[!!] InnoDB buffer pool instances: 6
[--] Number of InnoDB Buffer Pool Chunk : 36 for 6 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (3703840401 hits/ 3703944355 total)
[!!] InnoDB Write Log efficiency: 70.96% (2254480 hits/ 3176899 total)
[OK] InnoDB log waits: 0.00% (0 waits / 922419 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (50000) variable
should be greater than table_open_cache (6000)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
join_buffer_size (> 16.0M, or always use indexes with JOINs)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 6000)
innodb_buffer_pool_size (>= 9.0G) if possible.
innodb_log_file_size should be (=576M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.23-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 165 warning(s).
[!!] /var/log/mysqld.log contains 936 error(s).
[--] 47 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:32.547059Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-11-16T03:34:17.625819Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-11-14T10:22:40.815455Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-11-13T05:30:39.431447Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-11-13T02:24:39.126222Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-11-12T17:33:31.280532Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-11-12T16:26:08.192382Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-11-12T16:17:48.393508Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-11-12T14:59:38.463289Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-11-12T14:58:02.659350Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 46 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-11-19T03:15:30.131899Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-11-16T03:34:15.133264Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-11-14T10:22:38.285742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-11-13T05:30:35.410744Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-11-13T02:21:23.386572Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-11-12T17:33:27.568367Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-11-12T16:26:04.182657Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-11-12T16:17:37.240357Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-11-12T14:59:33.687687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-11-12T14:49:06.301982Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 555.6M (Tables: 2477)
[--] Data in InnoDB tables: 9.0G (Tables: 20153)
[--] Data in MEMORY tables: 7.4M (Tables: 816)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 2h 51m 1s (28M q [106.213 qps], 481K conn, TX: 56G, RX: 9G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 7.1G
[--] Other process memory: 4.1G
[--] Total buffers: 4.6G global + 16.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.9G (31.90% of installed RAM)
[OK] Maximum possible memory usage: 7.1G (45.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/28M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.01% (41/481831)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 80506
[!!] Temporary tables created on disk: 65% (2M on disk / 3M total)
[OK] Thread cache hit rate: 99% (26 created / 481K connections)
[!!] Table cache hit rate: 0% (6K open / 1M opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.5% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/55.9M
[OK] Read Key buffer hit rate: 99.9% (39M cached / 55K reads)
[!!] Write Key buffer hit rate: 41.9% (344K cached / 144K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.5G/9.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.4444444444444 %): 1.0G * 2/4.5G should be equal 25%
[!!] InnoDB buffer pool instances: 6
[--] Number of InnoDB Buffer Pool Chunk : 36 for 6 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (3703840401 hits/ 3703944355 total)
[!!] InnoDB Write Log efficiency: 70.96% (2254480 hits/ 3176899 total)
[OK] InnoDB log waits: 0.00% (0 waits / 922419 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (50000) variable
should be greater than table_open_cache (6000)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
join_buffer_size (> 16.0M, or always use indexes with JOINs)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 6000)
innodb_buffer_pool_size (>= 9.0G) if possible.
innodb_log_file_size should be (=576M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)pt-mysql-summary [OPTIONS]
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-11-22 06:01:56 UTC (local TZ: UTC +0000)
# 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 | 2018-11-22 06:01:56 (UTC)
Version | 5.7.23-cll-lve MySQL Community Server - (GPL)
Built On | Linux x86_64
Started | 2018-11-19 03:15 (up 3+02:46:26)
Databases | 100
Datadir | /var/lib/mysql/
Processes | 2 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
Sleep 1 0 50 50
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 2 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 2 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
information_schema 1 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
1 0 0 0
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 100
Aborted_connects 15
Bytes_received 3500000000 40000 15000
Bytes_sent 20000000000 225000 50000
Com_admin_commands 1500
Com_alter_table 4000
Com_begin 150000 1
Com_change_db 12500
Com_check 40
Com_commit 150000 1
Com_create_table 3500
Com_delete 40000
Com_delete_multi 2500
Com_drop_table 4000
Com_enable_governor_lve 1500
Com_flush 2
Com_grant 3
Com_insert 125000 1
Com_insert_select 1500
Com_lock_tables 7
Com_rename_table 2
Com_replace 100
Com_rollback 20
Com_select 8000000 90 40
Com_set_option 300000 3 2
Com_show_create_db 6
Com_show_create_table 1750
Com_show_create_trigger 15
Com_show_databases 200
Com_show_fields 40000
Com_show_grants 150
Com_show_keys 70
Com_show_master_status 1
Com_show_processlist 20000
Com_show_slave_status 1
Com_show_status 1750
Com_show_table_status 40000
Com_show_tables 1500
Com_show_triggers 1750
Com_show_variables 1000
Com_show_create_user 150
Com_stmt_execute 60000
Com_stmt_close 60000
Com_stmt_prepare 60000
Com_truncate 4
Com_unlock_tables 7
Com_update 175000 1
Com_update_multi 20000
Connections 150000 1 3
Created_tmp_disk_tables 800000 8 3
Created_tmp_files 500
Created_tmp_tables 1250000 15 10
Handler_commit 7000000 90 40
Handler_delete 175000 2
Handler_external_lock 35000000 400 150
Handler_read_first 1500000 15 4
Handler_read_key 125000000 1500 400
Handler_read_last 900
Handler_read_next 225000000 2500 250
Handler_read_prev 300000 3
Handler_read_rnd 30000000 350 125
Handler_read_rnd_next 800000000 9000 5000
Handler_rollback 20
Handler_update 2250000 25
Handler_write 8000000 90 60
Innodb_buffer_pool_bytes_data 1250000000 15000
Innodb_buffer_pool_pages_flushed 600000 6 4
Innodb_buffer_pool_read_ahead 15000
Innodb_buffer_pool_read_requests 1250000000 15000 2250
Innodb_buffer_pool_reads 35000
Innodb_buffer_pool_write_requests 35000000 400 100
Innodb_data_fsyncs 600000 6 5
Innodb_data_read 800000000 9000
Innodb_data_reads 60000
Innodb_data_writes 1000000 10 7
Innodb_data_written 17500000000 200000 125000
Innodb_dblwr_pages_written 500000 5 3
Innodb_dblwr_writes 80000 1
Innodb_log_write_requests 1000000 10 4
Innodb_log_writes 300000 3 1
Innodb_os_log_fsyncs 300000 3 2
Innodb_os_log_written 800000000 9000 4000
Innodb_pages_created 25000
Innodb_pages_read 50000
Innodb_pages_written 600000 6 4
Innodb_row_lock_time 6000
Innodb_row_lock_waits 600
Innodb_rows_deleted 175000 1
Innodb_rows_inserted 30000000 350 125
Innodb_rows_read 1000000000 10000 5000
Innodb_rows_updated 225000 2
Innodb_num_open_files 2000
Innodb_available_undo_logs 40
Key_read_requests 12500000 150
Key_reads 17500
Key_write_requests 100000 1
Key_writes 45000
Open_table_definitions 700
Opened_files 300000 3 3
Opened_table_definitions 175000 2
Opened_tables 600000 6 9
Performance_schema_digest_lost 150000 1 3
Performance_schema_file_instances_lost 2500
Queries 9000000 100 50
Questions 9000000 100 50
Select_full_join 25000
Select_full_range_join 125000 1
Select_range 700000 8 3
Select_range_check 6
Select_scan 1250000 15 4
Sort_merge_passes 800
Sort_range 500000 6
Sort_rows 30000000 350 125
Sort_scan 1000000 15 3
Table_locks_immediate 1250000 15
Table_locks_waited 100
Table_open_cache_hits 17500000 200 60
Table_open_cache_misses 600000 6 9
Table_open_cache_overflows 600000 6 9
Threads_created 8
Uptime 90000 1 1
# Table cache ################################################
Size | 6000
Usage | 100%
# 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 | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.23
Buffer Pool Size | 4.5G
Buffer Pool Fill | 80%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
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 | 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 | 9
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
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 35%
Unflushed | 0%
# Security ###################################################
Users | 2936 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Encryption #################################################
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 | NO_ENGINE_SUBSTITUTION
join_buffer_size | 16M
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
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]
performance-schema = On
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_file_per_table = 1
sql_mode = NO_ENGINE_SUBSTITUTION
innodb_buffer_pool_size = 4G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
open_files_limit = 40000
table_open_cache = 6000
max_heap_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
tmp_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
query_cache_limit = 16M
join_buffer_size = 16M
innodb_buffer_pool_instances = 6
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 16
wait_timeout = 300
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 819535
# The End ####################################################pt-mysql-summary [OPTIONS]
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-11-22 06:01:56 UTC (local TZ: UTC +0000)
# 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 | 2018-11-22 06:01:56 (UTC)
Version | 5.7.23-cll-lve MySQL Community Server - (GPL)
Built On | Linux x86_64
Started | 2018-11-19 03:15 (up 3+02:46:26)
Databases | 100
Datadir | /var/lib/mysql/
Processes | 2 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
Sleep 1 0 50 50
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 2 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 2 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
information_schema 1 0 0 0
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
1 0 0 0
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 100
Aborted_connects 15
Bytes_received 3500000000 40000 15000
Bytes_sent 20000000000 225000 50000
Com_admin_commands 1500
Com_alter_table 4000
Com_begin 150000 1
Com_change_db 12500
Com_check 40
Com_commit 150000 1
Com_create_table 3500
Com_delete 40000
Com_delete_multi 2500
Com_drop_table 4000
Com_enable_governor_lve 1500
Com_flush 2
Com_grant 3
Com_insert 125000 1
Com_insert_select 1500
Com_lock_tables 7
Com_rename_table 2
Com_replace 100
Com_rollback 20
Com_select 8000000 90 40
Com_set_option 300000 3 2
Com_show_create_db 6
Com_show_create_table 1750
Com_show_create_trigger 15
Com_show_databases 200
Com_show_fields 40000
Com_show_grants 150
Com_show_keys 70
Com_show_master_status 1
Com_show_processlist 20000
Com_show_slave_status 1
Com_show_status 1750
Com_show_table_status 40000
Com_show_tables 1500
Com_show_triggers 1750
Com_show_variables 1000
Com_show_create_user 150
Com_stmt_execute 60000
Com_stmt_close 60000
Com_stmt_prepare 60000
Com_truncate 4
Com_unlock_tables 7
Com_update 175000 1
Com_update_multi 20000
Connections 150000 1 3
Created_tmp_disk_tables 800000 8 3
Created_tmp_files 500
Created_tmp_tables 1250000 15 10
Handler_commit 7000000 90 40
Handler_delete 175000 2
Handler_external_lock 35000000 400 150
Handler_read_first 1500000 15 4
Handler_read_key 125000000 1500 400
Handler_read_last 900
Handler_read_next 225000000 2500 250
Handler_read_prev 300000 3
Handler_read_rnd 30000000 350 125
Handler_read_rnd_next 800000000 9000 5000
Handler_rollback 20
Handler_update 2250000 25
Handler_write 8000000 90 60
Innodb_buffer_pool_bytes_data 1250000000 15000
Innodb_buffer_pool_pages_flushed 600000 6 4
Innodb_buffer_pool_read_ahead 15000
Innodb_buffer_pool_read_requests 1250000000 15000 2250
Innodb_buffer_pool_reads 35000
Innodb_buffer_pool_write_requests 35000000 400 100
Innodb_data_fsyncs 600000 6 5
Innodb_data_read 800000000 9000
Innodb_data_reads 60000
Innodb_data_writes 1000000 10 7
Innodb_data_written 17500000000 200000 125000
Innodb_dblwr_pages_written 500000 5 3
Innodb_dblwr_writes 80000 1
Innodb_log_write_requests 1000000 10 4
Innodb_log_writes 300000 3 1
Innodb_os_log_fsyncs 300000 3 2
Innodb_os_log_written 800000000 9000 4000
Innodb_pages_created 25000
Innodb_pages_read 50000
Innodb_pages_written 600000 6 4
Innodb_row_lock_time 6000
Innodb_row_lock_waits 600
Innodb_rows_deleted 175000 1
Innodb_rows_inserted 30000000 350 125
Innodb_rows_read 1000000000 10000 5000
Innodb_rows_updated 225000 2
Innodb_num_open_files 2000
Innodb_available_undo_logs 40
Key_read_requests 12500000 150
Key_reads 17500
Key_write_requests 100000 1
Key_writes 45000
Open_table_definitions 700
Opened_files 300000 3 3
Opened_table_definitions 175000 2
Opened_tables 600000 6 9
Performance_schema_digest_lost 150000 1 3
Performance_schema_file_instances_lost 2500
Queries 9000000 100 50
Questions 9000000 100 50
Select_full_join 25000
Select_full_range_join 125000 1
Select_range 700000 8 3
Select_range_check 6
Select_scan 1250000 15 4
Sort_merge_passes 800
Sort_range 500000 6
Sort_rows 30000000 350 125
Sort_scan 1000000 15 3
Table_locks_immediate 1250000 15
Table_locks_waited 100
Table_open_cache_hits 17500000 200 60
Table_open_cache_misses 600000 6 9
Table_open_cache_overflows 600000 6 9
Threads_created 8
Uptime 90000 1 1
# Table cache ################################################
Size | 6000
Usage | 100%
# 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 | Yes
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
# InnoDB #####################################################
Version | 5.7.23
Buffer Pool Size | 4.5G
Buffer Pool Fill | 80%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
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 | 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 | 9
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
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 35%
Unflushed | 0%
# Security ###################################################
Users | 2936 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
# Encryption #################################################
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 | NO_ENGINE_SUBSTITUTION
join_buffer_size | 16M
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
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]
performance-schema = On
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_file_per_table = 1
sql_mode = NO_ENGINE_SUBSTITUTION
innodb_buffer_pool_size = 4G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
open_files_limit = 40000
table_open_cache = 6000
max_heap_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
tmp_table_size = 64M #https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/3/
query_cache_limit = 16M
join_buffer_size = 16M
innodb_buffer_pool_instances = 6
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 16
wait_timeout = 300
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 819535
# The End ####################################################mysql performance ram
mysql performance ram
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
asked Nov 22 '18 at 6:34
Kevin ChavezKevin Chavez
2271214
2271214
Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, cat /proc/meminfo includes VMallocUused, for server tuning analysis.
– Wilson Hauck
Nov 22 '18 at 16:16
Hoping you have applied my suggestions and seeing positive results. Let us know of any success or additional troubles, please. A more in-depth analysis could be accomplished by posting the info requested above.
– Wilson Hauck
Dec 7 '18 at 19:47
Merry Christmas. Would still like to assist - but without information, can not proceed with analysis of your system.
– Wilson Hauck
Dec 18 '18 at 20:32
Have you implemented any of the suggestions from my Nov 23, 18 Answer? Were the results positive or negative? Please view my profile, Network profile for contact info and get in touch via Skype Invite, please.
– Wilson Hauck
Jan 22 at 15:46
add a comment |
Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, cat /proc/meminfo includes VMallocUused, for server tuning analysis.
– Wilson Hauck
Nov 22 '18 at 16:16
Hoping you have applied my suggestions and seeing positive results. Let us know of any success or additional troubles, please. A more in-depth analysis could be accomplished by posting the info requested above.
– Wilson Hauck
Dec 7 '18 at 19:47
Merry Christmas. Would still like to assist - but without information, can not proceed with analysis of your system.
– Wilson Hauck
Dec 18 '18 at 20:32
Have you implemented any of the suggestions from my Nov 23, 18 Answer? Were the results positive or negative? Please view my profile, Network profile for contact info and get in touch via Skype Invite, please.
– Wilson Hauck
Jan 22 at 15:46
Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, cat /proc/meminfo includes VMallocUused, for server tuning analysis.
– Wilson Hauck
Nov 22 '18 at 16:16
Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, cat /proc/meminfo includes VMallocUused, for server tuning analysis.
– Wilson Hauck
Nov 22 '18 at 16:16
Hoping you have applied my suggestions and seeing positive results. Let us know of any success or additional troubles, please. A more in-depth analysis could be accomplished by posting the info requested above.
– Wilson Hauck
Dec 7 '18 at 19:47
Hoping you have applied my suggestions and seeing positive results. Let us know of any success or additional troubles, please. A more in-depth analysis could be accomplished by posting the info requested above.
– Wilson Hauck
Dec 7 '18 at 19:47
Merry Christmas. Would still like to assist - but without information, can not proceed with analysis of your system.
– Wilson Hauck
Dec 18 '18 at 20:32
Merry Christmas. Would still like to assist - but without information, can not proceed with analysis of your system.
– Wilson Hauck
Dec 18 '18 at 20:32
Have you implemented any of the suggestions from my Nov 23, 18 Answer? Were the results positive or negative? Please view my profile, Network profile for contact info and get in touch via Skype Invite, please.
– Wilson Hauck
Jan 22 at 15:46
Have you implemented any of the suggestions from my Nov 23, 18 Answer? Were the results positive or negative? Please view my profile, Network profile for contact info and get in touch via Skype Invite, please.
– Wilson Hauck
Jan 22 at 15:46
add a comment |
3 Answers
3
active
oldest
votes
0
The quick answer is to lower innodb_buffer_pool_size by enough to get VIRT down to the desired 8G. However,...
- Don't set it below 1G without further discussion;
- If lowering it does not change VIRT; let's discuss further;
- You have 0.5G "free" -- is there a real problem?
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
Ok, so innodb_buffer_pool_size will reserve sometimes more than double VIRT? It seems strange. Are there other settings that you saw in my config that add much to ram use? I do think .5G free of ram can be an issue as it will stop php-fpm and even though it should auto-start it doesn't always seem to.
– Kevin Chavez
Nov 29 '18 at 4:01
add a comment |
0
Suggestions to consider for your my.cnf [mysqld] section -
(based on information available as of 11/22/2018 in your question) - Rate Per Second=RPS
From Linux command prompt,
ulimit -n 65536 to support more Open Files
For Linux change to persist over shutdown/restart review this URL
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
Your details may be slightly different due to version.
For your MySQL instance my.cnf [mysqld]
thread_cache_size=64 # from 16 to reduce threads_created (expensive operation)
query_cache_limit=0 # from 16M to conserve RAM when QC not used
query_cache_min_res_unit=512 # from 4096 to conserve QC space if ever used
innodb_lru_scan_depth=100 # from 1024 to conserve CPU cycles every SECOND
table_open_cache=10000 # from 6000 and you may need more
key_cache_age_threshold=7200 # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50 # from 100 for Hot/Warm cache
key_cache_block_size=16K # from 1K to reduce CPU cycles to manage key_cache
query_prealloc_size=32K # from 8K to reduce malloc frequency
query_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
transaction_prealloc_size=32K # from 4K to reduce malloc frequency
transaction_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
Some of these questions can NOT be answered with ONE one-line ANSWER, just the way it is.
For additional suggestions, view my profile, Network profile for contact information.
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
add a comment |
0
If you want the VIRT to be around 8G, then you will have to let your OS know to increase the SWAP to be more than 1G that is the limit according to your first screen shot in the question.
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425108%2fcant-reduce-mysql-5-7-virt-ram-use-too-high-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
The quick answer is to lower innodb_buffer_pool_size by enough to get VIRT down to the desired 8G. However,...
- Don't set it below 1G without further discussion;
- If lowering it does not change VIRT; let's discuss further;
- You have 0.5G "free" -- is there a real problem?
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
Ok, so innodb_buffer_pool_size will reserve sometimes more than double VIRT? It seems strange. Are there other settings that you saw in my config that add much to ram use? I do think .5G free of ram can be an issue as it will stop php-fpm and even though it should auto-start it doesn't always seem to.
– Kevin Chavez
Nov 29 '18 at 4:01
add a comment |
0
The quick answer is to lower innodb_buffer_pool_size by enough to get VIRT down to the desired 8G. However,...
- Don't set it below 1G without further discussion;
- If lowering it does not change VIRT; let's discuss further;
- You have 0.5G "free" -- is there a real problem?
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
Ok, so innodb_buffer_pool_size will reserve sometimes more than double VIRT? It seems strange. Are there other settings that you saw in my config that add much to ram use? I do think .5G free of ram can be an issue as it will stop php-fpm and even though it should auto-start it doesn't always seem to.
– Kevin Chavez
Nov 29 '18 at 4:01
add a comment |
0
0
0
The quick answer is to lower innodb_buffer_pool_size by enough to get VIRT down to the desired 8G. However,...
- Don't set it below 1G without further discussion;
- If lowering it does not change VIRT; let's discuss further;
- You have 0.5G "free" -- is there a real problem?
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
The quick answer is to lower innodb_buffer_pool_size by enough to get VIRT down to the desired 8G. However,...
- Don't set it below 1G without further discussion;
- If lowering it does not change VIRT; let's discuss further;
- You have 0.5G "free" -- is there a real problem?
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
answered Nov 22 '18 at 21:00
Rick JamesRick James
69.1k561101
69.1k561101
Ok, so innodb_buffer_pool_size will reserve sometimes more than double VIRT? It seems strange. Are there other settings that you saw in my config that add much to ram use? I do think .5G free of ram can be an issue as it will stop php-fpm and even though it should auto-start it doesn't always seem to.
– Kevin Chavez
Nov 29 '18 at 4:01
add a comment |
Ok, so innodb_buffer_pool_size will reserve sometimes more than double VIRT? It seems strange. Are there other settings that you saw in my config that add much to ram use? I do think .5G free of ram can be an issue as it will stop php-fpm and even though it should auto-start it doesn't always seem to.
– Kevin Chavez
Nov 29 '18 at 4:01
Ok, so innodb_buffer_pool_size will reserve sometimes more than double VIRT? It seems strange. Are there other settings that you saw in my config that add much to ram use? I do think .5G free of ram can be an issue as it will stop php-fpm and even though it should auto-start it doesn't always seem to.
– Kevin Chavez
Nov 29 '18 at 4:01
Ok, so innodb_buffer_pool_size will reserve sometimes more than double VIRT? It seems strange. Are there other settings that you saw in my config that add much to ram use? I do think .5G free of ram can be an issue as it will stop php-fpm and even though it should auto-start it doesn't always seem to.
– Kevin Chavez
Nov 29 '18 at 4:01
add a comment |
0
Suggestions to consider for your my.cnf [mysqld] section -
(based on information available as of 11/22/2018 in your question) - Rate Per Second=RPS
From Linux command prompt,
ulimit -n 65536 to support more Open Files
For Linux change to persist over shutdown/restart review this URL
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
Your details may be slightly different due to version.
For your MySQL instance my.cnf [mysqld]
thread_cache_size=64 # from 16 to reduce threads_created (expensive operation)
query_cache_limit=0 # from 16M to conserve RAM when QC not used
query_cache_min_res_unit=512 # from 4096 to conserve QC space if ever used
innodb_lru_scan_depth=100 # from 1024 to conserve CPU cycles every SECOND
table_open_cache=10000 # from 6000 and you may need more
key_cache_age_threshold=7200 # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50 # from 100 for Hot/Warm cache
key_cache_block_size=16K # from 1K to reduce CPU cycles to manage key_cache
query_prealloc_size=32K # from 8K to reduce malloc frequency
query_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
transaction_prealloc_size=32K # from 4K to reduce malloc frequency
transaction_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
Some of these questions can NOT be answered with ONE one-line ANSWER, just the way it is.
For additional suggestions, view my profile, Network profile for contact information.
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
add a comment |
0
Suggestions to consider for your my.cnf [mysqld] section -
(based on information available as of 11/22/2018 in your question) - Rate Per Second=RPS
From Linux command prompt,
ulimit -n 65536 to support more Open Files
For Linux change to persist over shutdown/restart review this URL
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
Your details may be slightly different due to version.
For your MySQL instance my.cnf [mysqld]
thread_cache_size=64 # from 16 to reduce threads_created (expensive operation)
query_cache_limit=0 # from 16M to conserve RAM when QC not used
query_cache_min_res_unit=512 # from 4096 to conserve QC space if ever used
innodb_lru_scan_depth=100 # from 1024 to conserve CPU cycles every SECOND
table_open_cache=10000 # from 6000 and you may need more
key_cache_age_threshold=7200 # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50 # from 100 for Hot/Warm cache
key_cache_block_size=16K # from 1K to reduce CPU cycles to manage key_cache
query_prealloc_size=32K # from 8K to reduce malloc frequency
query_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
transaction_prealloc_size=32K # from 4K to reduce malloc frequency
transaction_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
Some of these questions can NOT be answered with ONE one-line ANSWER, just the way it is.
For additional suggestions, view my profile, Network profile for contact information.
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
add a comment |
0
0
0
Suggestions to consider for your my.cnf [mysqld] section -
(based on information available as of 11/22/2018 in your question) - Rate Per Second=RPS
From Linux command prompt,
ulimit -n 65536 to support more Open Files
For Linux change to persist over shutdown/restart review this URL
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
Your details may be slightly different due to version.
For your MySQL instance my.cnf [mysqld]
thread_cache_size=64 # from 16 to reduce threads_created (expensive operation)
query_cache_limit=0 # from 16M to conserve RAM when QC not used
query_cache_min_res_unit=512 # from 4096 to conserve QC space if ever used
innodb_lru_scan_depth=100 # from 1024 to conserve CPU cycles every SECOND
table_open_cache=10000 # from 6000 and you may need more
key_cache_age_threshold=7200 # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50 # from 100 for Hot/Warm cache
key_cache_block_size=16K # from 1K to reduce CPU cycles to manage key_cache
query_prealloc_size=32K # from 8K to reduce malloc frequency
query_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
transaction_prealloc_size=32K # from 4K to reduce malloc frequency
transaction_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
Some of these questions can NOT be answered with ONE one-line ANSWER, just the way it is.
For additional suggestions, view my profile, Network profile for contact information.
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
Suggestions to consider for your my.cnf [mysqld] section -
(based on information available as of 11/22/2018 in your question) - Rate Per Second=RPS
From Linux command prompt,
ulimit -n 65536 to support more Open Files
For Linux change to persist over shutdown/restart review this URL
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
Your details may be slightly different due to version.
For your MySQL instance my.cnf [mysqld]
thread_cache_size=64 # from 16 to reduce threads_created (expensive operation)
query_cache_limit=0 # from 16M to conserve RAM when QC not used
query_cache_min_res_unit=512 # from 4096 to conserve QC space if ever used
innodb_lru_scan_depth=100 # from 1024 to conserve CPU cycles every SECOND
table_open_cache=10000 # from 6000 and you may need more
key_cache_age_threshold=7200 # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50 # from 100 for Hot/Warm cache
key_cache_block_size=16K # from 1K to reduce CPU cycles to manage key_cache
query_prealloc_size=32K # from 8K to reduce malloc frequency
query_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
transaction_prealloc_size=32K # from 4K to reduce malloc frequency
transaction_alloc_block_size=32K # from 8K to reduce CPU cycles to manage blocks
Some of these questions can NOT be answered with ONE one-line ANSWER, just the way it is.
For additional suggestions, view my profile, Network profile for contact information.
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
answered Nov 23 '18 at 10:54
Wilson HauckWilson Hauck
7501511
7501511
add a comment |
add a comment |
0
If you want the VIRT to be around 8G, then you will have to let your OS know to increase the SWAP to be more than 1G that is the limit according to your first screen shot in the question.
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
add a comment |
0
If you want the VIRT to be around 8G, then you will have to let your OS know to increase the SWAP to be more than 1G that is the limit according to your first screen shot in the question.
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
add a comment |
0
0
0
If you want the VIRT to be around 8G, then you will have to let your OS know to increase the SWAP to be more than 1G that is the limit according to your first screen shot in the question.
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
If you want the VIRT to be around 8G, then you will have to let your OS know to increase the SWAP to be more than 1G that is the limit according to your first screen shot in the question.
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
answered Nov 28 '18 at 19:37
Wilson HauckWilson Hauck
7501511
7501511
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425108%2fcant-reduce-mysql-5-7-virt-ram-use-too-high-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, cat /proc/meminfo includes VMallocUused, for server tuning analysis.
– Wilson Hauck
Nov 22 '18 at 16:16
Hoping you have applied my suggestions and seeing positive results. Let us know of any success or additional troubles, please. A more in-depth analysis could be accomplished by posting the info requested above.
– Wilson Hauck
Dec 7 '18 at 19:47
Merry Christmas. Would still like to assist - but without information, can not proceed with analysis of your system.
– Wilson Hauck
Dec 18 '18 at 20:32
Have you implemented any of the suggestions from my Nov 23, 18 Answer? Were the results positive or negative? Please view my profile, Network profile for contact info and get in touch via Skype Invite, please.
– Wilson Hauck
Jan 22 at 15:46