2

I am using Centos 6 64bit on vps with 4gb ram. 1 wordpress website and 2 wallpapers script websites. Wordrpess have 10k visits daily. Site is slow.

My.cnf

# Example mysql config file for large systems.
#
# This is for large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-locking
set-variable    = key_buffer=256M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=256
set-variable    = sort_buffer=1M
set-variable    = record_buffer=1M
set-variable    = myisam_sort_buffer_size=64M
set-variable    = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable    = thread_concurrency=8
log-bin
server-id   = 1

# Uncomment the following if you are using BDB tables
#set-variable   = bdb_cache_size=64M
#set-variable   = bdb_max_lock=100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#set-variable = innodb_buffer_pool_size=256M
#set-variable = innodb_additional_mem_pool_size=20M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=64M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50

# Point the following paths to different dedicated disks
#tmpdir     = /tmp/     
#log-update     = /path-to-dedicated-directory/hostname

[mysqldump]
quick
set-variable    = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable    = key_buffer=128M
set-variable    = sort_buffer=128M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[myisamchk]
set-variable    = key_buffer=128M
set-variable    = sort_buffer=128M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

This is ./mysqltuner.pl

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.27-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 16M (Tables: 61)
[--] Data in InnoDB tables: 4M (Tables: 34)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 34

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9m 53s (40K q [68.862 qps], 3K conn, TX: 203M, RX: 4M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 432.0M global + 6.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.4G (36% of installed RAM)
[OK] Slow queries: 0% (0/40K)
[OK] Highest usage of available connections: 74% (113/151)
[OK] Key buffer size / total MyISAM indexes: 256.0M/10.1M
[OK] Key buffer hit rate: 97.9% (152K cached / 3K reads)
[OK] Query cache efficiency: 62.0% (17K cached / 27K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 224 sorts)
[OK] Temporary tables created on disk: 25% (71 on disk / 277 total)
[OK] Thread cache hit rate: 77% (831 created / 3K connections)
[OK] Table cache hit rate: 39% (135 open / 342 opened)
[OK] Open file limit used: 15% (162/1K)
[OK] Table locks acquired immediately: 98% (13K immediate / 13K locks)
[OK] InnoDB data size / buffer pool: 4.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
3
  • 2
    Run mysqltuner after the server has been up for a day or two and update the results. Running it after 9 minutes isn't going to give enough information.
    – R. S.
    Jan 26, 2013 at 21:22
  • An updated mysqltuner report with the server up for a while would be helpful. Is anything slow or are you being preemptive with the performance? As long as it's not getting in the way it's not a huge problem, but your key buffer is way too big; 16M would be a better size.
    – G-Nugget
    Jan 29, 2013 at 22:32
  • I would suggest you to use MariaDB instead of MySQL as I always feel that it consumes less RAM comparing with MySQL. But be noted that never use TokuDB as it will use 50% of RAM as Cache. :) You can check MariaDB website for more info. Feb 4, 2015 at 6:48

4 Answers 4

1

Try to increase both the innodb_buffer_pool_size (to 512M) and query cache size (query_cache_type=1 and query_cache_size=256M).

Some simple, general recommendation: http://www.ilsistemista.net/index.php/linux-a-unix/26-mysql-performance-tuning.html?limitstart=0

0

Try installing a caching plugin for your WordPress installation. I personally recommend W3 Super Cache:

http://wordpress.org/extend/plugins/wp-super-cache/

You can also install XCache and configure PHP as FCGI to get better caching(WP Super Cache works with XCache as well as standalone).

1
  • I used it but it caused server problems and unstability. I am using hyper cache now. Any sugestions for my.cnf optimization ?
    – user156220
    Jan 27, 2013 at 11:19
0

Percona has a pretty decent configuration wizard which will spit out a my.cnf file closely tuned to your server and workload: https://tools.percona.com/wizard

It wont give you a perfect configuration, but you can compare to what you are currently running and see why they selected certain settings.

0

Why do think mysql is a bottleneck? Im pretty shure php is the issue here, since i manage lot of magento websites with more visits than 10k/day.

Wp is 100%compatible with php7 so start from there. Do it in a development environment first then in live. Also a cacheing plugin is a good boost, i suggest redis cacheing simple super fast. Next step would be to move mysql/mariadb to another vm. Since you are pumping up my.cnf you are stealing cpu/ram to php so 1 vm for php and one for mysql should help a lot

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .