Падает служба MYSQL - на мощьном сервере!

[team-vip]

Добрый день друзья не могу корректно настроить сервер Mysql.

Сервер свой:
CentOS 6 + VestaCP(стандартное) + yum update
Оперативка Memory: 32G
Hdd: 2Т
Сайтов на серваке: 300 шт. , с бд 50 шт., по 1000 - 6000 посещений в в месяц сайты! Максимально в день по 1500 на одном сайте!

Свободно весит 21 Гб
mysql процесс загружен поднимается до 4-8 Гб и потом падает часто!

Вот мой конфиг:
my.cfg

Code: Select all

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=70
max_user_connections=30
wait_timeout=10
interactive_timeout=50
slow_query_log = 0
long_query_time= 5
log-queries-not-using-indexes
slow_query_log_file=/var/log/mysql/log-slow-queries.log

key_buffer_size = 8M
myisam_sort_buffer_size = 4M
join_buffer_size = 512K
read_buffer_size = 512K
sort_buffer_size = 1M
table_cache = 2048
thread_cache_size = 128
max_allowed_packet = 16M
query_cache_limit = 2M
query_cache_size = 8M
tmp_table_size = 8M

#innodb_use_native_aio = 0
innodb_file_per_table
innodb_buffer_pool_size = 8M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Ребят помогите предложите пожалуйста готовые варианты как настроить правильно чтобы нагрузки распределялись mysqld не падал!!! Нагрузки распределялись. за ранее благодарен! Может какие другие нужны параметры я предоставлю!

[Nanotraktor]

mysql процесс загружен поднимается до 4-8 Гб и потом падает часто!

А с какой ошибкой падает? Что в логах?

[skurudo]

Для начала стоит посмотреть на ошибку.

[raven-kg]

Версию mysql-сервера и что в логах хотелось бы знать. Еще не помешало бы знать сколько данных в таблицах и в каких движках оно хранится. Телепатически определить неисправность как-то сложновато будет, но даже так, навскидку мне видится, что innodb_buffer_pool_size = 8M как-то маловато будет (если конечно у вас не все в MyISAM).

[team-vip]

httpd -v
Server version: Apache/2.2.15 (Unix)
Server built: Nov 15 2012 15:52:50

#php -v
PHP 5.4.45 (cli) (built: Mar 30 2016 11:33:29)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies

#mysql -v
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 69911
Server version: 5.5.48 MySQL Community Server (GPL) by Remi

[root@user etc]# ps ax | grep mysql
root 9756 0.0 0.0 108168 1612 pts/0 S Apr11 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 10215 25.0 1.2 3708668 419808 pts/0 Sl Apr11 311:27 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root 29041 0.0 0.0 103304 896 pts/0 S+ 20:31 0:00 grep mysql



С какой ошибкой падает так как нет прям падения! есть просто из за сильной нагрузке зависание бд чтоли! а потрой и ваше ложиться! как помсреть логи с фильтром через ssh ???

[Nanotraktor]

Так.
Я совсем запутался.
То вы говорите что mysql

падает часто!

то

нет прям падения!

Видимо, сначала необходимо окончательно определиться - в чем выражается проблема? Падает мускуль, с ним невозможно соединиться (какая-то ошибка?), соединение происходит, но запросы выполняются медленно/не выполняются с какой-то ошибкой?
Как себя в этот момент ведет mySQL? Не занята ли, например, вся память, или высокий показатель LA, или проблемы чтения с диска, или ещё что-то.

[raven-kg]

Попробуйте для начала проанализировать ваш sql сервер этой штукой http://mysqltuner.pl и весь вывод сюда (перед анализом лучше дать серверу поработать пару суток в штатном рабочем режиме без перезапусков mysql). Это даст больше данных чем все выше перечисленное.

[team-vip]

Попробуйте для начала проанализировать ваш sql сервер этой штукой http://mysqltuner.pl и весь вывод сюда (перед анализом лучше дать серверу поработать пару суток в штатном рабочем режиме без перезапусков mysql). Это даст больше данных чем все выше перечисленное.

Code: Select all

