2

I have a MySQL server running on CentOS.

Recently I am having an issue that happens about every 2 days. The server is running fast and OK, but then suddenly it's getting very slow, until I restart MySQL, then it returns to a regular state.

This happened to me a couple of times, so this time I took 2 screenshots before I run service mysqld restart.

Before restart: Before restart

After restart: After restart

Most of my tables are InnoDB, a few are MyISAM. (4 MyISAM tables, 38 InnoDB tables)

my.cnf:

[mysqld]
bulk_insert_buffer_size = 8M
concurrent_insert = 2
connect_timeout = 30
default-storage-engine = MyISAM
innodb_buffer_pool_size=1300M
innodb_file_per_table=1
interactive_timeout = 1000
join_buffer_size=128M
key_buffer_size = 1200M
local-infile=0
slow_query_log=1
long_query_time=0.5
#skip-grant-tables
max_allowed_packet = 900M
max_connections = 40000
max_heap_table_size = 256M
max_user_connections = 10000
max_write_lock_count = 8
myisam_max_sort_file_size = 256M
myisam_sort_buffer_size = 64M
open_files_limit = 10192
query_alloc_block_size = 65536
query_cache_limit = 256M
query_cache_size = 384M
query_cache_type = 1
query_prealloc_size = 262144
range_alloc_block_size = 4096
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 4M
table_cache = 8048
table_open_cache = 8000
thread_cache_size = 50
tmp_table_size = 256M
transaction_alloc_block_size = 4096
transaction_prealloc_size = 4096
#innodb_force_recovery=5
wait_timeout = 1000
max_connect_errors = 5000
open-files = 50000
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

SHOW GLOBAL STATUS LIKE '%connect%';

+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 0      |
| Connections              | 859148 |
| Max_used_connections     | 103    |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 1      |
+--------------------------+--------+

SHOW GLOBAL VARIABLES LIKE 'thread_%';

+---------------------------+---------------------------+
| Variable_name             | Value                     |
+---------------------------+---------------------------+
| thread_cache_size         | 50                        |
| thread_concurrency        | 10                        |
| thread_handling           | one-thread-per-connection |
| thread_pool_idle_timeout  | 60                        |
| thread_pool_max_threads   | 500                       |
| thread_pool_oversubscribe | 3                         |
| thread_pool_size          | 8                         |
| thread_pool_stall_limit   | 500                       |
| thread_stack              | 294912                    |
+---------------------------+---------------------------+

SHOW GLOBAL STATUS LIKE 'threads_%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 49    |
| Threads_connected | 1     |
| Threads_created   | 372   |
| Threads_running   | 1     |
+-------------------+-------+

SHOW GLOBAL STATUS LIKE 'key_%';

+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 1003901 |
| Key_blocks_used        | 3365    |
| Key_blocks_warm        | 0       |
| Key_read_requests      | 99176   |
| Key_reads              | 3052    |
| Key_write_requests     | 29353   |
| Key_writes             | 29347   |
+------------------------+---------+

SHOW GLOBAL STATUS LIKE 'Q%';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 961       |
| Qcache_free_memory      | 400828904 |
| Qcache_hits             | 1634009   |
| Qcache_inserts          | 1201887   |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 59970     |
| Qcache_queries_in_cache | 1467      |
| Qcache_total_blocks     | 3926      |
| Queries                 | 5316596   |
| Questions               | 5187929   |
+-------------------------+-----------+

SHOW GLOBAL VARIABLES LIKE '_size';

Empty set
  • Based on your top output, this server is way overprovisioned. You're definitely not out of RAM or CPU capacity. However, this data doesn't point to any kind of problem. Try looking elsewhere, such as disk I/O, CPU I/O wait, disk and filesystem health, or network utilization. – Spooler Oct 31 '17 at 23:40
  • @SmallLoanOf1M Thank you for commenting, can you give me some helpful commands for checking those things? – HTMHell Oct 31 '17 at 23:54
  • Check the MySQL process running with mysqladmin pr. And see any complex query running long time consuming more resources. Take action depend on to optimize query or sql configuration. – Sunil Bhoi Nov 1 '17 at 8:47
  • try to find process, queries running under mysql. Use command "mysqladmin pr -u root -p". if some queries are taking time – Sunil Bhoi Nov 1 '17 at 9:12
1

