Page 1 of 1
Best my.cnf config for high traffic wordpress site
Posted: Tue May 26, 2015 6:26 pm
by vandread911
Hello all,
as the subject states, what would be the best my.cnf config for a high traffic wordpress site, hosted on a 32 GB ram dedicated server, of which contains 15 + plugins.
(using vestacp), in need of a suitable config file that is optimised for a high traffic websites that uses TB's of bandwidth each month
Re: Best my.cnf config for high traffic wordpress site
Posted: Tue May 26, 2015 6:43 pm
by skurudo
Something like this
Code: Select all
#
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
socket = /var/run/mysqld/mysqld.sock
port = 3306
user = mysql
# applies only when running as root
#memlock = 1
table_open_cache = 3072
table_definition_cache = 4096
max_heap_table_size = 64M
tmp_table_size = 64M
max_connections = 505
max_user_connections = 500
max_allowed_packet = 16M
thread_cache_size = 32
query_cache_size = 64M
# InnoDB
default_table_type = InnoDB
# 80% of ram that is dedicated for the database (this needs to be adjusted to your system)
innodb_buffer_pool_size = 14G
# number of CPU cores dedicated to the MySQL InnoDB backend
innodb_buffer_pool_instances = 16
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_per_table = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
# MyISAM
myisam_recover = backup,force
# Logging
log_warnings = 2
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 20
# Binary Log / Replication
server_id = 1
log-bin = mysql-bin
binlog_cache_size = 1M
sync_binlog = 8
binlog_format = row
expire_logs_days = 7
max_binlog_size = 128M
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqld_safe]
open-files-limit = 8192
log-error = /var/log/mysql/error.log
and cache-cache everything ;-)
Re: Best my.cnf config for high traffic wordpress site
Posted: Wed May 27, 2015 4:50 am
by vandread911
skurudo wrote:Something like this
Code: Select all
#
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
socket = /var/run/mysqld/mysqld.sock
port = 3306
user = mysql
# applies only when running as root
#memlock = 1
table_open_cache = 3072
table_definition_cache = 4096
max_heap_table_size = 64M
tmp_table_size = 64M
max_connections = 505
max_user_connections = 500
max_allowed_packet = 16M
thread_cache_size = 32
query_cache_size = 64M
# InnoDB
default_table_type = InnoDB
# 80% of ram that is dedicated for the database (this needs to be adjusted to your system)
innodb_buffer_pool_size = 14G
# number of CPU cores dedicated to the MySQL InnoDB backend
innodb_buffer_pool_instances = 16
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_per_table = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
# MyISAM
myisam_recover = backup,force
# Logging
log_warnings = 2
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 20
# Binary Log / Replication
server_id = 1
log-bin = mysql-bin
binlog_cache_size = 1M
sync_binlog = 8
binlog_format = row
expire_logs_days = 7
max_binlog_size = 128M
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqld_safe]
open-files-limit = 8192
log-error = /var/log/mysql/error.log
and cache-cache everything ;-)
Okay thanks alot, ill check it out
Re: Best my.cnf config for high traffic wordpress site
Posted: Wed May 27, 2015 6:13 am
by skurudo
vandread911 wrote:Okay thanks alot, ill check it out
Optimization it's complex process, not about just database. ;-)
Re: Best my.cnf config for high traffic wordpress site
Posted: Wed May 27, 2015 3:25 pm
by vandread911
skurudo wrote:vandread911 wrote:Okay thanks alot, ill check it out
Optimization it's complex process, not about just database. ;-)
It seems mysql wont start up after this my.cnf config has been applied,
heres my error log
Code: Select all
150527 06:39:08 mysqld_safe Starting mysqld daemon with databases from /var/lib$
150527 6:39:08 [Note] /usr/libexec/mysqld (mysqld 5.5.43) starting as process $
150527 6:39:08 [Note] Plugin 'FEDERATED' is disabled.
150527 6:39:08 InnoDB: The InnoDB memory heap is disabled
150527 6:39:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150527 6:39:08 InnoDB: Compressed tables use zlib 1.2.3
150527 6:39:08 InnoDB: Using Linux native AIO
150527 6:39:08 InnoDB: Initializing buffer pool, size = 128.0M
150527 6:39:08 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
150527 6:39:08 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
150527 6:39:08 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
150527 6:39:08 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
150527 6:39:08 InnoDB: Waiting for the background threads to start
150527 6:39:09 InnoDB: 5.5.43 started; log sequence number 0
150527 6:39:09 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150527 6:39:09 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150527 6:39:09 [Note] Server socket created on IP: '0.0.0.0'.
150527 6:39:09 [Note] Event Scheduler: Loaded 0 events
150527 6:39:09 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.43' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Commu$
Status information:
Current dir: /var/lib/mysql/
Running threads: 0 Stack size: 262144
Current locks:
lock: 0x72efc40433f0:
lock: 0x72efc400dda0:
lock: 0x1b4bd40:
lock: 0x1b14590:
lock: 0x1b09980:
lock: 0x1afcd80:
lock: 0x1aeba10:
lock: 0x1ae6500:
lock: 0x1ae1420:
lock: 0x1adaef0:
lock: 0x12fd800:
lock: 0xf9d780:
lock: 0xf9de40:
lock: 0xf9dd80:
lock: 0xf9dcc0:
lock: 0xf9d900:
lock: 0xf9d840:
lock: 0xf9d6c0:
Key caches:
default
Buffer_size: 8388608
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 4
not flushed: 0
w_requests: 33
writes: 17
r_requests: 30
reads: 4
handler status:
read_key: 9
read_next: 0
read_rnd 0
read_first: 5
write: 8432
delete 7
update: 1
Table status:
Opened tables: 35
Open tables: 28
Open files: 20
Open streams: 0
Alarm status:
Active alarms: 0
Max used alarms: 0
Memory status:
Non-mmapped space allocated from system: 9550640
Number of free chunks: 32
Number of fastbin blocks: 0
Number of mmapped regions: 14
Space in mmapped regions: 23969792
Maximum total allocated space: 0
Space available in freed fastbin blocks: 0
Total allocated space: 8894256
Total free space: 656384
Top-most, releasable space: 592000
Estimated memory (with thread stack): 33520432
Events status:
LLA = Last Locked At LUA = Last Unlocked At
WOC = Waiting On Condition DL = Data Locked
Event scheduler status:
State : INITIALIZED
Thread id : 0
LLA : n/a:0
LUA : n/a:0
WOC : NO
Workers : 0
Executed : 0
Data locked: NO
Event queue status:
Element count : 0
Data locked : NO
Attempting lock : NO
LLA : drop_schema_events:382
LUA : drop_schema_events:384
WOC : NO
Next activation : never
150527 14:04:56 [Warning] IP address '222.186.21.38' could not be resolved: Nam$
150527 17:13:44 [Note] /usr/libexec/mysqld: Normal shutdown
150527 17:13:44 [Note] Event Scheduler: Purging the queue. 0 events
150527 17:13:44 InnoDB: Starting shutdown...
150527 17:13:45 InnoDB: Shutdown completed; log sequence number 1683048
150527 17:13:45 [Note] /usr/libexec/mysqld: Shutdown complete
150527 17:13:45 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid end$
Re: Best my.cnf config for high traffic wordpress site
Posted: Wed May 27, 2015 3:58 pm
by skurudo
use old config yet and show please output:
df -i
df -h
Re: Best my.cnf config for high traffic wordpress site
Posted: Fri Jun 12, 2015 5:48 am
by vandread911
skurudo wrote:use old config yet and show please output:
df -i
df -h
It seems innoDB takes up to much resources (RAM, CPU), could you propose a new my.cnf thats suitable for myISAM
Re: Best my.cnf config for high traffic wordpress site
Posted: Fri Jun 12, 2015 7:17 am
by itsme
Hello,
I find the best way to take at first a good look at the wordpress plugins, do you really need them and do they suite your needs.
There is also a good plugin for wordpress which looks at all other plugins and sees which plugin can cause delay' in the site(s)
https://wordpress.org/plugins/p3-profiler/
If you want to tune mysql , download the following script
https://launchpad.net/mysql-tuning-prim ... -primer.sh , works great but mysql must be running for at least 2 days without restart, preferably longer.
And last tip is, run your site through
http://gtmetrix.com, it could be that your site is slow by itself.
Hope it helps