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.
----- 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
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).
On Thu, Mar 5, 2009 at 6:02 AM, Carl <carl@stripped> wrote:
> 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
> more than the dates I have asked for.)
> SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary
> (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
> 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
> rather just shorten the time of the locks. I am not suggesting that I
> not end up there, only hoping for a better solution.
> Thanks for all your insight and feel free to suggest away.
> ----- 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.
> 2009/3/4 Carl <carl@stripped>:
>> Under stress (having transaction entered), the query shows that it is
>> 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
>> Monitor in Navicat.
>> Explain (copy as text and copy as Excel attached) seems to indicate that
>> is fairly good although the first step does get quite a few rows.
>> Does anyone have any ideas?
>> ----- 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>
>>>> 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
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
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
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
1 SIMPLE journal_entry_master ref PRIMARY,organization,journal_entry_type_serial,date_effective organization 4 const 407484 Using
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
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
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