MySQL Forums
Forum List  »  Partitioning

Re: Partitioning dilemma
Posted by: Rick James
Date: July 26, 2010 11:15PM

GUIDs are very random. If that table is 'big', this could lead to a lot of random disk I/O. (You imply that the session table is much bigger than cache.) This is important if the GUID is indexed -- it implies uncached I/O for every INSERT.

SHOW TABLE STATUS
will show how big the tables are.

Can't you add this to help with the check:
INDEX(external_trx_id)

What's the value of innodb_buffer_pool_size, and how much RAM do you have?

So far, it sounds like most of the activity is at the 'end' of your two big tables. (I assume you will switch from GUID to AUTO_INCREMENT.) This would imply that PARTITION won't buy you anything, and a simple PRIMARY KEY on the id would be quite efficient for both tables. (Plus the index on external_trx_id.)

A few hours versus 6 months -- sounds like the hot spot on Sessions is only the last 1% of the table. This should be easily cached (unless you are talking about many terabytes).

You may need another index on some kind of date -- this would help with the DELETE. Or, you could walk through the ids on the assumption that they are roughly date-ordered.

Options: ReplyQuote


Subject
Views
Written By
Posted
2750
July 20, 2010 08:51AM
1566
July 21, 2010 11:58AM
1441
July 21, 2010 01:47PM
Re: Partitioning dilemma
1540
July 26, 2010 11:15PM
1387
July 27, 2010 09:02AM
1454
July 27, 2010 08:06PM


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.