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

Тема в разделе "Администрирование серверов", создана пользователем Grunuk, 16 июл 2013.

Модераторы: mefish, stooper
  1. Grunuk

    Grunuk

    Регистр.:
    19 янв 2008
    Сообщения:
    188
    Симпатии:
    6
    Здравствуйте, уже вторую неделю подряд все 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/
    
     
  2. Yuriy_Z

    Yuriy_Z Доработка+ OpenCart

    Регистр.:
    22 июл 2012
    Сообщения:
    159
    Симпатии:
    91
    Включи логирования медленных запросов, они у тебя отключены
    Посмотри какой скрипт нагружает мускуля и сделай вывод стоит его держать или нет
    да и конфигурация у тебя скупей дефолтной я б сказал)

    В каталоге /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
     
    latteo нравится это.
  3. Grunuk

    Grunuk

    Регистр.:
    19 янв 2008
    Сообщения:
    188
    Симпатии:
    6
    Включил:
    #log_slow_queries = /var/log/mysql/mysql-slow.log

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

    Да, в /usr/share/doc/mysql-server-5.1/есть папка examples
    там есть такие файлы:
    [​IMG]my-small.cnf
    [​IMG]my-huge.cnf.gz
    [​IMG]my-innodb-heavy-4G.cnf.gz
    [​IMG]my-large.cnf.gz
    [​IMG]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
    


    и содержимое файла my-huge:
    Код:
    #
    # 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


    Также сделал проверку MySQLTuner
    вот картина:
    Код:
    >>  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)
    


    Также получил первые результаты в лог файле mysql-slow.log
    Код:
    # 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: пользуйтесь редактированием сообщений и не забывайте "простыни" под спойлер убирать. в следующий раз выпишу "премию"
     
  4. Yuriy_Z

    Yuriy_Z Доработка+ OpenCart

    Регистр.:
    22 июл 2012
    Сообщения:
    159
    Симпатии:
    91
    В блоке [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 попробуй установить

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

    Grunuk

    Регистр.:
    19 янв 2008
    Сообщения:
    188
    Симпатии:
    6
    Спасибо Юрий, пробую, еще вопрос, как поставить индексы на медленные запросы?

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

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

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

    Yuriy_Z Доработка+ OpenCart

    Регистр.:
    22 июл 2012
    Сообщения:
    159
    Симпатии:
    91
    Запросом
    Код:
    ALTER TABLE имя_таблицы ADD PRIMARY KEY (список_столбцов);
    ALTER TABLE имя_таблицы ADD UNIQUE имя_индекса (список_столбцов);
    ALTER TABLE имя_таблицы ADD INDEX имя_индекса (список_столбцов);
    ALTER TABLE имя_таблицы ADD FULLTEXT имя_индекса (список_столбцов);
    Тебе скорей всего надо
    ALTER TABLE имя_таблицы ADD INDEX имя_индекса (список_столбцов);
     
  7. neodev

    neodev Создатель

    Регистр.:
    26 мар 2012
    Сообщения:
    24
    Симпатии:
    4