[root@magiccolor tmp]# perl mysqltuner.pl
String found where operator expected at mysqltuner.pl line 3096, near
        "get_wsrep_option 'gcache.mem_size'" (#1)
    (S syntax) The Perl lexer knows whether to expect a term or an operator.
    If it sees what it knows to be a term when it was expecting to see an
    operator, it gives you this warning.  Usually it indicates that an
    operator or delimiter was omitted, such as a semicolon.

        (Do you need to predeclare get_wsrep_option?)
Use of implicit usermagic to @_ is deprecated at mysqltuner.pl line 3179 (#2)
    (D deprecated, W syntax) It makes a lot of work for the compiler when you
    clobber a subroutine's argument list, so it's better if you assign the results
    of a usermagic() explicitly to an array (or list).

 >>  MySQLTuner 1.6.10 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.48
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 271M (Tables: 6261)
[--] Data in InnoDB tables: 40M (Tables: 708)
[--] Data in MEMORY tables: 0B (Tables: 14)
[!!] Total fragmented tables: 146

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'color_1@%' hasn't specific host restriction.
[!!] User 'color_2@%' hasn't specific host restriction.
[!!] User 'color_3@%' hasn't specific host restriction.
[!!] User 'color_4@%' hasn't specific host restriction.
[!!] User 'color_5@%' hasn't specific host restriction.
[!!] User 'color_6@%' hasn't specific host restriction.
[!!] User 'color_7@%' hasn't specific host restriction.
[!!] User 'color_8@%' hasn't specific host restriction.
[!!] User 'color_9%' hasn't specific host restriction.
[!!] User 'user_20@%' hasn't specific host restriction.
[!!] User 'user_21@%' hasn't specific host restriction.
[!!] User 'user_22@%' hasn't specific host restriction.
[!!] User 'user_23@%' hasn't specific host restriction.
[!!] User 'user_24@%' hasn't specific host restriction.
[!!] User 'user_25@%' hasn't specific host restriction.
[!!] User 'user_26@%' hasn't specific host restriction.
[!!] User 'user_27@%' hasn't specific host restriction.
[!!] User 'user_28@%' hasn't specific host restriction.
[!!] User 'user_29@%' hasn't specific host restriction.
[!!] User 'user_30@%' hasn't specific host restriction.
[!!] User 'user_31@%' hasn't specific host restriction.
[!!] User 'user_32@%' hasn't specific host restriction.
[!!] User 'user_33@%' hasn't specific host restriction.
[!!] User 'user_34@%' hasn't specific host restriction.
[!!] User 'user_35@%' hasn't specific host restriction.
[!!] User 'user_36@%' hasn't specific host restriction.
[!!] User 'user_37@%' hasn't specific host restriction.
[!!] User 'user_38@%' hasn't specific host restriction.
[!!] User 'user_39@%' hasn't specific host restriction.
[!!] User 'user_40@%' hasn't specific host restriction.
[!!] User 'user_41@%' hasn't specific host restriction.
[!!] User 'user_42@%' hasn't specific host restriction.
[!!] User 'user_43@%' hasn't specific host restriction.
[!!] User 'user_44@%' hasn't specific host restriction.
[!!] User 'user_45@%' hasn't specific host restriction.
[!!] User 'user_46@%' hasn't specific host restriction.
[!!] User 'user_47@%' hasn't specific host restriction.
[!!] User 'user_48@%' hasn't specific host restriction.
[!!] User 'user_49@%' hasn't specific host restriction.
[!!] User 'user_50@%' hasn't specific host restriction.
[!!] User 'user_51@%' hasn't specific host restriction.
[!!] User 'user_52@%' hasn't specific host restriction.
[!!] User 'user_53@%' hasn't specific host restriction.
[!!] User 'user_54@%' hasn't specific host restriction.
[!!] User 'user_55@%' hasn't specific host restriction.
[!!] User 'user_56@%' hasn't specific host restriction.
[!!] User 'user_57@%' hasn't specific host restriction.
[!!] User 'user_58@%' hasn't specific host restriction.
[!!] User 'user_59@%' hasn't specific host restriction.
[!!] User 'user_60@%' hasn't specific host restriction.
[!!] User 'user_61@%' hasn't specific host restriction.
[!!] User 'user_62@%' hasn't specific host restriction.
[!!] User 'user_63@%' hasn't specific host restriction.
[!!] User 'user_64@%' hasn't specific host restriction.
[!!] User 'user_65@%' hasn't specific host restriction.
[!!] User 'user_66@%' hasn't specific host restriction.
[!!] User 'user_67@%' hasn't specific host restriction.
[!!] User 'user_68@%' hasn't specific host restriction.
[!!] User 'user_69@%' hasn't specific host restriction.
[!!] User 'user_70@%' hasn't specific host restriction.

[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
Argument "gcache.mem_size" isn't numeric in addition (+) at mysqltuner.pl line
        1965 (#3)
    (W numeric) The indicated string was fed as an argument to an operator
    that expected a numeric value instead.  If you're fortunate the message
    will identify which operator was so unfortunate.

Argument "gcache.mem_size" isn't numeric in addition (+) at mysqltuner.pl line
        1972 (#3)

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 6d 10h 44m 41s (2 q [0.000 qps], 411K conn, TX: 196B, RX: 137B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 31.2G
[--] Max MySQL memory    : 687.0M
[--] Other process memory: 1.2G
[--] Total buffers: 512.0M global + 2.5M per thread (70 max threads)
[--] P_S Max memory usage: 0B
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
        mysqltuner.pl line 285 (#3)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
        mysqltuner.pl line 288 (#3)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
        mysqltuner.pl line 291 (#3)
[--] Galera GCache Max memory usage: *main::get_wsrep_optionB
[OK] Maximum reached memory usage: 619.5M (1.94% of installed RAM)
[OK] Maximum possible memory usage: 687.0M (2.15% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2)
[OK] Highest usage of available connections: 61% (43/70)
[OK] Aborted connections: 1.74%  (7159/411268)
[!!] Query cache should be disabled by default due to mutex contention.
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] No tmp tables created on disk
[OK] Thread cache hit rate: 99% (43 created / 411K connections)
[OK] Table cache hit rate: 100% (2K open / 0 opened)
[OK] Open file limit used: 5% (3K/65K)
[OK] Table locks acquired immediately: 99% (185M immediate / 185M locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.3% (48M used / 251M cache)
[OK] Key buffer size / total MyISAM indexes: 240.0M/100.8M
[OK] Read Key buffer hit rate: 100.0% (7B cached / 333K reads)
[!!] Write Key buffer hit rate: 10.7% (15M cached / 13M writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 240.0M/40.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 33.19% (5098 used/ 15359 total)
[OK] InnoDB Read buffer efficiency: 99.72% (1828621 hits/ 1833715 total)
[!!] InnoDB Write Log efficiency: 2100% (21 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 22 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    query_cache_type (=0)
[root@magiccolor tmp]#

жду ваших рекомендаций что нужно у меня исправить! чтобы бд не падало! за ранее благодарен всем! также может что по безопасности нужно сделать!

[team-vip]

Для начала стоит посмотреть на ошибку.

Где посмотреть ошибку? когда бд упадет?

[coldcube]

Присоединяюсь к вопросу, бд падает, на любой машине, что 512 мб что 8гб.

SpoilerShow

Code: Select all

InnoDB: using the same InnoDB data or log files.
160419  1:07:58  InnoDB: Unable to open the first data file
InnoDB: Error in opening ./ibdata1
160419  1:07:58  InnoDB: Operating system error number 11 in a file operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
160419  1:07:58 InnoDB: Could not open or create data files.
160419  1:07:58 InnoDB: If you tried to add new data files, and it failed here,
160419  1:07:58 InnoDB: you should now edit innodb_data_file_path in my.cnf back
160419  1:07:58 InnoDB: to what it was, and remove the new ibdata files InnoDB created
160419  1:07:58 InnoDB: in this failed attempt. InnoDB only wrote those files full of
160419  1:07:58 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
160419  1:07:58 InnoDB: remove old data files which contain your precious data!
160419  1:07:58 [ERROR] Plugin 'InnoDB' init function returned error.
160419  1:07:58 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160419  1:07:58 [ERROR] Unknown/unsupported storage engine: InnoDB
160419  1:07:58 [ERROR] Aborting
160419  1:07:58 [Note] /usr/sbin/mysqld: Shutdown complete

Вижу что проблема с innodb, но не знаю как решить не сталкивался, весту ставлю на чистую центос машину, сипмтомы, ребут - 5 минут работы - падает.

SpoilerShow

Code: Select all

160419  1:38:04 InnoDB: The InnoDB memory heap is disabled
160419  1:38:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160419  1:38:04 InnoDB: Compressed tables use zlib 1.2.8
160419  1:38:04 InnoDB: Using Linux native AIO
160419  1:38:04 InnoDB: Initializing buffer pool, size = 128.0M
160419  1:38:04 InnoDB: Completed initialization of buffer pool
160419  1:38:04 InnoDB: highest supported file format is Barracuda.
160419  1:38:04  InnoDB: Waiting for the background threads to start
160419  1:38:05 InnoDB: 5.5.47 started; log sequence number 2005384
160419  1:38:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160419  1:38:05 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160419  1:38:05 [Note] Server socket created on IP: '0.0.0.0'.
160419  1:38:05 [Note] Event Scheduler: Loaded 0 events
160419  1:38:05 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.47-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Вот лог с убунты, ребутнул сервер, 5 минут все работает, бд падает, service mysql restart, работает не определенное количества времени, падает всегда по разному.