List:General Discussion« Previous MessageNext Message »
From:Steve Musumeche Date:February 4 2011 7:21pm
Subject:Re: Table/select problem...
View as plain text  
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
steve@stripped
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)
>
>
Thread
Table/select problem...Andy Wallace4 Feb
  • Re: Table/select problem...Steve Musumeche4 Feb
    • Re: Table/select problem...Johan De Meersman4 Feb
      • Re: Table/select problem...Andy Wallace4 Feb
  • RE: Table/select problem...Travis Ard5 Feb