0

Everyday during the peak time my server getting slow or down. Our hosting provider insisting us to upgrade the server but I think some performance tuning issue is there.

Adding the process information, server configuration and my.cnf parameters below.

Process Information

 PID  USER       PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                  
 60848 mysql     20   0 34.8g  23g 6416 S 2196.2 82.1  16027:29 mysqld 

Dedicated Server Configuration

Size:   'Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz', 2600MHz, 6 Core, Sockets: 2    
Image:  CentOS 6 64-bit with cPanel Fully-managed   
CPU:    Intel Dual Xeon E5-2620 v3
Speed:  2600MHz 
RAM:    32067MB 
CPUs:   2 Physical CPUs
Cores:  12 
Total Cores RAID:   Level 10
Disks:  4
Size:   917GB   
Type:   SSD 

MySQL Configuration

[mysqld]
slow_query_log = 1
#long_query_time = 2
long_query_time = 2
slow_query_log_file = /var/lib/mysql/vps-slow.log
performance-schema=0
max_connections = 250
max_allowed_packet=128M
#skip-external-locking
key_buffer_size = 256M
open_files_limit = 50000
table_open_cache = 15000
sort_buffer_size = 6M
join_buffer_size = 6M
read_buffer_size = 1M
read_rnd_buffer_size = 15M
myisam_sort_buffer_size = 64M
query_cache_type=1
query_cache_limit=2M
query_cache_size=128M
max_heap_table_size = 96M
tmp_table_size = 96M
#low_priority_updates=1

thread_cache_size = 32
wait_timeout = 300
connect_timeout=15
interactive_timeout = 800

innodb_buffer_pool_size=4096M
innodb_buffer_pool_instances = 4

group_concat_max_len=1000000

default-storage-engine=MyISAM
innodb_file_per_table=1

[mysqldump]
quick

Please help me if there is anything wrong with the configuration ?

Or

Do I really need to upgrade the server ?

PS: Hosted web application in the same server. Also WHM enabled for easy management.

  • What does your overall memory consumption look like at the time this happens? How about CPU utilization? Disk I/O? – David Jan 17 '18 at 16:40
  • MySQL is really busy. Optimize your database queries. – Michael Hampton Jan 17 '18 at 18:28
  • @David, I could see MySQL is taking most the resources cpu and memory. I couldn't find the exact Disk I/O on peak time – Jyothish Jan 20 '18 at 10:52
  • You can use the Performance schema to find the queries that cause your problems, but every optimization has its limits. Does your server have SSD or magnetic drives? What is the number of requests per second/minute/hour. There's always a chance your hosting provider might actually be right. – Ondřej Xicht Světlík Jan 20 '18 at 14:37
  • @Jyothish Please add to your original question (or post in pastebin.com) Additional information request, current complete my.cnf/ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) SHOW ENGINE INNODB STATUS; 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 if available. – Wilson Hauck Mar 15 '18 at 1:27
0

Suggestions your my.cnf-ini [mysqld] section

innodb_buffer_pool_size=4G  # from 4096M  - instances will be evenly divisible
#sort_buffer_size=6M  lead with # for default
#read_buffer_size=1M  lead with # for default
#read_rnd_buffer_size=15M  lead with # for default
#join_buffer_size=6M  lead with # for default
query_cache_min_res_unit=512  # from 4096 default to reduce QC RAM required per SELECT

Please use MySQLCalculator.com for 2 minutes to recognize your X per connection values are much higher than necessary to have a more predicatable instance of MySQL.

Also, if you could post complete report from A) ulimit -a for your Linux OS limits and B) Mysqltuner.com complete report;

we would have helpful information, including the version of MySQL you are running, by ENGINE table data space and count of tables, all helpful information. Thanks

| improve this answer | |

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.