This appears to be more of a client load issue, rather than a memory-leaking server issue. The daemon's threads are chewing on roughly one or two cores. What are they busy with? What does SHOW FULL PROCESSLIST say?

The restart did lots more than reset the state of the daemon. It blew away 587 processes that presumably had active port 3306 (or AF_UNIX) connections to the server. What were they doing? Are you happy with what they were doing? Did they log fatal errors at time of restart that make you unhappy? Should they maybe be completing some task and then disconnecting and exiting?

The restart is a quick fix, but it looks like you want to gain an understanding of how client load grows greater and greater over the course of 48 hours preceding the restart.

  • So during the next "slowness", I should run SHOW FULL PROCESSLIST to look at what's running at that moment? And during the restart I got only one error: [ERROR] mysqld: Got an error writing communication packets – HTMHell Nov 1 '17 at 8:12
  • Yes, mysql's processlist will be helpful. Also tail -f the slow queries log. And save output of netstat -an before & after, then diff them, to see which network and AF_UNIX socket connections went away due to the restart - that may give you IP addresses of clients holding lots of connections open. And save ps axuw output before & after, to reveal local clients holding open connections. – J_H Nov 1 '17 at 18:38
  • Please post with your question text results of following; A. SHOW GLOBAL STATUS LIKE '%connect%'; B, SHOW GLOBAL VARIABLES LIKE 'thread_%'; C. SHOW GLOBAL STATUS LIKE 'threads_%' – Wilson Hauck Nov 1 '17 at 20:48
  • Please also post text results of D. SHOW GLOBAL STATUS LIKE 'key_%'; E. SHOW GLOBAL STATUS LIKE 'Q%'; so we have some idea of your workload. If results of MySQLTuner.com software could be posted, many details would be 'revealed'. Some of their advice is not relevant. – Wilson Hauck Nov 1 '17 at 21:07
  • @htmhell Please see the two comments above requesting additional information for analysis. – Wilson Hauck Nov 2 '17 at 0:23
1

For much immediate relief, after reviewing your reference manual, consider

set global read_rnd_buffer_size=256K;  # from 16M per connection

This can be done dynamically.
Login's after this is done will not require 16M per login. Why read 16M (even if it is from RAM) when 256K will be fine? After posting other requested items, I will have additional suggestions.

----- 2017 11 04 ------------- The following suggestions need your research before implementing ONLY one item per day. Some may be applied dynamically. Suggested cfg/ini values follow for [mysqld] section and could be modify, add or remove.

max_connections=200 #from 40000 to support your 103 max_used_connections
max_user_connections=200 #from 10000 to be matched with max_connections
key_buffer_size  REMOVE for default of 64M.  less than 1% of 1200MB used

thread_cache_size=100 #from 50  to support your 103 max_used_connections - cap at 100 per V8
thread_concurrency=33 #from 10  for about 30% active
max_connect_errors=10 #from 5000, to better control hacker passwd guessing
innodb_print_all_deadlocks=1 # from OFF, if you ever have one, you need this data in error log
#### these are PER CONNECTION values driving your RAM footprint up the wall
#read_buffer_size  or REMOVE for default of 128K vs 4M RAM
#read_rnd_buffer_size or REMOVE for default of 256K vs 16M RAM
#join_buffer_size or REMOVE for default of 128K vs 128MB RAM 

Use of MySQLCalculator.com will help you see how much RAM you could require if 40000 concurrent connections could be successful (not likely to ever happen) - appx 6 Terrabytes of RAM would be required.

For additional analysis of results reflecting changes implemented, AFTER 7 days of uptime please post complete text results of

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW ENGINE INNODB STATUS;

and repost complete my.cnf.

  • Should I run the other queries while the database is slow, or it doesn't matter? – HTMHell Nov 2 '17 at 14:00
  • The SHOW queries will only take seconds. Please add 1 more to the list. F: SHOW GLOBAL VARIABLES LIKE '_size'; Run them anytime. – Wilson Hauck Nov 2 '17 at 19:48
  • Please see updated question. – HTMHell Nov 3 '17 at 10:00
  • My apologies, my mistake on F: should be SHOW GLOBAL VARIABLES LIKE '%_size'; and post in your Question, repost your current content of my.cnf please. Are you making any progress? – Wilson Hauck Nov 8 '17 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.