0

I have a dedicated server setup (8 cores, 16 GB of RAM), running Ubuntu 12.04 LTS with latest updates, and I am experiencing some performance problems related to MySQL. I have multiple vhosts active. Did some basic performance tuning. See below.

Problem On one vhost, when I import a lot of data in my WordPress site, I see a huge CPU spike going to 100% when running htop. This is not split over the different cores though. I also notice that browsing on the vhost becomes very very slow, I even get some 503 errors, while my other vhosts on the machine are running just fine.

Question How can I manage to get the speed of the vhost with the problems (and where the script is running on) to an acceptable level while running the script? Could this be resolved with PHP.ini tuning? Please note I am not looking for a solution to adjust the query, so no questions about slow query logs etc, please.

Additional info

#
# The MySQL database server configuration file.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
local-infile=0
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
#tmpdir     = /tmp
tmpdir          = /run/mysqld
lc-messages-dir = /usr/share/mysql
skip-external-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address      = 127.0.0.1
#
# * Fine Tuning
#
sort_buffer_size    = 8M
read_buffer_size    = 8M
read_rnd_buffer_size    = 8M
join_buffer_size    = 24M
table_cache = 128K
table_definition_cache = 128K
open_files_limit = 32K

# originals
key_buffer      = 384M
max_allowed_packet  = 128M
thread_stack        = 256K
thread_cache_size = 8K
#low_priority_updates=1
#concurrent_insert=ALWAYS

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 150
thread_concurrency     = 16

#
# * Query Cache Configuration
#
query_cache_limit   = 2M
query_cache_size        = 128M
max_heap_table_size = 4096M
tmp_table_size      = 4096M
query_cache_min_res_unit = 512
query_cache_type = 1

#InnoDB Tuning
innodb_buffer_pool_size = 1024M
innodb_thread_concurrency = 16

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem


[mysqldump]
quick
quote-names
max_allowed_packet  = 32M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 512M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

And here is the php.ini of the particular vhost, I changed the open_basedir in this post for security reasons:

; ATTENTION!
; 
; DO NOT MODIFY THIS FILE BECAUSE IT WAS GENERATED AUTOMATICALLY,
; SO ALL YOUR CHANGES WILL BE LOST THE NEXT TIME THE FILE IS GENERATED.

[PHP]
soap.wsdl_cache_limit = 5
tidy.clean_output = Off
include_path = ".:"
mysql.allow_persistent = On
mysqli.max_persistent = -1
safe_mode_allowed_env_vars = PHP_
session.bug_compat_42 = Off
mysql.connect_timeout = 60
session.use_only_cookies = 1
register_argc_argv = Off
mssql.min_error_severity = 10
open_basedir = "/var/www/vhosts/domainname-scrambled-for-security-reasons/:/tmp/"
session.name = PHPSESSID
mysqlnd.collect_statistics = On
session.hash_function = 0
magic_quotes_runtime = Off
log_errors_max_len = 1024
mssql.secure_connection = Off
pgsql.max_links = -1
variables_order = "GPCS"
ldap.max_links = -1
sybct.allow_persistent = On
max_input_time = 1200
odbc.max_links = -1
mysqli.default_pw = 
session.save_handler = files
mysqli.cache_size = 2000
pgsql.auto_reset_persistent = Off
session.cache_expire = 180
error_reporting = E_ALL & ~E_DEPRECATED
safe_mode_gid = Off
auto_prepend_file = 
sybct.min_client_severity = 10
pgsql.max_persistent = -1
auto_globals_jit = On
soap.wsdl_cache_ttl = 86400
allow_url_fopen = On
zend.enable_gc = On
smtp_port = 25
register_globals = Off
display_startup_errors = Off
user_dir = 
session.cookie_lifetime = 0
mysqli.max_links = -1
default_socket_timeout = 60
session.serialize_handler = php
session.hash_bits_per_character = 5
unserialize_callback_func = 
pdo_mysql.cache_size = 2000
default_mimetype = "text/html"
magic_quotes_gpc = Off
safe_mode_include_dir = 
max_execution_time = 1200
mail.add_x_header = On
upload_max_filesize = 256M
ibase.max_links = -1
register_long_arrays = Off
safe_mode = off
zlib.output_compression = Off
safe_mode_protected_env_vars = LD_LIBRARY_PATH
odbc.max_persistent = -1
mssql.compatability_mode = Off
file_uploads = on
ibase.max_persistent = -1
mysqli.reconnect = Off
session.cookie_domain = 
mssql.allow_persistent = On
mysql.max_persistent = -1
mssql.max_links = -1
session.use_trans_sid = 0
mysql.default_socket = 
mysql.max_links = -1
odbc.defaultbinmode = 1
sybct.max_persistent = -1
output_buffering = 4096
ibase.timeformat = "%H:%M:%S"
doc_root = 
log_errors = On
mysql.default_host = 
mysqli.default_port = 3306
display_errors = Off
mysqli.default_socket = 
safe_mode_exec_dir = 
html_errors = Off
session.entropy_length = 0
ibase.allow_persistent = 1
y2k_compliance = On
mysql.allow_local_infile = On
post_max_size = 256M
asp_tags = Off
memory_limit = 4096M
short_open_tag = on
SMTP = localhost
precision = 14
session.bug_compat_warn = Off
sybct.min_server_severity = 10
mssql.min_message_severity = 10
session.gc_maxlifetime = 1440
session.gc_probability = 0
allow_url_include = Off
mysqli.default_host = 
mysqli.default_user = 
session.referer_check = 
pgsql.log_notice = 0
mysql.default_port = 
odbc.defaultlrl = 4096
pgsql.ignore_notice = 0
mysql.trace_mode = Off
ibase.timestampformat = "%Y-%m-%d %H:%M:%S"
engine = On
odbc.allow_persistent = On
ibase.dateformat = "%Y-%m-%d"
track_errors = Off
max_file_uploads = 20
pgsql.allow_persistent = On
session.auto_start = 0
auto_append_file = 
disable_classes = 
pdo_mysql.default_socket = 
mysql.default_password = 
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=fakeentry"
allow_call_time_pass_reference = Off
ignore_repeated_source = Off
mysqli.allow_persistent = On
sql.safe_mode = Off
session.cookie_path = /
expose_php = off
report_memleaks = On
session.gc_divisor = 1000
mssql.max_persistent = -1
serialize_precision = 17
odbc.check_persistent = On
sybct.max_links = -1
mysqlnd.collect_memory_statistics = Off
define_syslog_variables = Off
session.cookie_httponly = 
session.cache_limiter = nocache
enable_dl = Off
ignore_repeated_errors = Off
request_order = "GP"
disable_functions = pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,
magic_quotes_sybase = Off
soap.wsdl_cache_enabled = 1
soap.wsdl_cache_dir = "/tmp"
session.use_cookies = 1
mysql.default_user = 
mysql.cache_size = 4000
implicit_flush = Off
bcmath.scale = 0

