Plantage régulier de MYSQL

Spheerys · Avril 20, 2016, 8:17pm

Sur un serveur sous wheezy, j’ai régulièrement des plantages de mysql/percona

Percona 5.6.26 est installé et j’ai sur ce serveur 2 “gros” scripts :

Prestashop pour la boutique
Invision Board pour le forum

Les plantages disparaissent lorsque le forum est mis "hors d’état de nuire"
Par contre s’il est actif, le serveur plante tous les 2 jours.

Voici ce que donne le log mysql :

2016-04-20 18:11:03 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` + 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:11:04 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` + 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:11:05 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` + 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:11:07 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` + 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:11:10 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` - 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:11:10 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` - 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:11:11 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` - 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:11:11 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` - 1, `date_add` = NOW()
                        WHERE `id_product` = 141
                        AND `id_cart` = 31764
                        LIMIT 1
2016-04-20 18:35:30 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: UPDATE `ps_order_invoice` SET number =(SELECT new_number FROM (SELECT (MAX(`number`) + 1) AS new_number
            FROM `ps_order_invoice`) AS result) WHERE `id_order_invoice` = 11867
2016-04-20 18:44:58 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: UPDATE `ps_order_invoice` SET number =(SELECT new_number FROM (SELECT (MAX(`number`) + 1) AS new_number
            FROM `ps_order_invoice`) AS result) WHERE `id_order_invoice` = 11868
2016-04-20 19:01:18 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
                        SET `quantity` = `quantity` + 2, `date_add` = NOW()
                        WHERE `id_product` = 182
                        AND `id_cart` = 30953
                        LIMIT 1
2016-04-20 19:02:54 3884 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: UPDATE `ps_order_invoice` SET number =(SELECT new_number FROM (SELECT (MAX(`number`) + 1) AS new_number
            FROM `ps_order_invoice`) AS result) WHERE `id_order_invoice` = 11869
