CPU 100% - жрет /usr/sbin/mysqld

Grunuk · 16 Июл 2013

Здравствуйте, уже вторую неделю подряд все 100% процессора съедает mysqld,
по htop видно, а также по top -c
Знающие люди, помогите пожалуйста с настройками сервера, вот мой файл /etc/mysql/my.cnf:

Код:

[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
default-character-set = utf8
 
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0
 
[mysqld]
user        = mysql
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
skip-external-locking
key_buffer        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size      = 8
myisam-recover        = BACKUP
 
#max_connections        = 100
#table_cache            = 64
#thread_concurrency    = 10
 
query_cache_limit    = 1M
query_cache_size        = 8M
 
#general_log_file        = /var/log/mysql/mysql.log
#general_log            = 1
 
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
 
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size        = 100M
default-character-set = utf8
 
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
 
# 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    = 16M
default-character-set = utf8
 
[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition
 
default-character-set = utf8
[isamchk]
key_buffer        = 16M
 
!includedir /etc/mysql/conf.d/

Yuriy_Z · 16 Июл 2013

Включи логирования медленных запросов, они у тебя отключены
Посмотри какой скрипт нагружает мускуля и сделай вывод стоит его держать или нет
да и конфигурация у тебя скупей дефолтной я б сказал)

В каталоге /usr/share/doc/mysql-server-x.x.xx/ лежат следующие файлы(где x.x.xx - версия MySQL

my-small.cnf — для систем с малым обьемом памяти (<=64Mb), в которых MySQL используется редко.
my-medium.cnf — если памяти мало (32-64Mb) или MySQL используется совместно с другими приложениями (например Apache) и памяти около 128Mb.
my-large.cnf, my-huge.cnf — для систем с большим обьемом памяти (512Mb, 1-2Gb), где MySQL играет главную роль.
my-innodb-heavy-4G.cnf — 4Gb памяти, InnoDB, MySQL играет главную роль.

рекомендую тебе сменить файл конфигурации на my-huge.cnf
И в конец концов используй MySQLTuner

Grunuk · 16 Июл 2013

Включил:
#log_slow_queries = /var/log/mysql/mysql-slow.log

В каком смысле скупей дефолтной Юрий?

Да, в /usr/share/doc/mysql-server-5.1/есть папка examples
там есть такие файлы:

my-small.cnf

my-huge.cnf.gz

my-innodb-heavy-4G.cnf.gz

my-large.cnf.gz

my-medium.cnf.gz
Переименовать my-huge.cnf.gz в my-huge.cnf не получилось, вставил содержимое файла my-huge в my-small, сохранил и перезагрузил mysql
Файл my-small:

Код:

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
 
# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
 
# Here follows entries for some specific programs
 
# The MySQL server
[mysqld]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
 
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
server-id    = 1
 
# Uncomment the following if you want to log updates
#log-bin=mysql-bin
 
# binary logging format - mixed recommended
#binlog_format=mixed
 
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
 
[mysqlhotcopy]
interactive-timeout

[/spoil]

и содержимое файла my-huge:
[spoil]

Код:

#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user    =  <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =  <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port    =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
#
# binary logging format - mixed recommended
#binlog_format=mixed
 
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

[/spoil]

Также сделал проверку MySQLTuner
вот картина:
[spoil]

Код:

>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-0ubuntu0.10.04.1-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 92M (Tables: 157)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 6
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1m 32s (16K q [182.728 qps], 216 conn, TX: 25M, RX: 1M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 50.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 455.8M (91% of installed RAM)
[OK] Slow queries: 0% (2/16K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/30.6M
[OK] Key buffer hit rate: 99.7% (1M cached / 4K reads)
[OK] Query cache efficiency: 59.7% (8K cached / 14K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (4 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 20% (96 on disk / 468 total)
[OK] Thread cache hit rate: 94% (12 created / 216 connections)
[!!] Table cache hit rate: 5% (64 open / 1K opened)
[OK] Open file limit used: 11% (115/1K)
[OK] Table locks acquired immediately: 99% (5K immediate / 5K locks)
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    table_cache (> 64)

[/spoil]

Также получил первые результаты в лог файле mysql-slow.log
[spoil]

Код:

# Time: 130716 10:16:00
# User@Host: magazun[magazun] @ localhost []
# Query_time: 11.393991  Lock_time: 0.000132 Rows_sent: 30  Rows_examined: 139827
use magazun;
SET timestamp=1373958960;
SELECT t_tag.*, COUNT(t_tagcon.tag_id) as `weight` FROM SC_tags t_tag LEFT JOIN SC_tagged_objects t_tagcon ON t_tag.id=t_tagcon.tag_id
                WHERE 1
                AND t_tagcon.object_type='product'
                AND t_tagcon.language_id=1
               
                GROUP BY t_tagcon.tag_id
                HAVING weight>0
                ORDER BY weight DESC LIMIT 0,30;
# User@Host: magazun[magazun] @ localhost []
# Query_time: 13.069117  Lock_time: 0.000123 Rows_sent: 30  Rows_examined: 139827
SET timestamp=1373958960;
SELECT t_tag.*, COUNT(t_tagcon.tag_id) as `weight` FROM SC_tags t_tag LEFT JOIN SC_tagged_objects t_tagcon ON t_tag.id=t_tagcon.tag_id
                WHERE 1
                AND t_tagcon.object_type='product'
                AND t_tagcon.language_id=1
               
                GROUP BY t_tagcon.tag_id
                HAVING weight>0
                ORDER BY weight DESC LIMIT 0,30;

Я так понимаю что тегов на сайте очень много, и они долго подгружаются? Это если человек смотрит... а если поисковый бот, то тогда наверное максимальная нагрузка на мускул?
Ребята, подскажите по таким нюансам мелким...

Добавлено efs: пользуйтесь редактированием сообщений и не забывайте "простыни" под спойлер убирать. в следующий раз выпишу "премию"

Yuriy_Z · 16 Июл 2013

В блоке [mysqld] допиши

Код:

table_cache = 2000

Но ет ничего толком не даст, у тебя в блоке [mysqld] нет никаких настроек, из за етого мускуль по дефолту работает, так как етот блок "самый главный"
Попробуй такие параметры

Код:

key_buffer              = 64M
max_allowed_packet      = 12M
thread_stack            = 128K
thread_cache_size      = 64
max_connections        = 200
table_cache            = 2000
tmp_table_size          = 128M
max_heap_table_size    = 256M
join_buffer_size        = 1M
query_cache_limit      = 8M
query_cache_size        = 64M

ето все в блок [mysqld] вставь
Обязательно перезагрузить мускуль после внесения изменений
Потом еще query_cache_type = 1 попробуй установить

Но ет не выход, нужно знать параметры твоего сервера, сколько там ОЗУ, проц какой, да и смотреть на медленные запросы + роставь индексы в таблицах к которым есть медленные запросы

Grunuk · 16 Июл 2013

Спасибо Юрий, пробую, еще вопрос, как поставить индексы на медленные запросы?

Мой ВПС:
Ubuntu 12.04 LTS
512 Mb
1x2.6 Ghz
20 Gb

толковые медленные запросы наверно будут после 24 часов работы сервера...

Еще магазин очень слабый к кнопке F5... если зажать ф5 и подержать 10 сек., сразу 100% загрузка, и используется много оперативной памяти, сайт висит.... как вылечить?

Yuriy_Z · 16 Июл 2013

Запросом

Код:

ALTER TABLE имя_таблицы ADD PRIMARY KEY (список_столбцов);
ALTER TABLE имя_таблицы ADD UNIQUE имя_индекса (список_столбцов);
ALTER TABLE имя_таблицы ADD INDEX имя_индекса (список_столбцов);
ALTER TABLE имя_таблицы ADD FULLTEXT имя_индекса (список_столбцов);

Тебе скорей всего надо
ALTER TABLE имя_таблицы ADD INDEX имя_индекса (список_столбцов);

neodev · 7 Сен 2013

Попробуй настроить thread_cache по мануалу Для просмотра ссылки Войди или Зарегистрируйся

CPU 100% - жрет /usr/sbin/mysqld

Grunuk

Участник

Yuriy_Z

Доработка+ OpenCart

Grunuk

Участник

Yuriy_Z

Доработка+ OpenCart

Grunuk

Участник

Yuriy_Z

Доработка+ OpenCart

neodev

Создатель