Bonjour,

Nous venons de mettre en place un site web sous EzPublish, et nous rencontrons malheureusement quelques soucis, notamment un problème de performance sur le serveur Mysql.

Etant plutôt habitué au monde Oracle, je dois avouer que j'essaye de chercher comme je peux avec notre ami google, mais ce n'est pas évident de s'y retrouver.

Voici le souci :
Depuis la mise en ligne, le serveur MySQL (sur un serveur Ubuntu) explose et le process affiche 200% de CPU Usage en moyenne. Le Load Average peut monter à 10...

J'ai tenté quelques améliorations mais un peu à l'aveugle...

Voici le top :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
top - 16:58:24 up  2:25,  2 users,  load average: 2.13, 2.39, 3.16
Tasks:  87 total,   1 running,  86 sleeping,   0 stopped,   0 zombie
Cpu(s): 95.0%us,  0.8%sy,  0.0%ni,  3.1%id,  0.0%wa,  0.2%hi,  1.0%si,  0.0%st
Mem:   2057692k total,  2041768k used,    15924k free,    24996k buffers
Swap:  1764344k total,     8932k used,  1755412k free,   573460k cached
 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1308 mysql     20   0 4016m 1.3g 7656 S  199 65.9  61:03.26 mysqld
Et voici le my.cnf
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
[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
#
 
#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
 
user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
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              = 1G
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 16
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 550
table_cache            = 1024
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 256M
#
# * 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
 
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
 
innodb_buffer_pool_size=2G
innodb_additional_mem_pool_size=50M
innodb_flush_log_at_trx_commit=2
 
[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/
J'ai activé le trace sur les slow query, cela me donne le résultat suivant :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
donnees@LNX64BDD:~$ mysqldumpslow /var/log/mysql/mysql-slow.log -s t -t 5 -i agefiph > slow.txt
 
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
donnees@LNX64BDD:~$ less slow.txt
Count: 8  Time=445.83s (3566s)  Lock=0.00s (0s)  Rows=1.0 (8), agefiph[agefiph]@[10.222.114.195]
  SELECT
  count( DISTINCT ezcontentobject_tree.node_id ) as count
  FROM
  ezcontentobject_tree,
  ezcontentobject,ezcontentclass
  , ezcontentobject_name
  ,ezcontentobject_link AS t0,ezcontentobject_link AS t1,ezcontentobject_link AS t2,ezcontentobject_link AS t3,ezcontentobject_link AS t4,ezcontentobject_link AS t5
  WHERE  ezcontentobject_tree.path_string like 'S' and
  ezcontentobject_tree.contentobject_id = t0.from_contentobject_id AND ezcontentobject_tree.contentobject_version = t0.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t1.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t1.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t2.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t2.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t3.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t3.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t4.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t4.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t5.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t5.from_contentobject_version AND  (t0.contentclassattribute_id=N AND t0.to_contentobject_id=N OR t1.contentclassattribute_id=N AND t1.to_contentobject_id=N OR t2.contentclassattribute_id=N AND t2.to_contentobject_id=N OR t3.contentclassattribute_id=N AND t3.to_contentobject_id=N OR t4.contentclassattribute_id=N AND t4.to_contentobject_id=N OR t5.contentclassattribute_id=N AND t5.to_contentobject_id=N) AND
  ezcontentobject_tree.node_id = ezcontentobject_tree.main_node_id AND
  ezcontentobject.contentclass_id  IN  ( N ) AND
  ezcontentclass.version=N AND
  ezcontentobject_tree.node_id != N AND
  ezcontentobject_tree.contentobject_id = ezcontentobject.id  AND
  ezcontentclass.id = ezcontentobject.contentclass_id AND
  ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
  ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and
  ( ezcontentobject_name.language_id & ezcontentobject.language_mask > N AND
  ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & N )
  + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & N ) )
  <
  ( ezcontentobject_name.language_id & N )
  + ( ( ezcontentobject_name.language_id & N ) )
  )
  AND ezcontentobject_tree.is_invisible = N
  AND
  ezcontentobject.language_mask & N > N
 
