0

we have a prestashop website running at dedicated server with 10K products, I have slow perfomance and looking htop found that mysql CPU is 100%.

Dedicated Server

CPU:Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz

RAM:16 GB

mysql:

innodb_file_per_table
max_connections=500
max_user_connections=300
wait_timeout=100
interactive-timeout=100
table_open_cache=1024
thread_cache_size=64
join_buffer=16M
sort_buffer=32M
table_open_cache=5000
key_buffer_size=512M
innodb_buffer_pool_size=2GB
sort_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=128M
max_allowed_packet=32M
net_retry_count=512
thread_cache=8
thread_concurrency=32
server_id=1
collation_server=utf8_unicode_ci
character_set_server=utf8
tmp_table_size=128M
max_heap_table_size=128M
query_cache_limit=256K
query_cache_size=100MB
query_cache_type=1
concurrent_insert=2
low_priority_updates=1
open_files_limit=50000
default-storage-engine=MyISAM
slow_query_log

[mysqldump]
quick
max_allowed_packet=16M

What need to config/change for better performance? Thank you

Jaroslav Kucera
  • 1,435
  • 10
  • 16
teris
  • 25
  • 5
  • What is the output of the sql command show full processlist; – bgtvfr Nov 03 '17 at 09:08
  • What is the output of vmstat 2 10 ? – bgtvfr Nov 03 '17 at 09:09
  • [root@server ~]# vmstat 2 10 procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 0 210160 1629608 1704 12267484 1 2 38 23 3 2 32 1 67 0 0 2 0 210160 1629580 1704 12267476 0 0 0 3 3742 1142 25 0 75 0 0 2 0 210160 1629580 1704 12267476 0 0 0 0 3710 1117 25 0 75 0 0 – teris Nov 03 '17 at 09:12
  • You can always edit your own questions (and answers), regardless of reputation and that is the preferred way to add more info or to clarify things. Please don't use comments for that, especially if you want to preserve formatting. Thanks. – HBruijn Nov 03 '17 at 11:00
  • We need to know how much RAM your MySQL server has and please post in your Question text results of SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; SHOW ENGINE INNODB STATUS: – Wilson Hauck Nov 04 '17 at 10:02
  • @teris Do you still need assistance with this Nov 3, 2017 question? Thanks – Wilson Hauck Mar 07 '20 at 17:15

3 Answers3

0

Please check what is the current mysql process, some times the mysql service will take much resources when ever you have a bug in applications connected to the mysql servers.

Aswin Mohanan
  • 1
0

It's very hard to say which settings you should change, and what you should change them to. It appears you are using the default configuration settings, which are likely not ideal for your server.

A good place to start with your investigations is MySQLTuner, it's a popular script that you can run and looks at your current configuration and makes suggestions for improvement.

https://github.com/major/MySQLTuner-perl

Another useful tool is the slow query log. You can see which queries are taking a long time to run, they may be poorly written.

Don't get too aggressive with your MySQL caching settings if your web server also runs on the same server, heavy traffic could cause the server to run out of memory and start paging which will leave you dead in the water.

Bert
  • 2,733
  • 11
  • 12
0

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

join_buffer=16M   REMOVE, not a valid name
sort_buffer=32M   REMOVE, not a valid name
thread_cache=8   REMOVE, not a valid name
sort_buffer_size=8M  REMOVE to allow default of 2MB to work for you
read_buffer_size=8M  REMOVE to allow default of 128K to work for you
read_rnd_buffer_size=8M  REMOVE to allow default of 256K to work for you

The last 3 will improve user's response time (by minimizing read overhead) AND reduce your MySQL RAM footprint significantly. Use of MySQLCalculator.com will help you see why/how.

For detailed analysis, after one of more full working days, please post in your original question the following,

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

for additional specific configuration recommendations, one per day to be applied, monitor.

Wilson Hauck
  • 426
  • 4
  • 10
  • @teris Is your system running today? – Wilson Hauck Nov 18 '17 at 14:04
  • @teris If you still need assistance, please post in original question as a new detail item (or on pastebin.com) Additional information request, please. current complete my.cnf/ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; Optional very helpful information, if available includes - htop OR top for most active apps ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device df -h for a linux/unix free space list by device free -m for a linux/unix free memory report complete MySQLTuner.com report. – Wilson Hauck Mar 15 '18 at 00:54
  • @teris Are you still struggling with high cpu on your dedicated server? Please indicate if you still need assistance or not. – Wilson Hauck Mar 19 '18 at 14:51