List:General Discussion« Previous MessageNext Message »
From:Carl Date:March 12 2009 4:50pm
Subject:Re: Select query locks tables in Innodb
View as plain text  
Brent,

After a delay while I was busy killing alligators, I did as you suggested 
(added a composite index of date and organization_serial on 
journal_entry_master... in the spirit of your suggestion, anyway.)  The 
results were interesting:

1.  In my test environment, I could not force a locked file even though I 
opened the dates up to cover 2+ years and changed to an organization that 
had more records.  The 'Explain' is attached as temp1.txt.  You will note 
that it starts with 100,000+ records while the eventual set of records for 
the report is 60,000 because the 100,000+ number includes some journmal 
entries for refund/void/etc. transactions which we have no interest in.

2.  I tried various combinations of indexes but couldn't seem to get any 
better than the composite one on the journal_entry_master.  I did not check 
whether the other options would produce locked files.

I am now going to put this into production and see if it will actually fly.

I am still a little puzzled about how we could have a relatively large set 
of records (100,000+) and yet not cause any table to be locked as the server 
has only 8GB of memory.

Thanks for all your help and Baron's suggestions also.

Carl





----- Original Message ----- 
From: "Brent Baisley" <brenttech@stripped>
To: "Carl" <carl@stripped>
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb


Ok, so you have 687 unique organization serial numbers. That's not
very unique, on average it will only narrow down the table to 1/687 of
it's full size. This is probably the source of your locking problem
and where you want to focus.
InnoDB will lock on a query that doesn't use an index. It would have
to lock every record anyway, so why not lock the table?
36,000 records still may be too large of a result set to do record
versioning. But, optimizing your query is the only way to go.

Your date_effective is a lot more granular, so you may want to focus
on that. If you do a lot of these types of searches, you can try
creating a compound index on organization_serial+date_effective.
CREATE INDEX (org_date) ON
journal_entry_master(organization_serial,date_effective)

MySQL would/should then use that query, which will narrow things down
quicker and better. It shouldn't have to try to do versioning on
56,000 records while it tries to get the subset of that (36,000).

Brent

On Thu, Mar 5, 2009 at 6:02 AM, Carl <carl@stripped> wrote:
> Brent,
>
> The query returns about 36,000 rows. The 56,000 rows from the
> journal_entry_master table is all the entries for organization 16 (they 
> span
> more than the dates I have asked for.)
>
> SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary 
> index
> (auto-increment), 687 for the organization_serial (the one I am using), 18
> for the organization_shift_start (I tried this before, i.e., starting with
> the organization_shift, but it quickly got mired down) and 777,000+ for 
> the
> date_effective.
>
> If I understand correctly, you have suggested using the date index. The
> difficulty is the data contains many organizations and so the date range
> query returns 163,000+ rows.
>
> Also, I would expect scaling a query where I had to programatically cut it
> up would 1) be difficult and 2) wouldn't really solve the problem but 
> would
> rather just shorten the time of the locks. I am not suggesting that I 
> might
> not end up there, only hoping for a better solution.
>
> Thanks for all your insight and feel free to suggest away.
>
> Carl
>
> ----- Original Message ----- From: "Brent Baisley" <brenttech@stripped>
> To: "Carl" <carl@stripped>
> Sent: Wednesday, March 04, 2009 4:23 PM
> Subject: Re: Select query locks tables in Innodb
>
>
> Is the result of the query returning 56,000+ rows? How many rows are
> you expecting to be returned once the query is finished running?
> Your date range is over a year. You may actually get much better
> performance (and avoid locking) by running more queries with a
> narrower date range and linking them through a UNION. It's using the
> organization index rather than the date index.
> I don't know your dataset, but typically you want your query to use
> the date index since that narrows down the data set better.
>
> You can run SHOW INDEX FROM journal_entry_master to see the
> distribution of your data in the index. The cardinality column will
> indicate the uniqueness of your data. The higher the number, the more
> unique values.
>
> Brent
>
> 2009/3/4 Carl <carl@stripped>:
>>
>> Under stress (having transaction entered), the query shows that it is
>> still
>> locking the tables. I rewrote the query and tested it step by step but
>> could not tell whether tyhe partially complete query was locking tables
>> because it ran so fast. However, when I had all the pieces in the query
>> (copy attached), I could easily see it was locking tables using the 
>> Server
>> Monitor in Navicat.
>>
>> Explain (copy as text and copy as Excel attached) seems to indicate that
>> it
>> is fairly good although the first step does get quite a few rows.
>>
>> Does anyone have any ideas?
>>
>> TIA,
>>
>> Carl
>>
>>
>>
>> ----- Original Message ----- From: "Baron Schwartz" <baron@stripped>
>> To: "Brent Baisley" <brenttech@stripped>
>> Cc: "Carl" <carl@stripped>; <mysql@stripped>
>> Sent: Tuesday, March 03, 2009 5:50 PM
>> Subject: Re: Select query locks tables in Innodb
>>
>>
>>> On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley <brenttech@stripped>
>>> wrote:
>>>>
>>>> A SELECT will/can lock a table. It almost always does in MyISAM (no
>>>> insert/updates), almost never does in InnoDB. There is an exception to
>>>> every rule. The problem is most likely in the 107488 rows part of the
>>>> query. That's too many rows for InnoDB to keep a version history on so
>>>> it's likely just locking the table.
>>>
>>> InnoDB does not do lock escalation a la SQL Server etc. I'd look at
>>> Perrin's suggestions, I think they are likely to be the problem.
>>>
>>> More importantly, what is the query? :-)
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>
>
>

