OVH Community, votre nouvel espace communautaire.

Réduction de l'utilisation du CPU par le service mysql


Ludo.H
15/09/2016, 10h01
Bonjour,

Les requêtes sont peut être lentes.
Il faut surveiller les slowlog et optimiser les requêtes.

http://dev.mysql.com/doc/refman/5.5/...query-log.html

Cdt,

ovniz
14/09/2016, 17h25
Il y a quoi dans les fichiers de /etc/mysql/conf.d/ ?

et ça peut pas marcher, trop de RAM allouée à Mysql :
[!!] Maximum reached memory usage: 16.2G (104.03% of installed RAM)
[!!] Maximum possible memory usage: 16.6G (106.29% of installed RAM)

Il faut normalement 75-80% max pour mysql. Il faut en laisser un peu pour la machine aussi.
Il faut donc urgemment diminuer certaines ressources dans la conf mysql.

Faites aussi une sortie avec tuning-primer.sh, ça permet d'avoir une autre analyse.
Les 2 se complètent pas mal.

rherchel
14/09/2016, 15h15
Bonjour,
J'ai des connaissances mais pas vraiment un expert ni un crack les serveurs Web.
Et en ce moment, j'ai un petit soucis avec mon serveur dédié web sous Ubuntu 14.04 (Core i5 4 cores / 4 threads) et 16Go de RAM.
Le hic c'est que le service mysql crame tous mon CPU et le site est lent et des fois inaccessible.
Le type de table dans mysql est innodb et même après un optimisation des tables rien y fait.
Je serais vraiment reconnaissant si quelqu'un peut m'aider là-dessus.
Voic ma configuration /etc/mysql/my.cnf
Code:
# - "/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]
#
# * Basic Settings
#
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
#
# 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              = 6144M
key_buffer_size         = 6144M
max_allowed_packet      = 512M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 500
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1024M
query_cache_size        = 10240M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
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
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
secure-file-priv = /var/tmp

[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/
Et ci-après le résultat de MySQLTuner:
Code:
>>  MySQLTuner 1.6.18 - Major Hayden 
>>  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
error: Found option without preceding group in config file: /etc/mysql/conf.d/local.cnf at line: 1
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.5.49-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ---------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                    ISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 3G (Tables: 429)
[--] Data in MyISAM tables: 18M (Tables: 38)
[--] Data in MEMORY tables: 0B (Tables: 17)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations ------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics ---------------------------------------------------
[--] Up for: 21m 39s (1M q [1K qps], 258 conn, TX: 751M, RX: 604M)
[--] Reads / Writes: 57% / 43%
[--] Binary logging is disabled
[--] Physical Memory     : 15.6G
[--] Max MySQL memory    : 16.6G
[--] Other process memory: 117.9M
[--] Total buffers: 16.2G global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 16.2G (104.03% of installed RAM)
[!!] Maximum possible memory usage: 16.6G (106.29% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 11% (17/151)
[!!] Aborted connections: 6.20%  (16/258)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (118 temp sorts / 56K sorts)
[!!] Joins performed without indexes: 94
[!!] Temporary tables created on disk: 33% (383 on disk / 1K total)
[OK] Thread cache hit rate: 93% (17 created / 258 connections)
[OK] Table cache hit rate: 79% (197 open / 247 opened)
[OK] Open file limit used: 4% (42/1K)
[OK] Table locks acquired immediately: 100% (137K immediate / 137K locks)

-------- Performance schema ----------------------------------------------------
[--] Performance schema is disabled.

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

-------- MyISAM Metrics --------------------------------------------------------
[!!] Key buffer used: 18.8% (1B used / 6B cache)
[OK] Key buffer size / total MyISAM indexes: 6.0G/11.3M
[OK] Read Key buffer hit rate: 100.0% (14M cached / 3K reads)
[OK] Write Key buffer hit rate: 100.0% (2M cached / 0 writes)

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

-------- InnoDB Metrics --------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/3.0G
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (778690869 hits/ 778696521 total)
[!!] InnoDB Write Log efficiency: 37.2% (10851 hits/ 29171 total)
[OK] InnoDB log waits: 0.00% (0 waits / 40022 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:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configurat                    ion with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=0)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_size (>= 3G) if possible.
Cordialement