J'ai de gros problèmes avec MySQL sur un serveur dédié.
La config du serveur : Celeron 2.6, 512 MO
Exemple de processlist :
CITATION
upprimer 3331 monforum localhost monforumV2 Query 8 Locked UPDATE phpbb_posts SET post_username = '',
enable_bbcode =1,
enable_html =1,
enable_smilies =1,
e
Supprimer 3450 monforum localhost monforumV2 Query 8 Sending data SELECT COUNT( post_id ) AS total_posts
FROM phpbb_posts
WHERE forumid =6418
Supprimer 3517 monforum localhost monforumV2 Query 11 Sending data SELECT COUNT( post_id ) AS total_posts
FROM phpbb_posts
WHERE forumid =6149
Supprimer 3582 monforum localhost monforumV2 Query 8 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3589 monforum localhost monforumV2 Query 0 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3591 monforum localhost monforumV2 Query 7 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3594 monforum localhost monforumV2 Query 7 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3598 monforum localhost monforumV2 Query 3 Locked SELECT t.forum_id, t.topic_id, p.post_time
FROM phpbb_topics t, phpbb_posts p
WHERE
Supprimer 3600 monforum localhost monforumV2 Query 7 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3601 monforum localhost monforumV2 Query 8 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3607 monforum localhost monforumV2 Query 1 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3609 monforum localhost monforumV2 Query 3 Locked SELECT f . * , t.topic_id, t.topic_status, t.topic_type, t.topic_first_post_id, t.topic_last_post_id, t
Supprimer 3610 monforum localhost monforumV2 Query 0 Sending data SELECT *
FROM phpbb_portal_links
WHERE forumid =5213
AND link_active =1
Supprimer 3611 monforum localhost monforumV2 Query 0 statistics SELECT *
FROM phpbb_portal_struct s
LEFT JOIN phpbb_portal_mod m ON m.mod_id = s.mod_idWHE
Supprimer 3612 monforum localhost monforumV2 Query 3 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
Supprimer 3614 monforum localhost monforumV2 Query 1 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3615 monforum localhost monforumV2 Query 2 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
Supprimer 3617 monforum localhost monforumV2 Query 1 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3618 monforum localhost monforumV2 Query 2 Locked SELECT t . * , u.username, u.user_id, u2.username AS user2, u2.user_id AS id2, p.post_time, p.post_user
Supprimer 3619 monforum localhost monforumV2 Query 0 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
Supprimer 3621 pma localhost aucune Sleep 2 --- ---
Supprimer 3622 root localhost mysql Query 0 --- SHOW PROCESSLIST
Supprimer 3624 monforum localhost monforumV2 Query 0 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
enable_bbcode =1,
enable_html =1,
enable_smilies =1,
e
Supprimer 3450 monforum localhost monforumV2 Query 8 Sending data SELECT COUNT( post_id ) AS total_posts
FROM phpbb_posts
WHERE forumid =6418
Supprimer 3517 monforum localhost monforumV2 Query 11 Sending data SELECT COUNT( post_id ) AS total_posts
FROM phpbb_posts
WHERE forumid =6149
Supprimer 3582 monforum localhost monforumV2 Query 8 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3589 monforum localhost monforumV2 Query 0 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3591 monforum localhost monforumV2 Query 7 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3594 monforum localhost monforumV2 Query 7 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3598 monforum localhost monforumV2 Query 3 Locked SELECT t.forum_id, t.topic_id, p.post_time
FROM phpbb_topics t, phpbb_posts p
WHERE
Supprimer 3600 monforum localhost monforumV2 Query 7 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3601 monforum localhost monforumV2 Query 8 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3607 monforum localhost monforumV2 Query 1 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3609 monforum localhost monforumV2 Query 3 Locked SELECT f . * , t.topic_id, t.topic_status, t.topic_type, t.topic_first_post_id, t.topic_last_post_id, t
Supprimer 3610 monforum localhost monforumV2 Query 0 Sending data SELECT *
FROM phpbb_portal_links
WHERE forumid =5213
AND link_active =1
Supprimer 3611 monforum localhost monforumV2 Query 0 statistics SELECT *
FROM phpbb_portal_struct s
LEFT JOIN phpbb_portal_mod m ON m.mod_id = s.mod_idWHE
Supprimer 3612 monforum localhost monforumV2 Query 3 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
Supprimer 3614 monforum localhost monforumV2 Query 1 Locked SELECT u.username, u.user_id, p . * , pt.post_text, pt.post_subject, pt.bbcode_uid
FROM phpbb_posts
Supprimer 3615 monforum localhost monforumV2 Query 2 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
Supprimer 3617 monforum localhost monforumV2 Query 1 Locked SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u
Supprimer 3618 monforum localhost monforumV2 Query 2 Locked SELECT t . * , u.username, u.user_id, u2.username AS user2, u2.user_id AS id2, p.post_time, p.post_user
Supprimer 3619 monforum localhost monforumV2 Query 0 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
Supprimer 3621 pma localhost aucune Sleep 2 --- ---
Supprimer 3622 root localhost mysql Query 0 --- SHOW PROCESSLIST
Supprimer 3624 monforum localhost monforumV2 Query 0 Locked SELECT f . * , p.post_time, p.post_username, u.username, u.user_id, t.topic_id, t.topic_title
FROM
J'ai des "grosses" tables, au total près de 7 000 000 d'enregistrements pour 500 Mo.
Il y a de grosses requetes, qui ne prennent quasi pas de temps quand il y a personne sur le site, mais dès qu'il y a un peu de load elles durent longtemps et s'ajoutent dans le processlist mysql et viennent saturer l'ensemble.
Voici ma config mysql :
CODE
port = 3306
socket = /var/lib/mysql/mysql.sock
query_cache_size = 41984
#skip-external-locking
#skip-innodb
#key_buffer_size = 256M
#max_allowed_packet = 1M
#table_cache = 2048
#sort_buffer_size = 16M
#read_buffer_size = 16M
#thread_cache_size = 8
#thread_concurrency = 8
#myisam_sort_buffer_size = 64M
#connect_timeout = 10
max_connections = 200
connect_timeout = 5
#long_query_time = 30
wait_timeout=5
#interactive_timeout=60
#thread_stack = 128k
long_query_time = 3
log-slow-queries = /var/log/mysql/slow_queries.log
log-error = /var/log/mysql/error.loglog-error = /var/log/mysql/error.log
#max_allowed_packet = 500M
socket = /var/lib/mysql/mysql.sock
query_cache_size = 41984
#skip-external-locking
#skip-innodb
#key_buffer_size = 256M
#max_allowed_packet = 1M
#table_cache = 2048
#sort_buffer_size = 16M
#read_buffer_size = 16M
#thread_cache_size = 8
#thread_concurrency = 8
#myisam_sort_buffer_size = 64M
#connect_timeout = 10
max_connections = 200
connect_timeout = 5
#long_query_time = 30
wait_timeout=5
#interactive_timeout=60
#thread_stack = 128k
long_query_time = 3
log-slow-queries = /var/log/mysql/slow_queries.log
log-error = /var/log/mysql/error.loglog-error = /var/log/mysql/error.log
#max_allowed_packet = 500M
J'avoue ne pas trop savoir comment parramétrer MySQL... Avez vous des conseils à me donner ?
Quel peut être mon problème ?
Merci bcp