1	SIMPLE	journal_entry_master	range	PRIMARY,organization,journal_entry_type_serial,date_effective,org_date	org_date	12		41664	Using
where
1	SIMPLE	journal_entry_type	eq_ref	PRIMARY	PRIMARY	4	PRODUCTION.journal_entry_master.journal_entry_type_serial	1	
1	SIMPLE	payment_to_fee_link_budget_account_detail_link	ref	journal_entry,budget_account_detail_serial,event,date_effective	journal_entry	4	PRODUCTION.journal_entry_master.journal_entry_master_serial	1	Using
where
1	SIMPLE	ptfl_budget_account_detail	eq_ref	PRIMARY	PRIMARY	4	PRODUCTION.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial	1	
1	SIMPLE	payment_to_fee_link_event	eq_ref	PRIMARY,payment,transaction_event	PRIMARY	4	PRODUCTION.payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial	1	
1	SIMPLE	payment_to_fee_link	eq_ref	PRIMARY,fees	PRIMARY	4	PRODUCTION.payment_to_fee_link_event.payment_to_fee_link_serial	1	
1	SIMPLE	transaction_event	eq_ref	PRIMARY,receipt	PRIMARY	4	PRODUCTION.payment_to_fee_link_event.transaction_event_serial	1	
1	SIMPLE	receipt_master	eq_ref	PRIMARY	PRIMARY	4	PRODUCTION.transaction_event.receipt_serial	1	
1	SIMPLE	fees	eq_ref	PRIMARY,transactions	PRIMARY	4	PRODUCTION.payment_to_fee_link.fees_serial	1	
1	SIMPLE	transactions	eq_ref	PRIMARY,person	PRIMARY	4	PRODUCTION.fees.transactions_serial	1	
1	SIMPLE	person	eq_ref	PRIMARY	PRIMARY	4	PRODUCTION.transactions.person_serial	1	
1	SIMPLE	regs	ref	transaction	transaction	4	PRODUCTION.transactions.transactions_serial	1	
1	SIMPLE	fees_event	ref	PRIMARY,fees,event	fees	4	PRODUCTION.payment_to_fee_link.fees_serial	1	Using
where
1	SIMPLE	fees_budget_account_detail_link	ref	budget_account_detail_serial,fees_event	fees_event	4	PRODUCTION.fees_event.fees_event_serial	1	
1	SIMPLE	budget_account_detail	eq_ref	PRIMARY,budget_account	PRIMARY	4	PRODUCTION.fees_budget_account_detail_link.budget_account_detail_serial	1	
1	SIMPLE	budget_account_master	eq_ref	PRIMARY	PRIMARY	4	PRODUCTION.budget_account_detail.budget_account_serial	1	Using
where

1	SIMPLE	journal_entry_master	ref	PRIMARY,organization,journal_entry_type_serial,date_effective	organization	4	const	407484	Using
where
1	SIMPLE	payment_to_fee_link_budget_account_detail_link	ref	journal_entry,budget_account_detail_serial,event,date_effective	journal_entry	4	test.journal_entry_master.journal_entry_master_serial	1	Using
where
1	SIMPLE	ptfl_budget_account_detail	eq_ref	PRIMARY	PRIMARY	4	test.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial	1	
1	SIMPLE	payment_to_fee_link_event	eq_ref	PRIMARY,payment,transaction_event	PRIMARY	4	test.payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial	1	
1	SIMPLE	payment_to_fee_link	eq_ref	PRIMARY,fees	PRIMARY	4	test.payment_to_fee_link_event.payment_to_fee_link_serial	1	
1	SIMPLE	transaction_event	eq_ref	PRIMARY,receipt	PRIMARY	4	test.payment_to_fee_link_event.transaction_event_serial	1	
1	SIMPLE	receipt_master	eq_ref	PRIMARY	PRIMARY	4	test.transaction_event.receipt_serial	1	
1	SIMPLE	journal_entry_type	eq_ref	PRIMARY	PRIMARY	4	test.journal_entry_master.journal_entry_type_serial	1	
1	SIMPLE	fees	eq_ref	PRIMARY,transactions	PRIMARY	4	test.payment_to_fee_link.fees_serial	1	
1	SIMPLE	transactions	eq_ref	PRIMARY,person	PRIMARY	4	test.fees.transactions_serial	1	
1	SIMPLE	person	eq_ref	PRIMARY	PRIMARY	4	test.transactions.person_serial	1	
1	SIMPLE	regs	ref	transaction	transaction	4	test.transactions.transactions_serial	1	
1	SIMPLE	fees_event	ref	PRIMARY,fees,event	fees	4	test.payment_to_fee_link.fees_serial	1	Using
where
1	SIMPLE	fees_budget_account_detail_link	ref	budget_account_detail_serial,fees_event	fees_event	4	test.fees_event.fees_event_serial	1	
1	SIMPLE	budget_account_detail	eq_ref	PRIMARY,budget_account	PRIMARY	4	test.fees_budget_account_detail_link.budget_account_detail_serial	1	
1	SIMPLE	budget_account_master	eq_ref	PRIMARY	PRIMARY	4	test.budget_account_detail.budget_account_serial	1	Using
where

Thread
Select query locks tables in InnodbCarl3 Mar
  • Re: Select query locks tables in InnodbBrent Baisley3 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz3 Mar
  • Re: Select query locks tables in InnodbPerrin Harkins3 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbPerrin Harkins4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz5 Mar
  • Re: Select query locks tables in InnodbCarl5 Mar
  • Re: Select query locks tables in InnodbCarl12 Mar
    • Re: Select query locks tables in InnodbBrent Baisley12 Mar
    • Re: Select query locks tables in InnodbPerrin Harkins13 Mar
  • Re: <Solved> Select query locks tables in InnodbCarl25 Mar