MySQL падает

[radiolip]

Залез в my.cnf почему там нету важных настроек связанных с буфером, и.т.п.???

вот что в файле.

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

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

[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
log_error=/var/log/mysql/error.log
max_connections=200
max_user_connections=30
wait_timeout=30
interactive_timeout=50
long_query_time=5
innodb_file_per_table

!includedir /etc/mysql/conf.d/

а где же?

innodb_buffer_pool_size=
innodb_log_file_size=
innodb_log_buffer_size=
innodb_flush_log_at_trx_commit=

[radiolip]

погуглил и нашел: Стандартные параметры настройки innodb годятся разве что для крохотных баз с небольшой нагрузкой.

[radiolip]

Добавил в my.cnf

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

Было: Память: 297 мб

Стало: Память: 146 мб

[radiolip]

[radiolip]

После того как выбрал шаблон phpfcgid, вроде бы нагрузка на сервер снизилась. И перестала куча Apache2 плодится, как в первом скриншоте. К стати с этим шаблоном загрузка процессора в панели не работает, всегда Процессор: 0%

[radiolip]

может при одновременных online 120, это нормально для mysql, Или нет?

[imperio]

Нет, не нормально

[radiolip]

Нет, не нормально

Я выбрал для Apache2 шаблон phpfcgid, процессор Apache теперь не грузит, Ram потребление тоже уменьшилось, но вот что делать с mysql который так грузит проц, может у вас есть рекомендации?

вчера в my.cnf Добавил настройки для Innodb, удалось снизить потребление Ram, сейчас вроде выше 160мб не лезет, а было до 315мб.

[imperio]

Сейчас ram не имеет значения, у вас нагрузка на процессор.
Я ранее писал о возможных причинах. Битые индексы или таблицы базы вашего сайта.
Других вариантов у меня нет. Попробуйте воспользоваться помощником в phpmyadmin

[Lancaster]

Конфиг мускула дефолтный. Как решить?

Code: Select all

141206 00:24:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141206  0:24:33 [Note] Plugin 'FEDERATED' is disabled.
141206  0:24:33 InnoDB: The InnoDB memory heap is disabled
141206  0:24:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141206  0:24:33 InnoDB: Compressed tables use zlib 1.2.7
141206  0:24:33 InnoDB: Using Linux native AIO
141206  0:24:33 InnoDB: Initializing buffer pool, size = 8.0M
141206  0:24:33 InnoDB: Completed initialization of buffer pool
141206  0:24:33 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
141206  0:24:33  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
141206  0:24:33  InnoDB: Waiting for the background threads to start
141206  0:24:34 InnoDB: 5.5.40 started; log sequence number 1953150
141206  0:24:34 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141206  0:24:34 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
141206  0:24:34 [Note] Server socket created on IP: '0.0.0.0'.
141206  0:24:34 [Note] Event Scheduler: Loaded 0 events
141206  0:24:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-0+wheezy1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
Killed
141206 02:44:40 mysqld_safe Number of processes running now: 0
141206 02:44:40 mysqld_safe mysqld restarted
/usr/sbin/mysqld: error while loading shared libraries: libnsl.so.1: cannot map zero-fill pages: Cannot allocate memory
141206 11:53:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141206 11:53:51 [Note] Plugin 'FEDERATED' is disabled.
141206 11:53:51 InnoDB: The InnoDB memory heap is disabled
141206 11:53:51 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141206 11:53:51 InnoDB: Compressed tables use zlib 1.2.7
141206 11:53:51 InnoDB: Using Linux native AIO
141206 11:53:51 InnoDB: Initializing buffer pool, size = 8.0M
141206 11:53:51 InnoDB: Completed initialization of buffer pool
141206 11:53:51 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
141206 11:53:52  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
141206 11:53:52  InnoDB: Waiting for the background threads to start
141206 11:53:53 InnoDB: 5.5.40 started; log sequence number 1953150
141206 11:53:53 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141206 11:53:53 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
141206 11:53:53 [Note] Server socket created on IP: '0.0.0.0'.
141206 11:53:53 [Note] Event Scheduler: Loaded 0 events
141206 11:53:53 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-0+wheezy1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
Killed
141206 12:30:28 mysqld_safe Number of processes running now: 0
141206 12:30:28 mysqld_safe mysqld restarted
Killed
141206 12:32:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141206 12:32:17 [Note] Plugin 'FEDERATED' is disabled.
141206 12:32:17 InnoDB: The InnoDB memory heap is disabled
141206 12:32:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141206 12:32:17 InnoDB: Compressed tables use zlib 1.2.7
141206 12:32:17 InnoDB: Using Linux native AIO
141206 12:32:17 InnoDB: Initializing buffer pool, size = 8.0M
141206 12:32:17 InnoDB: Completed initialization of buffer pool
141206 12:32:17 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
141206 12:32:17  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
141206 12:32:17  InnoDB: Waiting for the background threads to start
141206 12:32:18 InnoDB: 5.5.40 started; log sequence number 1953150
141206 12:32:18 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141206 12:32:18 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
141206 12:32:18 [Note] Server socket created on IP: '0.0.0.0'.
141206 12:32:18 [Note] Event Scheduler: Loaded 0 events
141206 12:32:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-0+wheezy1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
141206 16:13:26 [Note] /usr/sbin/mysqld: Normal shutdown

141206 16:13:26 [Note] Event Scheduler: Purging the queue. 0 events
141206 16:13:28 [Warning] /usr/sbin/mysqld: Forcing close of thread 5290  user: 'lancaster_bans'

141206 16:13:28  InnoDB: Starting shutdown...
141206 16:13:30  InnoDB: Shutdown completed; log sequence number 2042624
141206 16:13:30 [Note] /usr/sbin/mysqld: Shutdown complete

141206 16:13:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
141206 16:13:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141206 16:13:31 [Note] Plugin 'FEDERATED' is disabled.
141206 16:13:31 InnoDB: The InnoDB memory heap is disabled
141206 16:13:31 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141206 16:13:31 InnoDB: Compressed tables use zlib 1.2.7
141206 16:13:31 InnoDB: Using Linux native AIO
141206 16:13:31 InnoDB: Initializing buffer pool, size = 128.0M
141206 16:13:31 InnoDB: Completed initialization of buffer pool
141206 16:13:31 InnoDB: highest supported file format is Barracuda.
141206 16:13:31  InnoDB: Waiting for the background threads to start
141206 16:13:32 InnoDB: 5.5.40 started; log sequence number 2042624
141206 16:13:32 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141206 16:13:32 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
141206 16:13:32 [Note] Server socket created on IP: '0.0.0.0'.
141206 16:13:32 [Note] Event Scheduler: Loaded 0 events
141206 16:13:32 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-0+wheezy1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)