Sample of one of the "slow" queries

SELECT COUNT( DISTINCT posts.ID ) FROM wp_posts as posts
LEFT JOIN wp_postmeta AS meta_visibility ON posts.ID = meta_visibility.post_id
LEFT JOIN wp_term_relationships AS rel ON posts.ID=rel.object_ID
LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
LEFT JOIN wp_terms AS term USING( term_id )
LEFT JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id
WHERE   post_status = 'publish'
AND     post_type       = 'product'
AND     meta_visibility.meta_key = '_visibility'
AND     meta_visibility.meta_value IN ( 'visible', 'catalog' )
AND term_id IN (80,121,122,189,236,291,292,293,403,678,957,975,1093,1170,1178,1603,2281,3338,3393,4100,4101,4102,4103,4124,663,666,677,1276,2572,680,689,718,665,674,715,1084,2594,$
3
  • I have managed to de-prioritise the MySQL PPID of the script in htop with the "nice" command. But this, ofcourse, was not the solution I was aiming for.
    – Mark
    Aug 9, 2014 at 10:22
  • you need to run tuning-primer.sh and what data you trying to import? how often and why you import?? how big is io-wait during import?? btw, your mysql config is evil, try to read this: dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
    – ADM
    Aug 9, 2014 at 18:32
  • what is "evil" about my config?
    – Mark
    Aug 10, 2014 at 9:22

2 Answers 2

0

First, having this query to run on only one CPU is normal.

Your problem is likely that the database will be occupied with INSERTing your data into the tables and doesn't have time or I/O capacity left to handle SELECT queries to display your site.

One approach you can try is to break down the import data as much as possible and use INSERT LOW PRIORITY or alternatively, INSERT DELAYED so your database has a chance to do other work in between (see INSERT documentation).

2
  • I am considering to move to a new server with SSD disks. Will this help? bi = 30, bo = 58, iowait = 1.2%
    – Mark
    Aug 10, 2014 at 12:42
  • I do see some high %util, varying from 25% - 53%.
    – Mark
    Aug 10, 2014 at 12:51
0

I know you didn't ask for DB related things, but I still have to mention this.

Your tables should use InnoDB as the engine. The problem with MyISAM is that it has only table-level locking. That is, if you insert data to the table, the whole table is locked, and other queries have to wait for the operation to finish before they can proceed.

With InnoDB, the locking is row-level. So, other queries can access different rows without waiting.

You can check if locking is the issue by looking at the results of SHOW PROCESSLIST query while you are running the import. You should see the different statements that are waiting for table access there.

6
  • I updated all tables to InnoDB, no luck, still same problems. SHOW PROCESSLIST shows : Sending data SELECT COUNT( DISTINCT posts.ID ) FROM wp_posts as...
    – Mark
    Aug 10, 2014 at 9:35
  • Why are you running a SELECT COUNT() query in an import script? You don't need the DISTINCT keyword with the ID column of the posts table, because that column is already unique. I recommend you to study your queries and optimize them, that will solve your problem. Aug 10, 2014 at 11:34
  • Could have all sort of reasons, anyways, I was not posting this to get comments on queries.
    – Mark
    Aug 10, 2014 at 12:40
  • Well, the primary cause for the slowdowns are the queries. Unfortunately there isn't much else you can do about it. Aug 10, 2014 at 20:09
  • Alright. Let's see what we can do. Since I don't understand anything about queries, maybe someone can help. I will edit the post and add the slow query.
    – Mark
    Aug 13, 2014 at 9:51

You must log in to answer this question.

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