MySQL Forums
Forum List  »  Performance

High memory usage on mysql 5.6.36-log on linux server
Posted by: Priya Pawar
Date: January 25, 2018 04:55AM

Hello experts,

I am observing high memory usage on mysql 5.6.36-log on linux server. The server is dedicated for mysql server only.

Any suggestions to bring down the memory usage please?

free and top output :

free -m
total used free shared buffers cached
Mem: 7872 7711 160 0 152 2264
-/+ buffers/cache: 5294 2577
Swap: 6023 464 5559

top - 04:41:49 up 358 days, 17:48, 3 users, load average: 0.00, 0.00, 0.00
Tasks: 146 total, 1 running, 145 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.2%us, 0.1%sy, 0.0%ni, 98.6%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8061348k total, 7896888k used, 164460k free, 155756k buffers
Swap: 6168572k total, 475320k used, 5693252k free, 2319116k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
498 mysql 20 0 6427m 4.9g 6288 S 2.0 63.2 12762:44 mysqld


Mysql status :
+-----------------------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------------------+--------------+
| Aborted_clients | 404893 |
| Aborted_connects | 360 |
| Binlog_cache_disk_use | 13216 |
| Binlog_cache_use | 1428969 |
| Binlog_stmt_cache_disk_use | 198 |
| Binlog_stmt_cache_use | 1604316 |
| Bytes_received | 30885101333 |
| Bytes_sent | 112986107973 |
| Com_admin_commands | 151973 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 520 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 8 |
| Com_begin | 799 |
| Com_binlog | 0 |
| Com_call_procedure | 1035 |
| Com_change_db | 4006 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 1526091 |
| Com_create_db | 8 |
| Com_create_event | 0 |
| Com_create_function | 4 |
| Com_create_index | 5 |
| Com_create_procedure | 5 |
| Com_create_server | 0 |
| Com_create_table | 176546 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 91650 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 3 |
| Com_drop_procedure | 8 |
| Com_drop_server | 0 |
| Com_drop_table | 165747 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 5 |
| Com_get_diagnostics | 9 |
| Com_grant | 25 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 26466 |
| Com_insert | 6558468 |
| Com_insert_select | 802 |
| Com_install_plugin | 0 |
| Com_kill | 464 |
| Com_load | 0 |
| Com_lock_tables | 188 |
| Com_optimize | 61 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 1 |
| Com_release_savepoint | 0 |
| Com_rename_table | 8 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 2935840 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 65660668 |
| Com_set_option | 10820830 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 120466 |
| Com_show_collations | 405570 |
| Com_show_create_db | 49 |
| Com_show_create_event | 0 |
| Com_show_create_func | 51 |
| Com_show_create_proc | 1051 |
| Com_show_create_table | 530 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 2917 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 8 |
| Com_show_events | 2 |
| Com_show_errors | 0 |
| Com_show_fields | 50238 |
| Com_show_function_code | 0 |
| Com_show_function_status | 1765 |
| Com_show_grants | 0 |
| Com_show_keys | 658 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 1 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 1 |
| Com_show_procedure_status | 1775 |
| Com_show_processlist | 19058 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1190 |
| Com_show_storage_engines | 67 |
| Com_show_table_status | 3678 |
| Com_show_tables | 2787 |
| Com_show_triggers | 13477 |
| Com_show_variables | 409278 |
| Com_show_warnings | 1981009 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 12 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 188 |
| Com_update | 1045515 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 929012 |
| Created_tmp_disk_tables | 48779 |
| Created_tmp_files | 23156 |
| Created_tmp_tables | 2153438 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 83535432 |
| Handler_delete | 916071 |
| Handler_discover | 0 |
| Handler_external_lock | 151527946 |
| Handler_mrr_init | 0 |
| Handler_prepare | 12423068 |
| Handler_read_first | 5766903 |
| Handler_read_key | 11091656374 |
| Handler_read_last | 2 |
| Handler_read_next | 8756723126 |
| Handler_read_prev | 164 |
| Handler_read_rnd | 4939841931 |
| Handler_read_rnd_next | 565755735622 |
| Handler_rollback | 3203999 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 45599729 |
| Handler_write | 435105053 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 246416 |
| Innodb_buffer_pool_bytes_data | 4037279744 |
| Innodb_buffer_pool_pages_dirty | 6935 |
| Innodb_buffer_pool_bytes_dirty | 113623040 |
| Innodb_buffer_pool_pages_flushed | 14172598 |
| Innodb_buffer_pool_pages_free | 8322 |
| Innodb_buffer_pool_pages_misc | 7398 |
| Innodb_buffer_pool_pages_total | 262136 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 461632 |
| Innodb_buffer_pool_read_ahead_evicted | 139 |
| Innodb_buffer_pool_read_requests | 410700343016 |
| Innodb_buffer_pool_reads | 1143577 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 433352786 |
| Innodb_data_fsyncs | 6528543 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 27307266048 |
| Innodb_data_reads | 1727932 |
| Innodb_data_writes | 19749707 |
| Innodb_data_written | 512169268224 |
| Innodb_dblwr_pages_written | 14172598 |
| Innodb_dblwr_writes | 1944241 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 100970610 |
| Innodb_log_writes | 2772092 |
| Innodb_os_log_fsyncs | 1100179 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 47642263552 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 2748902 |
| Innodb_pages_read | 1666698 |
| Innodb_pages_written | 14172598 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 2744745 |
| Innodb_row_lock_time_avg | 921 |
| Innodb_row_lock_time_max | 51850 |
| Innodb_row_lock_waits | 2980 |
| Innodb_rows_deleted | 551426 |
| Innodb_rows_inserted | 61198675 |
| Innodb_rows_read | 306351957014 |
| Innodb_rows_updated | 14853846 |
| Innodb_num_open_files | 256 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 26765 |
| Key_blocks_used | 26792 |
| Key_read_requests | 2724776968 |
| Key_reads | 23289981 |
| Key_write_requests | 9916025 |
| Key_writes | 2338275 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 431 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 135 |
| Open_streams | 0 |
| Open_table_definitions | 380 |
| Open_tables | 1922 |
| Opened_files | 781372 |
| Opened_table_definitions | 332597 |
| Opened_tables | 170555 |
| Performance_schema_accounts_lost | 225 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 210 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 579 |
| Qcache_free_memory | 42263152 |
| Qcache_hits | 12352195 |
| Qcache_inserts | 1967643 |
| Qcache_lowmem_prunes | 155223 |
| Qcache_not_cached | 63545068 |
| Qcache_queries_in_cache | 996 |
| Qcache_total_blocks | 3222 |
| Queries | 130495462 |
| Questions | 104923440 |
| Select_full_join | 5581 |
| Select_full_range_join | 3 |
| Select_range | 1091380 |
| Select_range_check | 0 |
| Select_scan | 7735227 |
| Slave_heartbeat_period | 0.000 |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 16915 |
| Sort_merge_passes | 11316 |
| Sort_range | 761801 |
| Sort_rows | 5830964666 |
| Sort_scan | 2794082 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 73705867 |
| Table_locks_waited | 695337 |
| Table_open_cache_hits | 74206049 |
| Table_open_cache_misses | 8048 |
| Table_open_cache_overflows | 728 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 60 |
| Threads_connected | 14 |
| Threads_created | 1860 |
| Threads_running | 1 |
| Uptime | 22624859 |
| Uptime_since_flush_status | 22624859 |






