mysql - 对于0.5K/day的Wordpress网站的sql内存问题我该怎么办？

Question

我的 WordPress 网站每天有 500 名访问者。我之前使用过共享主机。但在托管公司系统管理员发出警告后将其转移。

我从digitalocean购买了512 SSD云。我尝试了 Vesta 几个月，但在迷恋之后我将其更改为 webmin。现在，每天我的服务器都会因为内存而崩溃。

我无法得到它。每天只有 0.5 千名访客。通常服务器使用%59内存和%0 CPU。但是mysql使用大量内存，我不知道这正常吗？mysql的内存使用量在285mb到350mb之间。当它看到更多时，服务器就会崩溃。这正常吗？

我在wp上安装了这个插件； WP 火箭，Yoast SEO。我使用 WP-DBManager 来优化数据库，但没有任何改变。

我只是想知道，对于使用该插件的 0.5k wp 网站来说，这正常吗？

对于这个问题我能做什么？我对我糟糕的英语感到非常抱歉。

System Details: 512 MB Memory / 20 GB Disk / - Ubuntu 12.04.5 x32

Webmin version  1.801

Virtualmin version  5.03

Kernel and CPU  Linux 3.13.0-85-generic on i686

Processor information   Intel(R) Xeon(R) CPU E5-2650L v3 @ 1.80GHz, 1 cores

CPU load averages   0.04 (1 min) 0.05 (5 mins) 0.05 (15 mins)

Real memory 483.42 MB total / 319.32 MB used

Virtual memory  0 bytes total / 0 bytes used

Local disk space    19.56 GB total / 14.97 GB free / 4.60 GB used

免费-m

             total       used       free     shared    buffers     cached
Mem:           495        387        107          0         12         58
-/+ buffers/cache:        316        178
Swap:            0          0          0

我的.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
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
lc-messages-dir = /usr/share/mysql
skip-external-locking


bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet = 1M
myisam-recover         = BACKUP
query_cache_limit       = 1M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_file_per_table = 1
read_rnd_buffer_size = 256K
net_buffer_length = 2K
read_buffer_size = 256K
sort_buffer_size = 64K
table_open_cache = 4
key_buffer_size = 16K
thread_stack = 128K

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M
sort_buffer_size = 8M
key_buffer_size = 8M


!includedir /etc/mysql/conf.d/

Webmin mysql系统变量

auto_increment_increment    1

auto_increment_offset   1
    autocommit  ON

automatic_sp_privileges ON

back_log    50
    basedir /usr

big_tables  OFF

binlog_cache_size   32768

binlog_direct_non_transactional_updates OFF

binlog_format   STATEMENT

binlog_stmt_cache_size  32768

bulk_insert_buffer_size 8388608

character_set_client    latin1

character_set_connection    latin1

character_set_database  latin1

character_set_filesystem    binary

character_set_results   latin1

character_set_server    latin1

character_set_system    utf8

character_sets_dir  /usr/share/mysql/charsets/

collation_connection    latin1_swedish_ci

collation_database  latin1_swedish_ci

collation_server    latin1_swedish_ci

completion_type NO_CHAIN

concurrent_insert   AUTO

connect_timeout 10
    datadir /var/lib/mysql/

date_format %Y-%m-%d

datetime_format %Y-%m-%d %H:%i:%s

default_storage_engine  InnoDB

default_week_format 0

delay_key_write ON

delayed_insert_limit    100

delayed_insert_timeout  300

delayed_queue_size  1000

div_precision_increment 4

engine_condition_pushdown   ON

error_count 0

event_scheduler OFF

expire_logs_days    10

external_user   
    flush   OFF

flush_time  0

foreign_key_checks  ON

ft_boolean_syntax   + -><()~*:""&|

ft_max_word_len 84

ft_min_word_len 4

ft_query_expansion_limit    20

ft_stopword_file    (built-in)

general_log OFF

general_log_file    /var/lib/mysql/ns1.log

group_concat_max_len    1024

have_compress   YES

have_crypt  YES

have_csv    YES

have_dynamic_loading    YES

have_geometry   YES

have_innodb YES

