MySQL Forums
Forum List  »  Partitioning

Re: How should I partition this table?
Posted by: Rick James
Date: May 03, 2013 09:00AM

> Would it be ok to send you a sample of the select statements priavately?

584886 (at) rjweb.org

> Having them in an open forum might cause issues on this end.

Or it might provide ammunition in the battle. Some companies think it is a great idea to split duties between developers and system maintainers. At some level it is a good idea. For small databases, it does not matter. For a billion rows, any performance "fix" is likely to involve multiple groups.

One group says "faster hardware is the solution"; so they get approval for the money, expend the effort on migrating the data, only to find that that was just a partial solution.

The next group says "tune it". Well, that takes care of only a few aspects. And, again, it is only a partial solution.

The developers are likely to be the most reluctant to work on the problem, partially because it (usually) takes more effort. The simple act of normalizing causes upheaval in multiple spots in their code. So would combining a DATE & TIME into a single field.

One anecdote: The CPU was running constantly at 40%. No hardware or tuning would make much difference. Changing
WHERE DATE(dt) = '2011-11-11'
into
WHERE dt >= '2011-11-11' AND dt < '2011-11-11' + INTERVAL 1 DAY
brought the CPU down to 2%.
The developers did not baulk at the one-line change that I handed them. (Your problem is much more than a 1-liner.)

Another: "Please tune this system so this online query won't take an hour." Well, much _coding_ later, it was down to 2 seconds, but it involved changing the table definitions, revamping the data-loading code, creating summary tables, and rewriting the UI. Hardware and tuning were insignificant contributors to the improvement. (This involved a dataset about as big as yours.)

Options: ReplyQuote


Subject
Views
Written By
Posted
2973
April 26, 2013 09:18AM
Re: How should I partition this table?
1323
May 03, 2013 09:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.