MySQL Usage
in the last days, 2 of my cpanel servers face the same problem,
websites stop loading, while the load of the server is not high (1-5)
but when I stop mysql (/etc/init.d/stop) the load in half second,
the next time this happened I did run watch 'mysqladmin proc'
and I see endless screens of requests that do not seem to get completed
or endless screens of sleeped ones.
I did put in both my.cnf files these: interactive_timeout=60 wait_timeout=60 but still the same problem occurs. also mysql doesn't have high ram/cpu usage during the problems.. what could be the reason? am I attacked?
+-------+------------------+-----------+-----------------------------+----------------+------+--------------------+-----------------------------------------------------
-------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+-------+------------------+-----------+-----------------------------+----------------+------+--------------------+-----------------------------------------------------
| 14 | eximstats | localhost | eximstats | Query | 264 | Opening tables | INSERT INTO sends (mailtime,msgid,processed,domain,email,user,size,host,ip,auth,localsender,spamsco |
| 38733 | user123 | localhost | ortho983_anorthosis413 | Query | 590 | closing tables | SELECT value FROM xcart_languages WHERE code='el' AND name='txt_err_msg_code_79' LIMIT 1 |
| 38886 | user123 | localhost | ortho983_anorthosis413 | Query | 29 | Opening tables | SELECT value FROM xcart_languages WHERE code='el' AND name='txt_err_msg_code_79' LIMIT 1 |
| 38893 | user123 | localhost | ortho983_anorthosis413 | Query | 5 | Opening tables | SELECT value FROM xcart_languages WHERE code='el' AND name='txt_err_msg_code_79' LIMIT 1 |
| 38909 | user333 | localhost | aftonomi_jomsite | Query | 820 | Opening tables | SELECT m.id, m.title, m.module, m.position, m.content, m.showtitle, m.params, mm.menuidFROM e8eqw_m |
| 38923 | user12121 | localhost | kotesspa_base | Query | 863 | Opening tables | select CollectionVersionBlocks.isOriginal, BlockTypes.pkgID, CollectionVersionBlocks.cbOverrideAreaP |
| 38924 | user333 | localhost | aftonomi_jomsite | Query | 40 | Opening tables | SELECT COUNT(*) FROM e8eqw_k2_comments WHERE itemID=2805 AND published=1 |
| 38927 | user12121 | localhost | kotesspa_base | Query | 143 | closing tables | select csrID from CollectionVersionBlockStyles where cID = '1' and cvID = '130' and arHandle = 'Head |
| 38932 | user333 | localhost | aftonomi_jomsite | Query | 149 | Opening tables | SELECT COUNT(*) FROM e8eqw_k2_comments WHERE itemID=2805 AND published=1 |
| 38935 | user12121 | localhost | kotesspa_base | Query | 879 | Opening tables | select CollectionVersionBlocks.isOriginal, BlockTypes.pkgID, CollectionVersionBlocks.cbOverrideAreaP |
| 38936 | user12121 | localhost | kotesspa_base | Query | 879 | closing tables | select CollectionVersionBlocks.isOriginal, BlockTypes.pkgID, CollectionVersionBlocks.cbOverrideAreaP |
| 38937 | user333 | localhost | aftonomi_jomsite | Query | 457 | Opening tables | UPDATE `e8eqw_session`SET `data` = '__default|a:7:{s:15:\"session.counter\";i:1;s:19:\"session.time |
| 38944 | user333 | localhost | aftonomi_jomsite | Query | 76 | Opening tables | SELECT COUNT(*) FROM e8eqw_k2_comments WHERE itemID=2805 AND published=1 |
| 38947 | user123 | localhost | ortho983_anorthosis413 | Query | 6 | Opening tables | SHOW FIELDS FROM xcart_stats_shop
| 38949 | user333 | localhost | aftonomi_jomsite | Query | 104 | Opening tables | SELECT * FROM e8eqw_k2_items WHERE id != 3972 AND catid=18 AND ordering > 2225 AND published=1 AND ( |I did put in both my.cnf files these: interactive_timeout=60 wait_timeout=60 but still the same problem occurs. also mysql doesn't have high ram/cpu usage during the problems.. what could be the reason? am I attacked?
-
Re: serious MySQL problem Hello, Please check error_log, in you website folder and check server error logs. 0 -
Re: serious MySQL problem The mysql server needs to be optimized. You can optimize it by changing the parameters in /etc/my.cnf 0 -
Re: serious MySQL problem [quote="SS-Maddy, post: 1672932">The mysql server needs to be optimized. You can optimize it by changing the parameters in /etc/my.cnf
I've allready done that, with MySQLTuner ( did run it 2 times, 1 every 24 hours) and added all the suggestions to /etc/my.cnf0 -
Re: serious MySQL problem Hello :) Could you let us know the full contents of your /etc/my.cnf file? I have moved this thread to the "Optimization" forum where you are likely to receive more user feedback. Thank you. 0 -
Hello, yes it is: root@[~]# cat /etc/my.cnf [mysqld] max_connections = 800 innodb_file_per_table=1 open_files_limit=50000 interactive_timeout=30 wait_timeout=30 # debug long_query_time=0.3 log-slow-queries=/var/log/mysql-slow.log #last additions 24-06-2014 query_cache_type = 1 query_cache_size = 64M query_cache_limit = 1M join_buffer_size = 512K thread_cache_size = 4 table_open_cache = 1024 innodb_buffer_pool_size = 1G innodb_log_buffer_size = 2M max_heap_table_size = 32M0
Please sign in to leave a comment.
Comments
5 comments