It appears that every time I start query the event or the data table
gets Locked.
Could this have any affect on why it takes so long to delete records.
Grasping at straws,
Thank you,
Raymond
mysql> show processlist;
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| 1 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62693 |snort | Query |
1555 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305568',
'12', '2006-07-10 11:48:22. |
| 542 | root | localhost |snort | Query
| 1555 | Sending data | DELET
E
data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp < "2006-04-01"
|
| 543 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62680 | snort_archive | Sleep
| 1555 | | NULL
|
| 544 | root | localhost |snort | Query
| 0 | NULL | show
processlist
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--processlist
|
| 545 | root | localhost |snort | Query
| 308 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp < '2006-03-03'
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
4 rows in set (0.00 sec)
mysql> show processlist;
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| 1 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62693 |snort | Query |
333 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62680 | snort_archive | Sleep
| 333 | | NULL
|
| 544 | root | localhost |snort | Query
| 0 | NULL | show
processlist
|
| 545 | root | localhost |snort | Query
| 333 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp < '2006-03-03'
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
4 rows in set (0.00 sec)
mysql> show processlist;
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| 1 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62693 |snort | Query |
337 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62680 | snort_archive | Sleep
| 337 | | NULL
|
| 544 | root | localhost |snort | Query
| 0 | NULL | show
processlist
|
| 545 | root | localhost |snort | Query
| 337 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp < '2006-03-03'
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
4 rows in set (0.01 sec)
....
mysql> show processlist;
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+-----+-------+---------------------------------------------------------
----------------------------------------------------------+
| 1 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62693 |snort | Query |
1627 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62680 | snort_archive | Sleep
| 1627 | | NULL
|
| 545 | root | localhost |snort | Query
| 1627 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp < '2006-03-03'
|
| 546 | root | localhost | NULL |
Query | 0 | NULL | show
processlist
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
4 rows in set (0.00 sec)
....
mysql> show processlist;
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| 1 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62693 |snort | Query |
3955 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62680 | snort_archive | Sleep
| 3955 | | NULL
|
| 545 | root | localhost |snort | Query
| 3955 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp < '2006-03-03'
|
| 546 | root | localhost | NULL |
Query | 0 | NULL | show
processlist
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
4 rows in set (0.00 sec)
mysql> show processlist;
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| 1 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62693 |snort | Query |
3958 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62680 | snort_archive | Sleep
| 3958 | | NULL
|
| 545 | root | localhost |snort | Query
| 3958 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp < '2006-03-03'
|
| 546 | root | localhost | NULL |
Query | 0 | NULL | show
processlist
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
4 rows in set (0.00 sec)
mysql> show processlist;
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+-----+-------+-----------------------------------+---------------+-----
----+------+--------------+------
------------------------------------------------------------------------
------------------------+
| 1 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62693 |snort | Query |
3960 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxxxxxxxxxxxxxxxxxxxxxxxxxx:62680 | snort_archive | Sleep
| 3960 | | NULL
|
| 545 | root | localhost | snort |
Query | 3960 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp < '2006-03-03'
|
| 546 | root | localhost | NULL |
Query | 0 | NULL | show
processlist
|
+-----+-------+-----------------------------------+---------------+-----
----+------+-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
