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" <brentt...@gmail.com>
To: "Carl" <c...@etrak-plus.com>
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 <c...@etrak-plus.com> 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" <brentt...@gmail.com>
To: "Carl" <c...@etrak-plus.com>
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 <c...@etrak-plus.com>:
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" <ba...@xaprb.com>
To: "Brent Baisley" <brentt...@gmail.com>
Cc: "Carl" <c...@etrak-plus.com>; <mysql@lists.mysql.com>
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 <brentt...@gmail.com>
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=c...@etrak-plus.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
