New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Comments
Partition may help
Partitioning is an idea. Can accomplish much of the same effect without MySQL's lousy description in documentation of partitioning:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html
What is the purpose of this? If you need to read data all together (for stats or whatever) you're gonna need everything so what changes from having it all in one table or split in multiple tables? Well unless you have them on different servers/hdd..
I'm talking about the ID to store activities. Not the user/member id.
I just performed a test. I tried to query the same database with the same query once on a SATA HDD and once on my SSD Samsung 830. The SSD is slower.
HDD takes 5.70sec the SSD 6.35 sec. I'm taking about 90M rows.
Suitable key is the userid that should appear in that activity table. user.userid = activities.userid. Something like that.
The user/member ID should be the primary key throughout this. Additional keys will depend on your data and needs (date often used especially where updates aren't made to tables and instead we grab last value with most recent date)
90M rows? How big is the data set on disk? And how big is MySQL in RAM at that point?
Have you optimized MySQL to use as much RAM as possible?
Usually when a harddrive and SSD have similar time like that, the issue is MySQL itself. You want to get as much of indexes RAM resident.
The 830's are about as good as it gets. Not the fastest drives on earth at this point, but they are true, tried and tested.
Segment however you can. Think about isolating users to reduce your database size, at least reducing the number of records MySQL has to root through to find matches of data for that user.
A simple segmentation to stop this silly growth or quarantine it a bit would be to generate a value at random from 1-10. That random value store in user database. That gets used to say which tablespace for the activities where the user's data gets stored/is stored.
In doing that simple little thing you take that 1 million rows of table globbed into 1 table and you divided up ideally near evenly. So daily growth of records = 100,000 per user table space. Or 1/10th of total.
Even at month 30 million records in your big uni-table slop heap. Or 3 million my way.
Now I am sure you can come up with 5 reasons why this approach doesn't fit.
But I see 5 seconds for a query with 90 million records so far in your testing. That's unacceptably high. If you had 10 tables dividing the load you would have 9 million rows per user space. Query ideally scales down just the same, so you go from 5 seconds to 500 milliseconds. Big improvement. Still slow as it gets though.
Talking about stats and mining the data later, those are reports and come after the front end user experience. Stats tend to take a bunch of thought and engineering. Create this for the front end users and make it fast. Total page delivery in under 500ms all inclusive of queries, page assembly, internet lagging, etc.
I understand your approach, I find very interesting your idea about segmenting users to a specific table with a random number! Even though you can get into a case where one table becomes enormous and another is unused depending on the users assigned to a specific "segment".
Now that I think about it... do you think that having a table per user is bad practice? In a simple app this could be OK but I can think of many reasons why this will make things worse.
I am only familiar with partitions in Oracle. But I seem to read they also have in MySQL. It will greatly reduced IO, provided the application takes advantage of it.
@sandro, small things help immensely with big data piles like these.
Divide the data to conquer it
Good thing about the random number break up of the data is that it scales, is portable to other databases and fairly easy to manage long term. Most things are just scheduled jobs with a loop increment from 1-10 to perform the same process across the tables (say in case of long term archiving to a big table that is not really used much).
A table per user shouldn't be tried. At least not with current database solutions. It would be a massive mess to manage and likely break many common tools, logging in, etc. Anything that would get the table list would get this 10k table names returned. Not good.
My mom and I have a store and right now with our 11000+ items in it database doesn't really break a sweat.
Wait, so you actually mean to save each user data into multiple segmented tables? I thought ONE random table assigned per user (even if the the table holds multiple users).
BTW I wonder what kind of server I would need when I reach 100M rows total, I see that 90M only is already 2GB..imagine loading 2GB of data into PHP aka into the RAM!
Let's draw this out a bit:
(this is informal and illustrative and won't work as is... purely demonstrative on howto)
Table: users
Fields: id (int),prefix(int),username(varchar),email(varchar),etc.
*** prefix field is a random 1-10 number for each record ***
Table: activities[1-10]
Fields: id(int),userid(int),data_to_log
Get a given user:
select id, prefix, username from users where email = '[email protected]'
Get a given users activities:
select id,userid,data_to_log from activities.[user.prefix] where users.id = activities.userid
All big data needs partitioning. Either built in type in your database software or an improvised one with different table spaces broken up as part of the schema.
Note, the users activity data gets put in the same table space each and every time. That is determined by the prefix value we specify when the user is created. That field could be an autoincrement that automatically fills the value or you can just randomize the value at account creation. Either should work just fine.
Nothing stopping you from having more of these tables.
Me, I'd create a 36 tables probably for the activity data. I'd probably base that simply on 26 letters + 10 numbers. That's simple enough for tracking, keeping healthy, not being overwhelmed by shear number of tables, etc.
36 activity tables takes that 1 million rows and divides it up into 27,700+ rows per table. 30 days of activity is around 831k rows of data per table. Or less than 1 day's amount it chunking it all into one table
That should scale very well and allow for many months of storage per table before sweeping older data out for archiving.
Try simulating this with your data and see how the query times come WAY down.
BTW: @sandro, what sort of server are you intending on running this all on?
@pubcrawler at the moment I have no idea, really. I want to start on a VPS cause with a few users I would have none of these problems.
Your analysis is exactly what you I wanted to do.
I actually wanted to go with another approach: use a real time table daily and then archive the new data to the archive and start again. But this will put me in a situation where I have to query a gigantic table so I'm back to problem 1.
Your idea has one flaw based on how this app works. Those activities are based on what the user does so some might do millions of rows per week while others zero, this randomization could put two or more very active users on the same table and that table could become problematic.
@sandro,
You aren't going to stuff this project with 1 million inserts a day into a LEB VPS. Disk IO will be one problem and RAM the other. By the time you get enough disk on SSD and enough RAM to make MySQL perform well enough, you end up in dedicated server pricing.
I am certain someone out there will say otherwise though. It can be done, but provider better not have a busy box with low IOPs or you will be promptly flagged as abuse and shown the door
VPS is fine to prototype though.
Your real time tables with the archiving on interval is fine. Problem is that table has tons of inserts and eventually deletions as data moves out to archive. That results in bloat MySQL doesn't reclaim on its own. Also when/if corruption occurs you have a royal flusher of a problem and downtime. Avoid big tables all you can.
The distribution of activity based on users is almost never uniform. Even monitoring the users and intelligently putting them in new table spaces won't prove much better.
The round robin approach will work fine for this. If you have an insane activity amount out of some users, nothing stopping you from setting the field that dictates table space for them to a lesser used table space and prior to that moving their data over to that table space. But, the likelihood you will do that ever or need to is slim to none.
The many tables approach (36 of them) also make maintaining the tables and dealing with any errors, corruption, etc. a much speedier process to resolve and ideally only impacts a subset of the the users or 1/36th.... < 3% in case of one table corruption.
Same approach can be grown outward to utilize other machines, other drives, etc.
Been doing basically this for last decade and it works
I actually wanted to go with another approach: use a real time table daily and then archive the new data to the archive and start again. But this will put me in a situation where I have to query a gigantic table so I'm back to problem 1.
Usually, if you have very large tables, you dont need to check on majority of them 90%+ of the time. Your application usually are just interested on the more recent records, rather than the old ones. Here is where partition excels.
Usually, partition is used by date. E.g. you select which column is a good candidate for partition. E.g. if its a transaction table, then by transaction date. You can choose to partition by year, month, week, or day. Depending on your needs. Say your table have 100M rows spanning data for 5 years. Roughly you have 55k tables a day. If you partition the table by day (MySQL should support this), then each days data is on a different storage.
Hence, if you query something like: Select * from Transaction where txn_date=xxx and amount > 100;
Since your where clause includes the txn_date and filtered by a particular day, MySQL knows that it only need to look at the partition on that particular date (55k rows), and your whole 100M records are not considered. If you also have a local index, then it will add to performance. As local index are per partition. (I.e. it indexes only per 55k, not the entire table).
Hence, this is very efficient. And you don't have to do a lot of tricks in your design. You just have to make sure that most of your query on that table has a where clause on the partition column.
@pubcrawler I see your point. The more I visualize it the more I'm not entirely sure if this will be OK for my APP. I'll explain why. These activities per user store things they do regarding OTHER users. So if the table space stores the activity on different but limited users I can easily check what they did with no hassle since it's all there but what if I want to check the reverse query? Meaning checking which users did something regarding a specific user (target), I would need to query all the tables at once, wouldn't this be equally or more stressful than having everything in one table? Same thing goes for the opposite approach I could store the activities of the target users per table space so knowing which users did something regarding one it's easy but the reverse query means scanning every single table
For the VPS I think it would be fine for first 1-2k users... who knows I'll ever even reach that. No point in starting on a dedicated right?
@jcaleb is partitioning expensive?
What do you mean expensive?
Do I need multiple servers or just one to gain speed?
all you need to know at http://dev.mysql.com/doc/refman/5.5/en/partitioning.html
I would hope not...even if each item row took 2,000 characters, that's only 21MB :-)
I tried that, I gained about 30% speed increase.
It isn't future proof per se and probably will introduce functionality that isn't part of the SQL standard. So if MySQL is no longer you choice, you will have a major rewrite. I tend to avoid database specific solutions like that when possible.
Well then the relationship and 36 recommended table division should be based on the activity of the impacted user, not the user making the activity.
All activities applied to another user use the that users info to determine where the data gets stored. So activity for that impacted user all remains in one table space.
It is the same thing really.
You can do anything on a VPS. However, buyer beware.
You need to model this idea and create a fake database with 1-2k users and fake populate the dataset to see what size database that is.
Doubt you'd be running even that on the run of the mill el cheapo VPS offers.
Don't forget you need to double up on the disk space since in the one big table model a redbuild/fix requires writing out a second file to do the fix of nearly equal size.
I'd start another thread on here looking for folks running good sized MySQL installations on VPS nodes successfully and see which providers get recommended.
I started a thread looking for others with largish MySQL implementation running inside a VPS:
http://www.lowendtalk.com/discussion/7687/large-mysql-database-on-your-vps
We'll see if anyone chimes in with provider recommendations based on real use.
No you don't need. But of course, adding more servers in gereral do increase performance due to distribution of load.
OK but you're missing the second point of my app
I also need to make reverse queries so it won't solve the problem!
If you put it like this (tables based on the impacted users) what happens when I have to check what a single user did "impact"? If tables are stored like that it means that the "original" users (the ones that do things to other users) are stored randomly everywhere with no order/sorting = I have to scan all the tables. Did you get it now? Or maybe I didn't catch a link that solves this issue as well.
Do you have any experience with alternative DBs that use SQL language? Like MariaDB? Maybe there are even some specifically dedicated for this kind of queries or that have better partitioning system.
BTW: multiple servers while an inferred speed improvement from horizontally scaling is not a guarantee of actual improvement. There is latency across a LAN that while low multiplies quickly. There are issues on how to segment the data across machines. Do we store different users on different servers via a hash (typically the easiest thing to do and fastest).
Queries can get confusing and complex quickly and clustering software for such a solution isn't pedestrian.
Databases are a lot more complicated to scale that say Nginx with static elements. For this reason, the NoSQL movement along with key stores have become so popular.
Maybe I should just stick to my original idea.
Have a daily or weekly live table (1-2 millions rows are nothing really) and then swap it out to the archive table (touched only when needed, possibly never or when moving to BIG resources) while keeping frequently-updated summary tables of things I would need to show visitors, like "all time" stats (that will be recalculated based on the last day activity).
OK, It's not real time for everything but this will keep me going for months even on a medium VPS since the load will be minimal.
Can you elaborate?
Is SQL really suited for this? Is the way you've structured your app correct? I suspect there are improvements to be had on the design itself.
I suggest going with MongoDB too. Been deploying them on VPS and they are great. Doesn't work with OpenVZ without VSwap though - crashes.
Mainly use OrientDB though.
I didn't start the app yet (not the database part) so I'm open to suggestions. What exactly do these servers do so magically? What's the secret?