lock query

[tom Pasto]

Bonjour,

Aujourd'hui j'ai eu un incident sur mon serveru mysql (Ver 14.12 Distrib 5.0.38) et je voudrais vos avis éclairés pour éviter que cela se reproduise.

J'ai un serveur mysql-apache sur un Bi-Xeon quad-core, 4go de mémoire et 100Go de disuqe libre.

Une requête apparament assez importante à apparement générer un grand nombre de tables temporaires. Ce fesant cette requête à monopolisé tout le serveur mysql et locker toute les autres requêtes entrant.
Le serveur à litéralement gelé.
Comment pus je setter ce serveur pour tuer automatiquement un processus trop long.

voici le SHOW PROCESS lorsque le seveur à geler:

Id | User | Host | db | Command | Time | State | Info |

+--------+---------------+-----------+------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+

| 303943 | MedentWebUser | localhost | Evaltest | Killed | 88 | removing tmp table | Select * from Session, trs_fact where Session.id = trs_fact.session_id and trs_fact.trs_daa_id is nu |

| 303994 | MedentWebUser | localhost | Evaluation | Query | 81 | update | INSERT INTO Spool (NoFiche,date,moment,place,activite,multi,position)

values (NULL,'2007-10-1 |

| 303997 | MedentWebUser | localhost | Evaluation | Query | 78 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304001 | MedentWebUser | localhost | Evaluation | Query | 71 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304003 | MedentWebUser | localhost | Evaluation | Query | 70 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304005 | MedentWebUser | localhost | Evaluation | Query | 69 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304022 | MedentWebUser | localhost | Evaluation | Query | 55 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304030 | MedentWebUser | localhost | Evaluation | Query | 52 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304031 | MedentWebUser | localhost | Evaluation | Query | 51 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304038 | MedentWebUser | localhost | Evaluation | Query | 49 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304039 | MedentWebUser | localhost | Evaluation | Query | 49 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304042 | MedentWebUser | localhost | Evaluation | Query | 49 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304043 | MedentWebUser | localhost | Evaluation | Query | 47 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304046 | MedentWebUser | localhost | Evaluation | Query | 45 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304059 | MedentWebUser | localhost | Evaluation | Query | 32 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304061 | MedentWebUser | localhost | Evaluation | Sleep | 15 | | |

| 304062 | MedentWebUser | localhost | Evaluation | Query | 15 | update | INSERT INTO Paiement

(

idpat,

commis,

date,

amount,

mode,

child_ |

| 304064 | MedentWebUser | localhost | Evaluation | Query | 13 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304072 | MedentWebUser | localhost | Evaluation | Query | 1 | Locked | select count(S.form_id) from Spool Sp,Session S

where Sp.date < DATE_SUB(now(), INTERVAL 7 |

| 304073 | root | localhost | | Query | 0 | | show processlist


Voici mon my.cnf:



[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
.
[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
language = /usr/share/mysql/english
skip-external-locking

#
# * Fine Tuning
#
key_buffer = 64M
sort_buffer = 2M
max_allowed_packet = 32M
thread_stack = 128K
thread_cache_size = 8
#max_connections = 100
table_cache = 300
read_buffer = 4M
tmp_table_size = 128M

query_cache_limit = 2M
query_cache_size = 32M

log = /var/log/mysql/mysql.log

expire_logs_days = 10
max_binlog_size = 100M

#
# * 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!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:1024M:autoextend
set-variable = innodb_buffer_pool_size=800M
set-variable = innodb_additional_mem_pool_size=60M
innodb_flush_log_at_trx_commit=1





[mysqldump]
quick
quote-names
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 16M


!includedir /etc/mysql/conf.d/