have_ndbcluster NO

have_openssl    DISABLED

have_partitioning   YES

have_profiling  YES

have_query_cache    YES

have_rtree_keys YES

have_ssl    DISABLED

have_symlink    YES

hostname    myhostname

identity    0

ignore_builtin_innodb   OFF

init_connect    

init_file   

init_slave  

innodb_adaptive_flushing    ON

innodb_adaptive_hash_index  ON

innodb_additional_mem_pool_size 8388608

innodb_autoextend_increment 8

innodb_autoinc_lock_mode    1

innodb_buffer_pool_instances    1

innodb_buffer_pool_size 134217728

innodb_change_buffering all

innodb_checksums    ON

innodb_commit_concurrency   0

innodb_concurrency_tickets  500

innodb_data_file_path   ibdata1:10M:autoextend

innodb_data_home_dir    

innodb_doublewrite  ON

innodb_fast_shutdown    1

innodb_file_format  Antelope

innodb_file_format_check    ON

innodb_file_format_max  Antelope

innodb_file_per_table   ON

innodb_flush_log_at_trx_commit  1

innodb_flush_method 

innodb_force_load_corrupted OFF

innodb_force_recovery   0

innodb_io_capacity  200

innodb_large_prefix OFF

innodb_lock_wait_timeout    50

innodb_locks_unsafe_for_binlog  OFF

innodb_log_buffer_size  8388608

innodb_log_file_size    5242880

innodb_log_files_in_group   2

innodb_log_group_home_dir   ./

innodb_max_dirty_pages_pct  75

innodb_max_purge_lag    0

innodb_mirrored_log_groups  1

innodb_old_blocks_pct   37

innodb_old_blocks_time  0

innodb_open_files   300

innodb_print_all_deadlocks  OFF

innodb_purge_batch_size 20

innodb_purge_threads    0

innodb_random_read_ahead    OFF

innodb_read_ahead_threshold 56

innodb_read_io_threads  4

innodb_replication_delay    0

innodb_rollback_on_timeout  OFF

innodb_rollback_segments    128

innodb_spin_wait_delay  6

innodb_stats_method nulls_equal

innodb_stats_on_metadata    ON

innodb_stats_sample_pages   8

innodb_strict_mode  OFF

innodb_support_xa   ON

innodb_sync_spin_loops  30

innodb_table_locks  ON

innodb_thread_concurrency   0

innodb_thread_sleep_delay   10000

innodb_use_native_aio   OFF

innodb_use_sys_malloc   ON

innodb_version  5.5.49

innodb_write_io_threads 4

insert_id   0

interactive_timeout 28800

join_buffer_size    131072

keep_files_on_create    OFF

key_buffer_size 16384

key_cache_age_threshold 300

key_cache_block_size    1024

key_cache_division_limit    100

large_files_support ON

large_page_size 0

large_pages OFF

last_insert_id  0

lc_messages en_US

lc_messages_dir /usr/share/mysql/

lc_time_names   en_US

license GPL

local_infile    ON

lock_wait_timeout   31536000

locked_in_memory    OFF
    log OFF

log_bin OFF

log_bin_trust_function_creators OFF

log_error   /var/log/mysql/error.log

log_output  FILE

log_queries_not_using_indexes   OFF

log_slave_updates   OFF

log_slow_queries    OFF

log_warnings    1

long_query_time 10.000000

low_priority_updates    OFF

lower_case_file_system  OFF

lower_case_table_names  0

max_allowed_packet  1048576

max_binlog_cache_size   18446744073709547520

max_binlog_size 104857600

max_binlog_stmt_cache_size  18446744073709547520

max_connect_errors  10

max_connections 151

max_delayed_threads 20

max_error_count 64

max_heap_table_size 16777216

max_insert_delayed_threads  20

max_join_size   18446744073709551615

max_length_for_sort_data    1024

max_long_data_size  1048576

max_prepared_stmt_count 16382

max_relay_log_size  0

max_seeks_for_key   4294967295

max_sort_length 1024

max_sp_recursion_depth  0

max_tmp_tables  32

max_user_connections    0