Below is mysqlTuner output.

>> MySQLTuner 1.7.5 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.6.36-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql.log(32K)
[OK] Log file /var/log/mysql.log exists
[!!] Log file /var/log/mysql.log isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 175M (Tables: 57)
[--] Data in InnoDB tables: 10G (Tables: 248)
[!!] Total fragmented tables: 1

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'ul_transUser@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 261d 21h 10m 22s (104M q [4.638 qps], 929K conn, TX: 105G, RX: 28G)
[--] Reads / Writes: 89% / 11%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 7.7G
[--] Max MySQL memory : 23.0G
[--] Other process memory: 79.0M
[--] Total buffers: 4.2G global + 34.2M per thread (550 max threads)
[--] P_S Max memory usage: 471M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 19.1G (247.87% of installed RAM)
[!!] Maximum possible memory usage: 23.0G (299.64% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (16K/104M)
[OK] Highest usage of available connections: 78% (431/550)
[OK] Aborted connections: 0.04% (361/929031)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 15.8% (12M cached / 78M selects)
[!!] Query cache prunes per day: 592
[OK] Sorts requiring temporary tables: 0% (11K temp sorts / 3M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 2% (48K on disk / 2M total)
[OK] Thread cache hit rate: 99% (1K created / 929K connections)
[!!] Table cache hit rate: 1% (1K open / 170K opened)
[OK] Open file limit used: 2% (135/5K)
[OK] Table locks acquired immediately: 99% (73M immediate / 74M locks)
[OK] Binlog cache memory access: 99.08% (1415753 Memory / 1428969 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 471.6M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/33.9M
[OK] Read Key buffer hit rate: 99.1% (2B cached / 23M reads)
[!!] Write Key buffer hit rate: 23.6% (9M cached / 2M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 8
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.0G/10.8G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (410699311735 hits/ 410700455312 total)
[OK] InnoDB Write log efficiency: 97.25% (98198533 hits/ 100970672 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2772139 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `infopltfrm_transDBv08`.`entity_attributes`; -- can free 193 MB
Total freed space after theses OPTIMIZE TABLE : 193 Mb
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (5000) variable
should be greater than table_open_cache (2000)
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 8M, or use smaller result sets)
query_cache_size (> 64M)
table_open_cache (> 2000)
innodb_buffer_pool_size (>= 10G) if possible.
innodb_buffer_pool_instances(=4)

Also innodb_buffer_pool_size in my.cnf file is set to 4G.


mysql> select
-> concat(round(sum(table_rows)/1000000,2),'m') rows,
-> concat(round(sum(data_length)/(1024*1024*1024),2),'g') data,
-> concat(round(sum(index_length)/(1024*1024*1024),2),'g') idx,
-> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'g') total_size,
-> round(sum(index_length)/sum(data_length),2) idxfrac
-> from information_schema.tables
-> where engine = 'innodb';
+--------+-------+-------+------------+---------+
| rows | data | idx | total_size | idxfrac |
+--------+-------+-------+------------+---------+
| 29.29m | 6.30g | 4.46g | 10.76g | 0.71 |
+--------+-------+-------+------------+---------+

Can anyone please help me to bring down the utilization?

Thanks,
Priya

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.