MySQL Forums
Forum List  »  MyISAM

Re: one big table or one million tables
Posted by: James Day
Date: October 16, 2011 07:25AM

Those row counts are low so the count alone isn't a problem.

Forget the idea of 200,000 to a million tables. It's such a horrible idea that even the table cache that holds open tables doesn't allow caching more than 150,000 and even that is usually far too many.

Partition by user ID, perhaps ten partitions total, not hundreds, to help speed up some of the work with the table and reduce locking. But it seems to be so small, at least in row count, that it's unlikely to be very problematic.

If the data in each row is large do be sure that you only store in the most table the information that every or almost every query needs. It's a bad idea usually to store things like say full name and address in the main user table. That sort of thing tends to increase the working set size and difficulty of caching the whole working set. The same can apply to the multiple rows that you're creating for each user if many queries don't need all of the data in every row.

It's worth considering that there are places out there that have hundreds of gigabytes of InnoDB buffer pool and 32-64 gigabytes is becoming common. If all of your data fits in say sixteen gigabytes it'd be a pretty small data set by today's standards, and easy to cache.

Create as many indexes as you need to make the reports fast.

If you think that you might eventually store more data than one server can handle you might consider storing for each user a logical server ID for the server that holds their data. Then you can partition based on user, looking up the server you need to query before doing the query for that user's data. You could also do things like migrating inactive users to servers dedicated to that job, or varying how many users are on a server depending on how active they are.

If you have some big data, say passport pictures, you could consider storing those on a dedicated server with cheap but slow storage so that the primary server isn't burdened with storing bulk but seldom accessed data.

Options: ReplyQuote


Subject
Views
Written By
Posted
4242
September 21, 2011 07:22AM
1915
September 22, 2011 03:33PM
Re: one big table or one million tables
2847
October 16, 2011 07:25AM


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.