max_write_lock_count    4294967295

metadata_locks_cache_size   1024

min_examined_row_limit  0

multi_range_count   256

myisam_data_pointer_size    6

myisam_max_sort_file_size   2146435072

myisam_mmap_size    4294967295

myisam_recover_options  BACKUP

myisam_repair_threads   1

myisam_sort_buffer_size 8388608

myisam_stats_method nulls_unequal

myisam_use_mmap OFF

net_buffer_length   2048

net_read_timeout    30

net_retry_count 10

net_write_timeout   60
    new OFF
    old OFF

old_alter_table OFF

old_passwords   OFF

open_files_limit    1024

optimizer_prune_level   1

optimizer_search_depth  62

optimizer_switch    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

performance_schema  OFF

performance_schema_events_waits_history_long_size   10000

performance_schema_events_waits_history_size    10

performance_schema_max_cond_classes 80

performance_schema_max_cond_instances   1000

performance_schema_max_file_classes 50

performance_schema_max_file_handles 32768

performance_schema_max_file_instances   10000

performance_schema_max_mutex_classes    200

performance_schema_max_mutex_instances  1000000

performance_schema_max_rwlock_classes   30

performance_schema_max_rwlock_instances 1000000

performance_schema_max_table_handles    100000

performance_schema_max_table_instances  50000

performance_schema_max_thread_classes   50

performance_schema_max_thread_instances 1000

pid_file    /var/run/mysqld/mysqld.pid

plugin_dir  /usr/lib/mysql/plugin/
    port    3306

preload_buffer_size 32768

profiling   OFF

profiling_history_size  15

protocol_version    10

proxy_user  

pseudo_slave_mode   OFF

pseudo_thread_id    579

query_alloc_block_size  8192

query_cache_limit   1048576

query_cache_min_res_unit    4096

query_cache_size    0

query_cache_type    ON

query_cache_wlock_invalidate    OFF

query_prealloc_size 8192

rand_seed1  0

rand_seed2  0

range_alloc_block_size  4096

read_buffer_size    262144

read_only   OFF

read_rnd_buffer_size    262144

relay_log   

relay_log_index 

relay_log_info_file relay-log.info

relay_log_purge ON

relay_log_recovery  OFF

relay_log_space_limit   0

report_host 

report_password 

report_port 3306

report_user 

rpl_recovery_rank   0

secure_auth OFF

secure_file_priv    

server_id   0

skip_external_locking   ON

skip_name_resolve   OFF

skip_networking OFF

skip_show_database  OFF

slave_compressed_protocol   OFF

slave_exec_mode STRICT

slave_load_tmpdir   /tmp

slave_max_allowed_packet    1073741824

slave_net_timeout   3600

slave_skip_errors   OFF

slave_transaction_retries   10

slave_type_conversions  

slow_launch_time    2

slow_query_log  OFF

slow_query_log_file /var/lib/mysql/ns1-slow.log
    socket  /var/run/mysqld/mysqld.sock

sort_buffer_size    65536

sql_auto_is_null    OFF

sql_big_selects ON

sql_big_tables  OFF

sql_buffer_result   OFF

sql_log_bin ON

sql_log_off OFF

sql_low_priority_updates    OFF

sql_max_join_size   18446744073709551615

sql_mode    

sql_notes   ON

sql_quote_show_create   ON

sql_safe_updates    OFF

sql_select_limit    18446744073709551615

sql_slave_skip_counter  0

sql_warnings    OFF

ssl_ca  

ssl_capath  

ssl_cert    

ssl_cipher  

ssl_key 

storage_engine  InnoDB

stored_program_cache    256

sync_binlog 0

sync_frm    ON

sync_master_info    0

sync_relay_log  0

sync_relay_log_info 0

system_time_zone    EEST

table_definition_cache  400

table_open_cache    4

thread_cache_size   0

thread_concurrency  10

thread_handling one-thread-per-connection

thread_stack    131072

time_format %H:%i:%s

time_zone   SYSTEM

timed_mutexes   OFF

timestamp   1465490671

tmp_table_size  16777216
    tmpdir  /tmp

