Проблемы с производительностью MySQL на определенных хостах

У меня есть выделенный сервер (8 ядер, 16 ГБ ОЗУ), работающий под управлением Ubuntu 12.04 LTS с последними обновлениями, и я испытываю некоторые проблемы с производительностью, связанные с MySQL. У меня несколько активных хостов. Сделал базовую настройку производительности. Увидеть ниже.

Проблема На одном виртуальном хосте, когда я импортирую много данных на свой сайт WordPress, я вижу огромный скачок производительности процессора, достигающий 100% при запуске htop. Однако это не разделено на разные ядра. Я также замечаю, что просмотр на vhost становится очень-очень медленным, я даже получаю некоторые ошибки 503, в то время как другие мои vhosts на машине работают нормально.

Вопрос Как я могу получить скорость виртуального хоста с проблемами (и где скрипт запущен) до приемлемого уровня при запуске скрипта? Можно ли решить эту проблему с помощью настройки PHP.ini? Обратите внимание, я не ищу решение для настройки запроса, поэтому не задавайте вопросов о медленных журналах запросов и т. Д., Пожалуйста.

Дополнительная информация

#
# 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/

А вот php.ini конкретного vhost, я изменил open_basedir в этом посте по соображениям безопасности:

; 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

Пример одного из «медленных» запросов

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,$