0

On mysql community 5.5 (centos 6), VM on hyper-v win 2008 r2, 4 CPU, 48 GB RAM

Which setting(s) that I would need to change to reduce cpu usage and get my web app (joomla) to respond faster without buying new hardware? 

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 40G
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

symbolic-links=0

user=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Improve this question

2 Answers 2

Reset to default
1

Use mysqltuner for some quick suggestions. At a glance it's easy to tell that you will be alerted for high memory allocation!! Your innodb-buffer-pool is much higher WRT available memory.

About high CPUs, what does your processlist or slow query log tells you? Sort or long running queries, using tmp tables? Queries not using indexes? Make sure you have well indexed tables for respective queries.

Use explain to review and optimize your queries.

Improve this answer
8
  • Since I'm not a DBA, unsure what to do. I checked mysql-slow.log and same query with varying execution time from 0.01 seconds to 60 seconds.
    – grepmaster
    Commented Aug 11, 2015 at 4:40
  • 60 Sec is way too long if that isn't a reporting sort-of query!!! You might want to fix those speed issues first and you will surely see the improvements.
    – mysql_user
    Commented Aug 11, 2015 at 6:26
  • it mainly does inventory but I can see "systems busy" from browser
    – grepmaster
    Commented Aug 11, 2015 at 6:31
  • as said... Run mysqltuner and try to follow it's suggestions. Very first thing you should do is reduce your innodb_buffer_pool which is way higher than suggested (2/3rd of total ram)... there are many other considerations at my.cnf changes but everything else can wait for the query tuning. Fix the slow queries, mostly you're missing indexes... See to get some DBA help!
    – mysql_user
    Commented Aug 11, 2015 at 6:39
  • 1
    @mysql_user please don't recommend mysqltuner or other tuning scripts. They tend to provoke people to make uninformed changes, and they often offer very ridiculous recommendations that can degrade performance, sometimes more often than not. Parameter is tunable != parameter should be tuned. Also, 40GB buffer pool on a 48GB server dedicated to MySQL is perhaps slightly large, but isn't going to increase CPU utilization. Poorly-written queries need to be found and fixed.
    – Michael - sqlbot
    Commented Aug 11, 2015 at 10:02
1

1) Can you check the value of innodb_thread_concurrency?

Try setting it to 8. Default is 0 (disabled)

https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency

2) Check the slow query log and see if many queries are using filesort. Usually sorting uses lot of CPU. If there are lot of queries using filesort, try to optimize them.

Improve this answer
3
  • I am not sure why you would believe that advertising your services in the body of an answer would be helpful or acceptable here. If you want to provide valuable and useful answers, we welcome that. If you want to mention your services on your profile, that's acceptable, too... but if you are looking for a place to gratuitously include links to your web site under the auspices of an answer, I would not recommend the Stack Exchange sites. This will not be well-received.
    – Michael - sqlbot
    Commented Aug 11, 2015 at 10:19
  • Thank you for the edit. I have removed my down-vote, but I don't believe an up-vote is necessarily merited. innodb_thread_concurrency = 0 does not mean "disabled," it means "infinite." The suggestion "check the slow query log" is not necessarily useful, as you can see that it is enabled and OP mentioned having checked the log without fully understanding the implications, about 3 hours before you posted. Answers need to focus on solutions, rather than suggestions, and in this case, we don't really have enough background info to find and solve a problem.
    – Michael - sqlbot
    Commented Aug 11, 2015 at 11:25
  • @Michael-sqlbot: you did not get it right what I meant by disabled. Check the documentation "Disabling thread concurrency checking enables InnoDB to create as many threads as it needs. A value of 0 also disables the queries inside InnoDB and queries in queue counters in the ROW OPERATIONS section of SHOW ENGINE INNODB STATUS output." Coming to slow query log, "OP mentioned having checked the log without fully understanding the implications, about 3 hours before you posted" that's why I suggested to specifically look for queries that use filesort. The suggestions are part of solutions
    – DBHash.com
    Commented Aug 11, 2015 at 12:00

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