Killed
160420 19:23:26 mysqld_safe Number of processes running now: 0
160420 19:23:26 mysqld_safe mysqld restarted
2016-04-20 19:23:28 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2016-04-20 19:23:28 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-04-20 19:23:28 0 [Note] /usr/sbin/mysqld (mysqld 5.6.26-74.0-log) starting as process 23706 ...
2016-04-20 19:23:28 23706 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-04-20 19:23:28 23706 [Note] Plugin 'FEDERATED' is disabled.
2016-04-20 19:23:28 23706 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-04-20 19:23:28 23706 [Note] InnoDB: The InnoDB memory heap is disabled
2016-04-20 19:23:28 23706 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-04-20 19:23:28 23706 [Note] InnoDB: Memory barrier is not used
2016-04-20 19:23:28 23706 [Note] InnoDB: Compressed tables use zlib 1.2.7
2016-04-20 19:23:28 23706 [Note] InnoDB: Using Linux native AIO
2016-04-20 19:23:28 23706 [Note] InnoDB: Using CPU crc32 instructions
2016-04-20 19:23:28 23706 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-04-20 19:23:28 23706 [Note] InnoDB: Completed initialization of buffer pool
2016-04-20 19:23:28 23706 [Note] InnoDB: Highest supported file format is Barracuda.
2016-04-20 19:23:28 23706 [Note] InnoDB: The log sequence numbers 17461728446 and 17461728446 in ibdata files do not match the log sequence number 17587562963 in the ib_logfiles!
2016-04-20 19:23:28 23706 [Note] InnoDB: Database was not shutdown normally!
2016-04-20 19:23:28 23706 [Note] InnoDB: Starting crash recovery.
2016-04-20 19:23:28 23706 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-04-20 19:23:28 23706 [Note] InnoDB: Restoring possible half-written data pages 
2016-04-20 19:23:28 23706 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 9568488, file name mysql-bin.005084
2016-04-20 19:23:29 23706 [Note] InnoDB: 128 rollback segment(s) are active.
2016-04-20 19:23:29 23706 [Note] InnoDB: Waiting for purge to start
2016-04-20 19:23:29 23706 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.26-74.0 started; log sequence number 17587562963
2016-04-20 19:23:29 23706 [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
2016-04-20 19:23:29 23706 [Note] Starting crash recovery...
2016-04-20 19:23:29 23706 [Note] Crash recovery finished.
2016-04-20 19:23:29 23706 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2016-04-20 19:23:29 23706 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2016-04-20 19:23:29 23706 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2016-04-20 19:23:29 23706 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2016-04-20 19:23:29 23706 [Note] Server socket created on IP: '127.0.0.1'.
2016-04-20 19:23:29 23706 [Note] Event Scheduler: Loaded 0 events
2016-04-20 19:23:29 23706 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.26-74.0-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Percona Server (GPL), Release 74.0, Revision 32f8dfd
2016-04-20 19:23:29 23706 [ERROR] /usr/sbin/mysqld: Table './forum/ibf_core_sessions' is marked as crashed and should be repaired
2016-04-20 19:23:29 23706 [Warning] Checking table:   './forum/ibf_core_sessions'
2016-04-20 19:23:30 23706 [ERROR] /usr/sbin/mysqld: Table './forum/ibf_forums_forums' is marked as crashed and should be repaired
2016-04-20 19:23:30 23706 [Warning] Checking table:   './forum/ibf_forums_forums'
2016-04-20 19:23:30 23706 [ERROR] /usr/sbin/mysqld: Table './forum/ibf_core_members' is marked as crashed and should be repaired
2016-04-20 19:23:30 23706 [Warning] Checking table:   './forum/ibf_core_members'
2016-04-20 19:23:34 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_configuration' is marked as crashed and should be repaired
2016-04-20 19:23:34 23706 [Warning] Checking table:   './prestashop/ps_configuration'
2016-04-20 19:23:35 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_customer' is marked as crashed and should be repaired
2016-04-20 19:23:35 23706 [Warning] Checking table:   './prestashop/ps_customer'
2016-04-20 19:23:36 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_customer_group' is marked as crashed and should be repaired
2016-04-20 19:23:36 23706 [Warning] Checking table:   './prestashop/ps_customer_group'
2016-04-20 19:23:37 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_cart' is marked as crashed and should be repaired
2016-04-20 19:23:37 23706 [Warning] Checking table:   './prestashop/ps_cart'
2016-04-20 19:23:39 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_orders' is marked as crashed and should be repaired
2016-04-20 19:23:39 23706 [Warning] Checking table:   './prestashop/ps_orders'
2016-04-20 19:23:40 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_address' is marked as crashed and should be repaired
2016-04-20 19:23:40 23706 [Warning] Checking table:   './prestashop/ps_address'
2016-04-20 19:23:40 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_cart_product' is marked as crashed and should be repaired
2016-04-20 19:23:40 23706 [Warning] Checking table:   './prestashop/ps_cart_product'
2016-04-20 19:23:41 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_product' is marked as crashed and should be repaired
2016-04-20 19:23:41 23706 [Warning] Checking table:   './prestashop/ps_product'
2016-04-20 19:23:41 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_stock_available' is marked as crashed and should be repaired
2016-04-20 19:23:41 23706 [Warning] Checking table:   './prestashop/ps_stock_available'
2016-04-20 19:23:41 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_cart_cart_rule' is marked as crashed and should be repaired
2016-04-20 19:23:41 23706 [Warning] Checking table:   './prestashop/ps_cart_cart_rule'
2016-04-20 19:23:58 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_guest' is marked as crashed and should be repaired
2016-04-20 19:23:58 23706 [Warning] Checking table:   './prestashop/ps_guest'
2016-04-20 19:24:12 23706 [ERROR] /usr/sbin/mysqld: Table './prestashop/ps_smarty_lazy_cache' is marked as crashed and should be repaired
2016-04-20 19:24:12 23706 [Warning] Checking table:   './prestashop/ps_smarty_lazy_cache'
Killed
160420 19:24:20 mysqld_safe Number of processes running now: 0
160420 19:24:20 mysqld_safe mysqld restarted
2016-04-20 19:24:21 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2016-04-20 19:24:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-04-20 19:24:21 0 [Note] /usr/sbin/mysqld (mysqld 5.6.26-74.0-log) starting as process 23919 ...
2016-04-20 19:24:21 23919 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-04-20 19:24:21 23919 [Note] Plugin 'FEDERATED' is disabled.
2016-04-20 19:24:21 23919 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-04-20 19:24:21 23919 [Note] InnoDB: The InnoDB memory heap is disabled
2016-04-20 19:24:21 23919 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-04-20 19:24:21 23919 [Note] InnoDB: Memory barrier is not used
2016-04-20 19:24:21 23919 [Note] InnoDB: Compressed tables use zlib 1.2.7
2016-04-20 19:24:21 23919 [Note] InnoDB: Using Linux native AIO
2016-04-20 19:24:21 23919 [Note] InnoDB: Using CPU crc32 instructions
2016-04-20 19:24:21 23919 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2016-04-20 19:24:21 23919 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-04-20 19:24:21 23919 [ERROR] Plugin 'InnoDB' init function returned error.
2016-04-20 19:24:21 23919 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-04-20 19:24:21 23919 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-04-20 19:24:21 23919 [ERROR] Aborting

2016-04-20 19:24:21 23919 [Note] Binlog end
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'partition'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'BLACKHOLE'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_CHANGED_PAGES'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_METRICS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_CMP'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'INNODB_TRX'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'XTRADB_RSEG'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'XTRADB_INTERNAL_HASH_TABLES'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'XTRADB_READ_VIEW'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'ARCHIVE'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'MEMORY'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'MyISAM'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'CSV'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'MRG_MYISAM'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'sha256_password'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'mysql_old_password'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'mysql_native_password'
2016-04-20 19:24:21 23919 [Note] Shutting down plugin 'binlog'
2016-04-20 19:24:21 23919 [Note] /usr/sbin/mysqld: Shutdown complete

160420 19:24:21 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Voici mon fichier /etc/mysql/my.cnf :

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
# GENERAL #
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
innodb_force_recovery = 0

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 1
sync-binlog                    = 1




max-allowed-packet             = 16M
max-connect-errors             = 1000000

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
#key_buffer		= 16M
#thread_stack		= 192K
#thread_cache_size       = 8
#innodb_buffer_pool_size = 64M

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP



# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 16M
query-cache-limit              = 16M
max-connections                = 30
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 2048
table-open-cache               = 2048
#table_cache		       = 4096
key_buffer_size		       = 16M

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 10
sync-binlog                    = 0
max_binlog_size                = 10M

# LOGGING #
log-error                      = /var/log/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 0
slow-query-log-file            = /var/log/mysql-slow.log



# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 128M


[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Je pense qu’il y a un paramètre mysql qui m’échappe, mais je cale un peu là…

PengouinPdt · Avril 21, 2016, 5:24am

Bon, apparemment, tu cumules les problèmes …

PREMIÈRE chose : JE NE SUIS PAS UN SPÉCIALISTE MYSQL !
Donc, je peux peut-être t’induire en erreur !
(oui, je sais, j’ai écris en majuscule … mais c’est histoire d’être sûr, d’être clair, compris …)

Ma démarche a été de lire ton premier log.
Il est visible que plusieurs de tes tables sont en erreur et ont besoin d’être réparées !

Spheerys:

2016-04-20 19:23:29 23706 [ERROR] /usr/sbin/mysqld: Table ‘./forum/ibf_core_sessions’ is marked as crashed and should be repaired
2016-04-20 19:23:29 23706 [Warning] Checking table: './forum/ibf_core_sessions’
2016-04-20 19:23:30 23706 [ERROR] /usr/sbin/mysqld: Table ‘./forum/ibf_forums_forums’ is marked as crashed and should be repaired
2016-04-20 19:23:30 23706 [Warning] Checking table: './forum/ibf_forums_forums’
2016-04-20 19:23:30 23706 [ERROR] /usr/sbin/mysqld: Table ‘./forum/ibf_core_members’ is marked as crashed and should be repaired
2016-04-20 19:23:30 23706 [Warning] Checking table: './forum/ibf_core_members’
2016-04-20 19:23:34 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_configuration’ is marked as crashed and should be repaired
2016-04-20 19:23:34 23706 [Warning] Checking table: './prestashop/ps_configuration’
2016-04-20 19:23:35 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_customer’ is marked as crashed and should be repaired
2016-04-20 19:23:35 23706 [Warning] Checking table: './prestashop/ps_customer’
2016-04-20 19:23:36 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_customer_group’ is marked as crashed and should be repaired
2016-04-20 19:23:36 23706 [Warning] Checking table: './prestashop/ps_customer_group’
2016-04-20 19:23:37 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_cart’ is marked as crashed and should be repaired
2016-04-20 19:23:37 23706 [Warning] Checking table: './prestashop/ps_cart’
2016-04-20 19:23:39 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_orders’ is marked as crashed and should be repaired
2016-04-20 19:23:39 23706 [Warning] Checking table: './prestashop/ps_orders’
2016-04-20 19:23:40 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_address’ is marked as crashed and should be repaired
2016-04-20 19:23:40 23706 [Warning] Checking table: './prestashop/ps_address’
2016-04-20 19:23:40 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_cart_product’ is marked as crashed and should be repaired
2016-04-20 19:23:40 23706 [Warning] Checking table: './prestashop/ps_cart_product’
2016-04-20 19:23:41 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_product’ is marked as crashed and should be repaired
2016-04-20 19:23:41 23706 [Warning] Checking table: './prestashop/ps_product’
2016-04-20 19:23:41 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_stock_available’ is marked as crashed and should be repaired
2016-04-20 19:23:41 23706 [Warning] Checking table: './prestashop/ps_stock_available’
2016-04-20 19:23:41 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_cart_cart_rule’ is marked as crashed and should be repaired
2016-04-20 19:23:41 23706 [Warning] Checking table: './prestashop/ps_cart_cart_rule’
2016-04-20 19:23:58 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_guest’ is marked as crashed and should be repaired
2016-04-20 19:23:58 23706 [Warning] Checking table: './prestashop/ps_guest’
2016-04-20 19:24:12 23706 [ERROR] /usr/sbin/mysqld: Table ‘./prestashop/ps_smarty_lazy_cache’ is marked as crashed and should be repaired
2016-04-20 19:24:12 23706 [Warning] Checking table: './prestashop/ps_smarty_lazy_cache’
Killed

La dernière ligne n’est jamais bon signe !

Pour les réparer, la documentation de mysql donne ces conseils :

https://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html
https://dev.mysql.com/doc/refman/5.7/en/repair-table.html
Et, il te faudra les réparer, très certainement … mais je ne peux pas t’assurer du bon fonctionnement.
(autrement, je serais sûr de moi ; autrement, je serais compétent sur ce domaine ; autrement …)

Le deuxième point sur lequel j’attire ton attention est celui-ci :

C’est une issue qui semble être connue :

À la lecture de ces liens, voire d’autres, il semble que tu peux résoudre ce problème très facilement par l’influx sur trois paramètres différents :

augmenter ta ram
créer un fichier swap - si ce n’est déjà fait - voire l’augmenter
augmenter le paramètre ‘key_buffer’ - qui maintenant se nomme 'key_buffer_size (si je ne me trompe pas …) -

Une fois que tu auras agis sur ces paramètres, ensuite, et seulement ensuite, essayes de lire la documentation officielle, pour réparer tes tables. Il est très possible que ce soit le manque de mémoire qui est mis en péril celles-ci. Mais ceci n’est qu’une supputation !

Bon courage …

Spheerys · Avril 21, 2016, 9:11am

Merci pour ta réponse
Le fait que les tables soient cassées, c’est “normal” après le crash.
Je les répare régulièrement, mais elles se casseront à nouveau.
Heureusement il n’y a jamais eu de dégât jusqu’à présent à ce niveau !

Je fouille dans tes liens et je regarde si ça change quelque chose

Clochette · Avril 22, 2016, 4:45am

Dans un premier teps tu peux sans doute utiliser les logs de MySQL afin de vérifier qu’une ou plusieurs requêtes ne soient pas à l’origine du problème.
Tu peux aussi installer un script d’audit MySQL qui te permettra de vérifier que la configuration soit correct pour tes besoins : MySQL Tuner

Spheerys · Avril 23, 2016, 8:03am

J’ai installé mysqltuner.
Le soucis c’est que le serveur ne tient pas 24h en ce moment !
J’ai toutefois suivi les premières recommandations mais ça plante tjrs :

160423 05:38:35 mysqld_safe Number of processes running now: 0
160423 05:38:35 mysqld_safe mysqld restarted
2016-04-23 05:38:37 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2016-04-23 05:38:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-04-23 05:38:37 0 [Note] /usr/sbin/mysqld (mysqld 5.6.26-74.0-log) starting as process 24381 ...
2016-04-23 05:38:37 24381 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-04-23 05:38:37 24381 [Note] Plugin 'FEDERATED' is disabled.
2016-04-23 05:38:37 24381 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-04-23 05:38:37 24381 [Note] InnoDB: The InnoDB memory heap is disabled
2016-04-23 05:38:37 24381 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-04-23 05:38:37 24381 [Note] InnoDB: Memory barrier is not used
2016-04-23 05:38:37 24381 [Note] InnoDB: Compressed tables use zlib 1.2.7
2016-04-23 05:38:37 24381 [Note] InnoDB: Using Linux native AIO
2016-04-23 05:38:37 24381 [Note] InnoDB: Using CPU crc32 instructions
2016-04-23 05:38:37 24381 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2016-04-23 05:38:37 24381 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-04-23 05:38:37 24381 [ERROR] Plugin 'InnoDB' init function returned error.
2016-04-23 05:38:37 24381 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-04-23 05:38:37 24381 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-04-23 05:38:37 24381 [ERROR] Aborting

2016-04-23 05:38:37 24381 [Note] Binlog end
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'partition'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'BLACKHOLE'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_CHANGED_PAGES'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_METRICS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_CMP'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'INNODB_TRX'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'XTRADB_RSEG'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'XTRADB_INTERNAL_HASH_TABLES'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'XTRADB_READ_VIEW'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'ARCHIVE'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'MEMORY'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'MyISAM'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'CSV'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'MRG_MYISAM'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'sha256_password'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'mysql_old_password'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'mysql_native_password'
2016-04-23 05:38:37 24381 [Note] Shutting down plugin 'binlog'
2016-04-23 05:38:37 24381 [Note] /usr/sbin/mysqld: Shutdown complete

160423 05:38:37 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Par ailleurs, j’ai déjà un fichier swap utilisé par défaut (c’est sur un VPS Gandi).
Je ne sais plus trop quoi modifier sur mon fichier conf my.cnf

Je ne sais pas trop si la configuration d’Apache peut jouer dans ces plantages, mais voici le fichier conf :

# Global configuration
#

#
# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
# 
# NOTE!  If you intend to place this on an NFS (or otherwise network)
# mounted filesystem then please read the LockFile documentation (available
# at <URL:http://httpd.apache.org/docs/2.2/mod/mpm_common.html#lockfile>);
# you will save yourself a lot of trouble.
# 
# Do NOT add a slash at the end of the directory path.
#
#ServerRoot "/etc/apache2"

#
# The accept serialization lock file MUST BE STORED ON A LOCAL DISK.
#
LockFile ${APACHE_LOCK_DIR}/accept.lock

#
# PidFile: The file in which the server should record its process
# identification number when it starts.
# This needs to be set in /etc/apache2/envvars
#
PidFile ${APACHE_PID_FILE}

#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 300

#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive On

#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100

#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 5

##
## Server-Pool Size Regulation (MPM specific)
##
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
    StartServers          5
    MinSpareServers       5
    MaxSpareServers      10
    MaxClients          150
    MaxRequestsPerChild   0
</IfModule>

# worker MPM
# StartServers: initial number of server processes to start
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadLimit: ThreadsPerChild can be changed to this maximum value during a
#              graceful restart. ThreadLimit can only be changed by stopping
#              and starting Apache.
# ThreadsPerChild: constant number of worker threads in each server process
# MaxClients: maximum number of simultaneous client connections
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_worker_module>
    StartServers          2
    MinSpareThreads      25
    MaxSpareThreads      75
    ThreadLimit          64
    ThreadsPerChild      25
    MaxClients          150
    MaxRequestsPerChild   0
</IfModule>

# event MPM
# StartServers: initial number of server processes to start
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxClients: maximum number of simultaneous client connections
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
    StartServers          5
    MinSpareServers       5
    MaxSpareServers      10
    MaxClients          150
    MaxRequestsPerChild   0
</IfModule>

# worker MPM
# StartServers: initial number of server processes to start
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadLimit: ThreadsPerChild can be changed to this maximum value during a
#              graceful restart. ThreadLimit can only be changed by stopping
#              and starting Apache.
# ThreadsPerChild: constant number of worker threads in each server process
# MaxClients: maximum number of simultaneous client connections
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_worker_module>
    StartServers          2
    MinSpareThreads      25
    MaxSpareThreads      75
    ThreadLimit          64
    ThreadsPerChild      25
    MaxClients          150
    MaxRequestsPerChild   0
</IfModule>

# event MPM
# StartServers: initial number of server processes to start
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxClients: maximum number of simultaneous client connections
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_event_module>
    StartServers          2
    MinSpareThreads      25
    MaxSpareThreads      75
    ThreadLimit          64
    ThreadsPerChild      25
    MaxClients          150
    MaxRequestsPerChild   0
</IfModule>

# These need to be set in /etc/apache2/envvars
User ${APACHE_RUN_USER}
Group ${APACHE_RUN_GROUP}

#
# AccessFileName: The name of the file to look for in each directory
# for additional configuration directives.  See also the AllowOverride
# directive.
#

AccessFileName .htaccess

# 
# The following lines prevent .htaccess and .htpasswd files from being
# viewed by Web clients.
#
<Files ~ "^\.ht">
    Order allow,deny
    Deny from all
    Satisfy all
</Files>

#
# DefaultType is the default MIME type the server will use for a document
# if it cannot otherwise determine one, such as from filename extensions.
# If your server contains mostly text or HTML documents, "text/plain" is
# a good value.  If most of your content is binary, such as applications
# or images, you may want to use "application/octet-stream" instead to
# keep browsers from trying to display binary files as though they are
# text.
# 
# It is also possible to omit any default MIME type and let the
# client's browser guess an appropriate action instead. Typically the
# browser will decide based on the file's extension then. In cases
# where no good assumption can be made, letting the default MIME type
# unset is suggested  instead of forcing the browser to accept
# incorrect  metadata.

DefaultType None


#
# HostnameLookups: Log the names of clients or just their IP addresses
# e.g., www.apache.org (on) or 204.62.129.132 (off).
# The default is off because it'd be overall better for the net if people
# had to knowingly turn this feature on, since enabling it means that
# each client request will result in AT LEAST one lookup request to the
# nameserver.
# 
HostnameLookups Off

# ErrorLog: The location of the error log file.
# If you do not specify an ErrorLog directive within a <VirtualHost>
# container, error messages relating to that virtual host will be
# logged here.  If you *do* define an error logfile for a <VirtualHost>
# container, that host's errors will be logged there and not here.
# 
ErrorLog ${APACHE_LOG_DIR}/error.log

#
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
#
LogLevel warn

# Include module configuration:
Include mods-enabled/*.load
Include mods-enabled/*.conf

# Include list of ports to listen on and which to use for name based vhosts
Include ports.conf

# 
# The following directives define some format nicknames for use with
# a CustomLog directive (see below).
# If you are behind a reverse proxy, you might want to change %h into %{X-Forwarded-For}i
#
LogFormat "%v:%p %h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" vhost_combined
LogFormat "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %O" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

# Include of directories ignores editors' and dpkg's backup files,
# see the comments above for details.

# Include generic snippets of statements
Include conf.d/

# Include the virtual host configurations:
Include sites-enabled/

PengouinPdt · Avril 23, 2016, 12:00pm

Encore, une fois, il te dit que le serveur ne peut pas allouer les 128 Mo de mémoire qu’il cherche à allouer !
Résultat, le moteur InnoDB plante ; résultat, le moteur InnoDB n’est pas reconnu, et le processus s’arrête …
Il te faut trouver le moyen d’augmenter la mémoire dont le serveur SQL a besoin.

Selon ta configuration MySQL, telle que tu nous l’as montrée, le paramètre ‘key_buffer_size’, pour le moteur InnoDB, est à 16Mo …
D’ailleurs, tu as les deux paramètres déclarés “key_buffer” et “key_buffer_size” ???

Spheerys · Avril 23, 2016, 6:07pm

Oui j’avais bien saisi ce point et j’avais ajusté le paramètre key_buffer_size ) 256Mo
Mais en effet, j’avais oublié d’ajuster key_buffer en le laissant à 16Mo

J’ai fait la modif et relancé mysql, pour voir si ça tient cette fois.

Spheerys · Avril 24, 2016, 4:09pm

Bon rien à faire, ça plante tjrs… je viens d’ajouter 512Mo de RAM au serveur, qui en avait déjà 1,5Go
Si effectivement ça pourrait résoudre le problème, ça me chagrine qd même car ce même serveur tournait il y a un an avec 768Mo de RAM.
Depuis, j’ai juste mis à jour Debian de Lenny vers Wheezy et mis à jour le script de control panel (i-MSCP Omega), sans pour autant que la fréquentation du serveur augmente notablement…

Bref… j’espère qu’avec les petites corrections de tuning + l’ajout de RAM, ça sera une affaire réglée

Spheerys · Avril 26, 2016, 8:34am

Bon malheureusement, ajouter de la RAM n’a rien résolu, j’ai tjrs des plantages réguliers de MySQL

Dernier log en date :

2016-04-26 10:03:42 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 32113
						LIMIT 1
2016-04-26 10:03:43 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 32113
						LIMIT 1
2016-04-26 10:03:55 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` + 1458, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 32113
						LIMIT 1
2016-04-26 10:04:03 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1612, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 32113
						LIMIT 1
2016-04-26 10:04:18 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` + 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 32113
						LIMIT 1
2016-04-26 10:04:20 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` + 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 32113
						LIMIT 1
2016-04-26 10:07:34 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: UPDATE `ps_order_invoice` SET number =(SELECT new_number FROM (SELECT (MAX(`number`) + 1) AS new_number
			FROM `ps_order_invoice`) AS result) WHERE `id_order_invoice` = 12015
2016-04-26 10:11:51 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '4.97%',`date_upd` = '2016-04-26 10:11:51' WHERE `name` = 'CONVERSION_RATE' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:11:51 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '1461708000',`date_upd` = '2016-04-26 10:11:51' WHERE `name` = 'CONVERSION_RATE_EXPIRE' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:11:52 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '54,75 €',`date_upd` = '2016-04-26 10:11:52' WHERE `name` = 'AVG_ORDER_VALUE' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:11:52 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '1461708000',`date_upd` = '2016-04-26 10:11:52' WHERE `name` = 'AVG_ORDER_VALUE_EXPIRE' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:11:52 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '58',`date_upd` = '2016-04-26 10:11:52' WHERE `name` = 'ABANDONED_CARTS' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:11:52 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '1461661912',`date_upd` = '2016-04-26 10:11:52' WHERE `name` = 'ABANDONED_CARTS_EXPIRE' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:11:52 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '1,39 €',`date_upd` = '2016-04-26 10:11:52' WHERE `name` = 'NETPROFIT_VISIT' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:11:52 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_configuration_kpi` SET `value` = '1461708000',`date_upd` = '2016-04-26 10:11:52' WHERE `name` = 'NETPROFIT_VISIT_EXPIRE' AND (id_shop_group IS NULL OR id_shop_group = 0) AND (id_shop IS NULL OR id_shop = 0) LIMIT 1
2016-04-26 10:20:56 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
2016-04-26 10:20:57 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
2016-04-26 10:20:58 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
2016-04-26 10:20:59 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
2016-04-26 10:20:59 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
2016-04-26 10:21:04 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
2016-04-26 10:21:05 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
2016-04-26 10:21:06 27975 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `ps_cart_product`
						SET `quantity` = `quantity` - 1, `date_add` = NOW()
						WHERE `id_product` = 186
						AND `id_cart` = 24557
						LIMIT 1
Killed
160426 10:27:19 mysqld_safe Number of processes running now: 0
160426 10:27:19 mysqld_safe mysqld restarted
2016-04-26 10:27:20 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2016-04-26 10:27:20 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-04-26 10:27:20 0 [Note] /usr/sbin/mysqld (mysqld 5.6.26-74.0-log) starting as process 11437 ...
2016-04-26 10:27:20 11437 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-04-26 10:27:20 11437 [Note] Plugin 'FEDERATED' is disabled.
2016-04-26 10:27:21 11437 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-04-26 10:27:21 11437 [Note] InnoDB: The InnoDB memory heap is disabled
2016-04-26 10:27:21 11437 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-04-26 10:27:21 11437 [Note] InnoDB: Memory barrier is not used
2016-04-26 10:27:21 11437 [Note] InnoDB: Compressed tables use zlib 1.2.7
2016-04-26 10:27:21 11437 [Note] InnoDB: Using Linux native AIO
2016-04-26 10:27:21 11437 [Note] InnoDB: Using CPU crc32 instructions
2016-04-26 10:27:21 11437 [Note] InnoDB: Initializing buffer pool, size = 512.0M
InnoDB: mmap(549453824 bytes) failed; errno 12
2016-04-26 10:27:21 11437 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-04-26 10:27:21 11437 [ERROR] Plugin 'InnoDB' init function returned error.
2016-04-26 10:27:21 11437 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-04-26 10:27:21 11437 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-04-26 10:27:21 11437 [ERROR] Aborting

2016-04-26 10:27:21 11437 [Note] Binlog end
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'partition'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'BLACKHOLE'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_CHANGED_PAGES'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_METRICS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_CMP'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'INNODB_TRX'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'XTRADB_RSEG'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'XTRADB_INTERNAL_HASH_TABLES'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'XTRADB_READ_VIEW'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'ARCHIVE'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'MEMORY'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'MyISAM'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'CSV'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'MRG_MYISAM'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'sha256_password'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'mysql_old_password'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'mysql_native_password'
2016-04-26 10:27:21 11437 [Note] Shutting down plugin 'binlog'
2016-04-26 10:27:21 11437 [Note] /usr/sbin/mysqld: Shutdown complete

160426 10:27:21 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Prestashop semble balancer des requêtes qui génèrent des warning.
Est-ce que ça pourrait être la cause des plantages ?

Spheerys · Avril 26, 2016, 12:03pm

Voilà le résultat de mysqltuner :

 >>  MySQLTuner 1.6.10 - 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.

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.26-74.0-log
[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: 370M (Tables: 961)
[--] Data in InnoDB tables: 192M (Tables: 316)
[--] Data in MEMORY tables: 385K (Tables: 4)
[!!] Total fragmented tables: 14
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 47s (2 q [0.043 qps], 1K conn, TX: 221B, RX: 266B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 1.0G
[--] Other process memory: 859.3M
[--] Total buffers: 576.0M global + 1.1M per thread (30 max threads)
[--] P_S Max memory usage: 449M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.0G (51.28% of installed RAM)
[OK] Maximum possible memory usage: 1.0G (52.73% 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: 13% (4/30)
[OK] Aborted connections: 0.00%  (0/1042)
[OK] Query cache is 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% (4 created / 1K connections)
[OK] Table cache hit rate: 100% (1K open / 0 opened)
[OK] Open file limit used: 3% (1K/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] Binlog cache memory access: 100.00% ( 18 Memory / 18 Total)
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 24 thread(s).
[OK] thread_pool_size between 16 and 36 when using InnoDB storage engine.
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 449.3M
[--] Sys schema isn't installed.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.5% (49M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/132.2M
[!!] Read Key buffer hit rate: 26.1% (116K cached / 85K reads)
[!!] Write Key buffer hit rate: 72.1% (204 cached / 57 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 256.0M/192.1M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 4.97% (814 used/ 16383 total)
[OK] InnoDB Read buffer efficiency: 95.87% (18626 hits/ 19429 total)
[!!] InnoDB Write Log efficiency: 59.09% (39 hits/ 66 total)
[OK] InnoDB log waits: 0.00% (0 waits / 27 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
    MySQL started within last 24 hours - recommendations may be inaccurate

J’ai un peu de mal à le faire durer 24h par contre :o/