transaction_alloc_block_size    8192

transaction_prealloc_size   4096

tx_isolation    REPEATABLE-READ

unique_checks   ON

updatable_views_with_limit  YES

version 5.5.49-0ubuntu0.12.04.1

version_comment (Ubuntu)

version_compile_machine i686

version_compile_os  debian-linux-gnu

wait_timeout    28800

warning_count   0

正在运行的进程

1055    mysql   287.15 MB   /usr/sbin/mysqld
1789    ajnasite 95.90 MB   /usr/bin/php5-cgi
1860    ajnasite 95.52 MB   /usr/bin/php5-cgi
1734    ajnasite 88.77 MB   /usr/bin/php5-cgi
1032    bind    50.98 MB    /usr/sbin/named -u bind
5631    www-data    47.72 MB    /usr/sbin/apache2 -k start
1874    www-data    47.71 MB    /usr/sbin/apache2 -k start
1864    www-data    47.71 MB    /usr/sbin/apache2 -k start
5642    www-data    47.69 MB    /usr/sbin/apache2 -k start
5650    www-data    47.67 MB    /usr/sbin/apache2 -k start
7035    www-data    47.66 MB    /usr/sbin/apache2 -k start
1733    www-data    47.65 MB    /usr/sbin/apache2 -k start
7650    www-data    47.64 MB    /usr/sbin/apache2 -k start
8296    www-data    47.62 MB    /usr/sbin/apache2 -k start
8107    www-data    47.61 MB    /usr/sbin/apache2 -k start
1688    root    46.89 MB    /usr/sbin/apache2 -k start
8451    root    46.73 MB    /usr/share/webmin/proc/index_size.cgi
508 syslog  29.45 MB    rsyslogd -c5
1693    www-data    25.31 MB    /usr/sbin/apache2 -k start
1036    whoopsie    23.90 MB    whoopsie
1199    clamav  17.69 MB    /usr/bin/freshclam -d --quiet
1736    root    16.86 MB    /usr/bin/perl /usr/share/webmin/miniserv.pl /etc/webmin/miniserv.conf
8444    root    16.86 MB    /usr/bin/perl /usr/share/webmin/miniserv.pl /etc/webmin/miniserv.conf
1626    root    15.21 MB    /usr/bin/perl /usr/share/usermin/miniserv.pl /etc/usermin/miniserv.conf
1339    root    12.02 MB    /usr/sbin/saslauthd -a pam -m /var/spool/postfix/var/run/saslauthd -r -n 5
1340    root    12.02 MB    /usr/sbin/saslauthd -a pam -m /var/spool/postfix/var/run/saslauthd -r -n 5
1341    root    12.02 MB    /usr/sbin/saslauthd -a pam -m /var/spool/postfix/var/run/saslauthd -r -n 5
1342    root    12.02 MB    /usr/sbin/saslauthd -a pam -m /var/spool/postfix/var/run/saslauthd -r -n 5
1343    root    12.02 MB    /usr/sbin/saslauthd -a pam -m /var/spool/postfix/var/run/saslauthd -r -n 5
7448    root    10.65 MB    -bash
7252    root    9.46 MB sshd: root@pts/0
1644    proftpd 9.27 MB proftpd: (accepting connections)
8517    root    7.84 MB sshd: [accepted]
8518    sshd    7.84 MB sshd: [net]
8290    postfix 7.80 MB smtpd -n smtp -t inet -u -c -o stress= -s 2 -o smtpd_sasl_auth_enable=yes
1890    postfix 7.02 MB tlsmgr -l -t unix -u -c
5890    postfix 6.97 MB smtp -t unix -u -c
818 root    6.52 MB /usr/sbin/sshd -D
5889    postfix 4.93 MB cleanup -z -t unix -u -c
7661    root    4.79 MB nano my.cnf
7695    root    4.79 MB nano my.cnf
8165    root    4.79 MB nano my.cnf
6020    postfix 4.67 MB trivial-rewrite -n rewrite -t unix -u -c
1312    postfix 4.53 MB qmgr -l -t fifo -u
973 root    4.52 MB /sbin/getty -8 38400 tty4
980 root    4.52 MB /sbin/getty -8 38400 tty5
987 root    4.52 MB /sbin/getty -8 38400 tty2
988 root    4.52 MB /sbin/getty -8 38400 tty3
996 root    4.52 MB /sbin/getty -8 38400 tty6
1741    root    4.52 MB /sbin/getty -8 38400 tty1
5892    postfix 4.51 MB bounce -z -t unix -u -c
5901    postfix 4.48 MB pickup -l -t fifo -u -c
8292    postfix 4.48 MB anvil -l -t unix -u -c
1304    root    4.46 MB /usr/lib/postfix/master
1   root    3.45 MB /sbin/init
497 messagebus  3.18 MB dbus-daemon --system --fork --activation=upstart
1015    root    2.93 MB /usr/sbin/dovecot -F -c /etc/dovecot/dovecot.conf
493 root    2.90 MB /sbin/udevd --daemon
572 root    2.89 MB /sbin/udevd --daemon
573 root    2.89 MB /sbin/udevd --daemon
8522    root    2.79 MB ps --cols 2048 -eo user:80,ruser:80,group:80,rgroup:80,pid,ppid,pgid,pcpu,vsz,ni ...
850 root    2.78 MB upstart-socket-bridge --daemon
470 root    2.77 MB upstart-udev-bridge --daemon
1042    dovecot 2.66 MB dovecot/anvil
1043    root    2.65 MB dovecot/log
1018    root    2.55 MB cron
1691    root    2.50 MB /usr/sbin/htcacheclean -d120 -i -p/var/cache/apache2/mod_disk_cache -l300M
1019    daemon  2.41 MB atd
8521    root    2.18 MB sh -c ps --cols 2048 -eo user:80,ruser:80,group:80,rgroup:80,pid,ppid,pgid,pcpu, ...
1017    root    2.13 MB acpid -c /etc/acpi/events -s /var/run/acpid.socket
2   root    0 kB    [kthreadd]
3   root    0 kB    [ksoftirqd/0]
5   root    0 kB    [kworker/0:0H]
6   root    0 kB    [kworker/u2:0]
7   root    0 kB    [rcu_sched]
8   root    0 kB    [rcu_bh]
9   root    0 kB    [migration/0]
10  root    0 kB    [watchdog/0]
11  root    0 kB    [khelper]
12  root    0 kB    [kdevtmpfs]
13  root    0 kB    [netns]
14  root    0 kB    [writeback]
15  root    0 kB    [kintegrityd]
16  root    0 kB    [bioset]
17  root    0 kB    [kworker/u3:0]
18  root    0 kB    [kblockd]
19  root    0 kB    [ata_sff]
20  root    0 kB    [khubd]
21  root    0 kB    [md]
22  root    0 kB    [devfreq_wq]
23  root    0 kB    [kworker/0:1]
25  root    0 kB    [khungtaskd]
26  root    0 kB    [kswapd0]
27  root    0 kB    [vmstat]
28  root    0 kB    [ksmd]
29  root    0 kB    [fsnotify_mark]
30  root    0 kB    [ecryptfs-kthrea]
31  root    0 kB    [crypto]
43  root    0 kB    [kthrotld]
45  root    0 kB    [vballoon]
46  root    0 kB    [scsi_eh_0]
47  root    0 kB    [scsi_eh_1]
67  root    0 kB    [deferwq]
68  root    0 kB    [charger_manager]
211 root    0 kB    [jbd2/vda1-8]
212 root    0 kB    [ext4-rsv-conver]
222 root    0 kB    [scsi_eh_2]
679 root    0 kB    [kpsmoused]
691 root    0 kB    [ttm_swap]
703 root    0 kB    [kworker/0:2]
858 root    0 kB    [kvm-irqfd-clean]
1821    root    0 kB    [kworker/u3:1]
7944    root    0 kB    [kworker/u2:2]

我也使用 mysqlcheck -uroot -p 优化了数据库

Answer 1

问题是缺少RAM，因为我缺少交换分区。对于有同样问题的人，我使用这篇文章添加了交换: https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-12-04