我有一套专用服务器设置（8 核、16 GB RAM），运行 Ubuntu 12.04 LTS 并进行了最新更新，但遇到了一些与 MySQL 相关的性能问题。我有多个活跃的虚拟主机。做了一些基本的性能调整。见下文。
问题 在一个虚拟主机上，当我在 WordPress 网站中导入大量数据时，运行 htop 时，我看到 CPU 峰值大幅上升至 100%。不过，这并没有分散到不同的核心上。我还注意到，在虚拟主机上浏览变得非常非常慢，我甚至会收到一些 503 错误，而机器上的其他虚拟主机运行良好。
问题 在运行脚本时，如何设法将有问题的虚拟主机（以及脚本运行的位置）的速度提高到可接受的水平？可以通过 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/
这是特定 vhost 的 php.ini，出于安全原因，我在本帖中更改了 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,$
答案1
首先，让这个查询仅在一个 CPU 上运行是正常的。
你的问题可能是数据库忙于INSERT将数据放入表中，而没有时间或 I/O 容量来处理SELECT显示你的网站的查询。
您可以尝试的一种方法是尽可能地分解导入数据并使用INSERT LOW PRIORITY或替代，INSERT DELAYED这样您的数据库就有机会在其间做其他工作（请参阅INSERT文档）。
答案2
我知道您没有要求 DB 相关的事情，但我还是必须提一下这一点。
您的表应该使用InnoDB作为引擎。问题MyISAM是它只有表级锁定。也就是说，如果您将数据插入到表中，则整个表都会被锁定，其他查询必须等待操作完成才能继续。
使用InnoDB，锁定是行级的。因此，其他查询可以访问不同的行而无需等待。
SHOW PROCESSLIST您可以在运行导入时查看查询结果，以检查锁定是否是问题所在。您应该会看到等待表访问的不同语句。