Count: 8  Time=256.24s (2049s)  Lock=0.00s (0s)  Rows=1.5 (12), agefiph[agefiph]@[10.222.114.195]
  SELECT DISTINCT
  ezcontentobject.*,
  ezcontentobject_tree.*,
  ezcontentclass.serialized_name_list as class_serialized_name_list,
  ezcontentclass.identifier as class_identifier,
  ezcontentclass.is_container as is_container
  , ezcontentobject_name.name as name,  ezcontentobject_name.real_translation
  FROM
  ezcontentobject_tree,
  ezcontentobject,ezcontentclass
  , ezcontentobject_name
  ,ezcontentobject_link AS t0,ezcontentobject_link AS t1,ezcontentobject_link AS t2,ezcontentobject_link AS t3,ezcontentobject_link AS t4,ezcontentobject_link AS t5
  WHERE
  ezcontentobject_tree.path_string like 'S' and
  ezcontentobject_tree.contentobject_id = t0.from_contentobject_id AND ezcontentobject_tree.contentobject_version = t0.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t1.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t1.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t2.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t2.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t3.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t3.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t4.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t4.from_contentobject_version AND  ezcontentobject_tree.contentobject_id=t5.from_contentobject_id AND ezcontentobject_tree.contentobject_version=t5.from_contentobject_version AND  (t0.contentclassattribute_id=N AND t0.to_contentobject_id=N OR t1.contentclassattribute_id=N AND t1.to_contentobject_id=N OR t2.contentclassattribute_id=N AND t2.to_contentobject_id=N OR t3.contentclassattribute_id=N AND t3.to_contentobject_id=N OR t4.contentclassattribute_id=N AND t4.to_contentobject_id=N OR t5.contentclassattribute_id=N AND t5.to_contentobject_id=N) AND
  ezcontentclass.version=N AND
  ezcontentobject_tree.node_id != N AND
  ezcontentobject_tree.contentobject_id = ezcontentobject.id  AND
  ezcontentclass.id = ezcontentobject.contentclass_id AND
   ezcontentobject_tree.node_id = ezcontentobject_tree.main_node_id AND
  ezcontentobject.contentclass_id  IN  ( N ) AND
  ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
  ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and
  ( ezcontentobject_name.language_id & ezcontentobject.language_mask > N AND
  ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & N )
  + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & N ) )
  <
  ( ezcontentobject_name.language_id & N )
  + ( ( ezcontentobject_name.language_id & N ) )
  )
  AND ezcontentobject_tree.is_invisible = N
  AND
  ezcontentobject.language_mask & N > N
  ORDER BY ezcontentobject.published DESC
  LIMIT N, N
 
Count: 6  Time=37.68s (226s)  Lock=0.00s (0s)  Rows=0.0 (0), agefiph[agefiph]@[10.222.114.195]
  INSERT INTO ezsearch_tmp_0 SELECT DISTINCT ezsearch_object_word_link.contentobject_id, ezsearch_object_word_link.published
  FROM ezcontentobject,
  ezsearch_object_word_link
  ,
  ezcontentclass,
  ezcontentobject_tree
  WHERE
  ezsearch_object_word_link.contentclass_id IN ( N ) AND
  ezcontentobject.id=ezsearch_object_word_link.contentobject_id and
  ezcontentobject.contentclass_id = ezcontentclass.id and
  ezcontentclass.version = 'S' and
  ezcontentobject.id = ezcontentobject_tree.contentobject_id and
  ezcontentobject_tree.node_id = ezcontentobject_tree.main_node_id
  AND ezcontentobject_tree.is_invisible = N
 
Count: 1  Time=148.01s (148s)  Lock=0.00s (0s)  Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost
  select count(*) into @discard from `information_schema`.`PARTITIONS`
 
Count: 1  Time=66.86s (66s)  Lock=0.00s (0s)  Rows=49.0 (49), debian-sys-maint[debian-sys-maint]@localhost
  select concat('S',
  TABLE_SCHEMA, 'S', TABLE_NAME, 'S')
  from information_schema.TABLES where ENGINE='S'
Si quelqu'un a des pistes ou des actions à mener pour identifier le problème, je dois avouer que cela m'aiderait beaucoup...

Cactus.