Table/select problem...

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)


--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Table/select problem...

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@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=gcdmg-mysql-2@m.gmane.org

Re: Table/select problem...

--0015174bdefa1f0dcd049b7a81a9
Content-Type: text/plain; charset=ISO-8859-1

Do you delete data from the table ?

MyISAM will only grant a write lock when there are no locks on the table -
including implicit read locks. That may be your problem.

There is a single situation when concurrent reads and writes are possible on
MyISAM, however: when your table has no holes in the data. At that point,
selects happen on the existing data, while the insert queue is handled
(sequentially) at the same time.

If that is indeed your problem, you may "fix" the table using OPTIMIZE
TABLE.

Two other options might be:

- set the variable concurrent_insert to 2 - this will allow concurrent
inserts at the end of the table even when there are holes. Downside is that
freed space (from deletes) is not reused.
- use INSERT DELAYED. Code returns immediately, but you have no way of
knowing wether or not any given insert succeeded.


If you delete data, but only relatively old data, you might also benefit
from partitioning the table: I'm not sure about this, but it seems
reasonable that concurrent updates would be possible on partitions with no
holes. Should try this sometime.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0015174bdefa1f0dcd049b7a81a9--

Re: Table/select problem...

Interesting... we have a process that runs once a night to delete old data (we are only
keeping events from the last 6 months).

And I believe you have described the primary issue exactly - the read is locking the
table, so the writes get blocked. All inserts do happen at the end of the table, but
since we do delete records, and usually several thousand at a time (delete from event_log
where date < some value), I don't think that the concurrent_insert option will work for
us, as we would like the space to be reused when possible.

I'll look into partitioning the table, thanks!

I'm still leaning towards changing the table to InnoDB to avoid the locking problem,
I made this one MyISAM because I thought that selects would be faster. But the main
issue is, we do a LOT of inserts (there are upwards of 50-100 inserts blocked when the
one select locks the table).

thanks! And thanks Steve, as well. I don't think that the periodic import of events
from a file will work for us, we need relatively timely reporting from the table. We
don't have a lot of hits for the event reports, but when we do get them, they want the
data up-to-date...

andy


On 2/4/11 12:12 PM, Johan De Meersman wrote:
> Do you delete data from the table ?
>
> MyISAM will only grant a write lock when there are no locks on the table - including
> implicit read locks. That may be your problem.
>
> There is a single situation when concurrent reads and writes are possible on MyISAM,
> however: when your table has no holes in the data. At that point, selects happen on
> the existing data, while the insert queue is handled (sequentially) at the same time.
>
> If that is indeed your problem, you may "fix" the table using OPTIMIZE TABLE.
>
> Two other options might be:
>
> * set the variable concurrent_insert to 2 - this will allow concurrent inserts at
> the end of the table even when there are holes.
> Downside is that freed space (from deletes) is not reused.
> * use INSERT DELAYED. Code returns immediately, but you have no way of knowing wether
> or not any given insert succeeded.
>
> If you delete data, but only relatively old data, you might also benefit from partitioning
> the table: I'm not sure about this, but it seems reasonable that concurrent updates would
> be possible on partitions with no holes. Should try this sometime.
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel

--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Table/select problem...

What columns do you have indexed on your event_log table? Can you post the
output from SHOW CREATE TABLE? How long does the query run for?

-----Original Message-----
From: Andy Wallace [mailto:awallace@ihouseweb.com]
Sent: Friday, February 04, 2011 10:29 AM
To: mysql list
Subject: Table/select problem...

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)


--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_ard@hotmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org