I had this same issue a while back and solved it by writing my events to
a disk-based file and periodically importing them into the event log
MyISAM table. This way, even if your select statements lock the table,
it won't affect the performance of your application. Of course, this
may require some rewriting of your application code, depending on how
events are logged.
You could avoid the locking with InnoDB, but I did not choose that
solution because MyISAM seems like a better fit for a logging situation,
and they can later be used in Merge tables. I wonder if any others have
used InnoDB for large logging tables and what the performance has been?
Steve Musumeche
CIO, Internet Retail Connection
st...@internetretailconnection.com
1-800-248-1987 ext 802
On 2/4/2011 11:29 AM, Andy Wallace wrote:
Greetings, all...
I'm having an issue with a SELECT in our system. We have an event log
table,
with about 9 million rows in it. Inserts happen with some pretty high
frequency,
and these selects happen periodically. The event_log table is MyISAM,
the
rest of the tables are InnoDB.
What's happening is that, periodically, when this select gets run, the
whole
damn thing locks up, and that pretty much shuts us down (since many
things
insert events into the table, and the table gets locked, so all the
inserts
hang).
The statement and the explain for it are below. the enduser table has
about a
million rows in it, the event_type table 35 rows. The weird part is
that, if
I strip down the query to use no joins, the explain wants to return
about 17,000
rows, but the query itself does the table locking thing.
Should we perhaps change the event log to InnoDB to avoid table
locking? Might
the table itself be corrupt in some way? Any thoughts?
thanks,
andy
EXPLAIN
SELECT EL.event_log_id,
EL.event_time,
DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as
'time_formatted',
ET.event_type_id,
ET.description,
EL.csr_name,
EL.enduser_acnt,
EL.csr_name,
EL.referer,
EL.mls_id,
EL.mls_no,
EL.ss_id,
EL.details,
E.fname,
E.lname,
E.email,
E.phone1
FROM event_log EL
JOIN event_type ET ON EL.event_type_id = ET.event_type_id
JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt
WHERE EL.acnt = 'AR238156'
AND EL.enduser_acnt != ''
AND EL.event_type_id = 'EndUserLogin'
AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW()
ORDER BY EL.event_time DESC
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ET
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 92
ref: const
rows: 1
Extra: Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: EL
type: index_merge
possible_keys: agent,enduser,event_log_ibfk_1
key: agent,event_log_ibfk_1
key_len: 62,92
ref: NULL
rows: 1757
Extra: Using intersect(agent,event_log_ibfk_1); Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: E
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: idx_acnt.EL.enduser_acnt
rows: 1
Extra: Using where
3 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
