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
It's mainly a mismatch. SQL and relational databases are built on columns, tables and rows and rows of data. They might not fit your data model and application.
These days with lots of Object Oriented programming floating around and the rest of it there is a call for a different model of how to approach data storage and the query of it.
You might not need joins (which is most of the SQL performance bottleneck at times) - you might not need primary keys, etc...
I don't like how MongoDB and the like abuse mmap though. Memory mapping everything isn't fun. It's "high performance", but meh.
@sandro,
I haven't used MariaDB, but it claims to be a drop in replacement for MySQL. Some of the same authors as MySQL. Check the requirements for RAM, disk, etc. and give it a try. I am short listing it again to try officially.
Partitioning is a delicate hack. All real databases have partitioning. Compatibility probably isn't good from one to other, but check first.
That gets complicated surely.
We solved the data growth and every query is a slow one with the divided strategy.
Partitioning tends to happen based on some predefined type of thing, like date. So data older than 1 year ago gets put over there and deprioritized.
I don't think partitioning will help you, the traditional style as you require all the data to be available and queried and ideally keys in memory for rapid location. That defeats the concept of how partitioning works per se.
Clearly, what you need is two keys at least on the records created by users as activities. One would be a key based on user making the activity. The other key would be based on the userid of the user receiving the activity.
We segment the mass storage into 10-36 tables based on the userid of the person making the activity.
Hmm how to assemble the data the other way...
It's a hack and data doubler per se if implemented sloppily/easily, but try writing to separate tables with same divided mentality I've gone over but base that on the user receiving the activity.
So basically you have two major table groups.
One based on the person making the activity, so me as a user, all my activities get stored in activities_user_made[25] (random table space)
The second is tablespaces with all activities about me. activities_user_received[25].
These activity tables should basically be a roadmap and index to make the relationships, with the actual data about the activity (text and long set of fields) stored elsewhere. But that gets complicated and might not be best to try up front.
You are going to need to develop scripts to create all these spaces and fake data and prototype from that.
Where possible I recommend pre-compiling data. For instance, I take totals and other summary information that many folks run live time to calculate and I calculate such at the point of activity. Meaning the values the user sees are created when they last did something, not adding up live time the number of reviews or comments they have, but did so the last time they made a review or comment.
Surely, a DBA will have a complicated big query method of doing most of this. Problem typically is it won't scale and making changes as schema changes means the DBA has to re-engineer the queries.
Anyone try the AWS DynamoDB? It looks very promising and it could create a distributed environment very easily.
Look if you can keep a live set of data to a limited size and discard or archive older data then fine. Do that.
Problem is you original idea was 1 million rows of input per day.
At 90 days of activity (a typical length retained by many sites for top view stuff), you are at 90 million rows.
90 million rows of data isn't anything to mess around with in one large pile and certainly not something to live time query to generate pages. It certainly won't run on a VPS unless it is some large and exotic machine that you are paying for the resources you are going to burn.
No matter how you slice this pie, data duplication, pre compiled use tables, pre calculations, etc. are the best and maybe only way to have any real speed and low enough page load time.
Going back to that archive table, if you have to ever query that pigpen, your site is going to go to sleep (well when populated and growing at expected rates). Only way you might survive is with partitioning and date based queries against it.
NoSQL and other anti structured data methods are out there. I think they are novel for some things. They aren't a replacement for SQL though. They aren't a fix for something like this either.
Your schema and business rules and needs dictate what gets implemented. Not technology.
MySQL and derivatives will work fine. Get to prototyping.
And I'm still waiting to hear what it does better, other than make its users feel more ideologically pure.
@raindog308,
Hard to say what MariaDB does better or faster. It's one of those classic nerd tech idiot sites. Not as awful as MySQL's own site, but close.
These firms really need to hire some functional humans with communication skills who also understand the technology. Articulating things better, clearer, and real world examples, mock implementations, configs, etc. should be way up front and clear. Similarly benchmarking that can be reproduced, very important.
Tired of searching those sorts of sites for simple, often duplicative questions. Then end up on their mailing list chattering with nerd kingdom who often is tired of the newbie questions and ranges from rude to non existent depending.
This is another great idea.
Even though I like it I think that if I have to swap out old data regularly with this method too, I might as well just use the live/summary/archive tables approach. It seems far more easy and fast even though... that big table I don't see it scaling proof.
Once I have my summary tables or per-compiled as you call them the big one won't be queried again. I have to make sure the summary tables have everything I need to not make want to touch the archive one ever again. MAYBE it could be queried once a week for stats BUT ON A SECONDARY SERVER outside the production server so users won't be affected by the load.
I tried partitioning a 50M rows table on my stupid SATA2 HDD, cached index on the RAM. Partitions are made by key(user_id). To select all the rows from one user (for instance 10 million) it takes approx 0.65sec. I think it's a perfect result for background, non-live scripts, don't you?
And by the time I will get to a point where I'll have 1 million activities per day (that would translate to _at least _1 million page views) it means I will have 10k active users doing that much traffic per day not counting the ones making just 1/1000 of the traffic...By then I suppose I would be able to afford much better dedicated servers with the revenue!
This is from a post about Wikicrapia moving from MySQL to MariaDB:
Typically error margin on things is 5%. So MariaDB being 2-10% faster is just a tad better than error margin
I wouldn't be jumping hoops to ride the MariaDB magic carpet unless there is some must have feature in MariaDB. You can get way more than 10% improvement by optimizing queries, adjusting indexes, segmenting data and prebuilding content.
I don't know you guys but when I happen to land on the MySQL documentation I wanna kill my self. I always end up searching on other sites or forums!
Nothing like the PHP documentation. That's my bible.
I agree.
Seems same effect as partitioning, but too much work
Why? the site will only work a 1-2M rows per day
@sandro, I often do double writes
That is one piece of data goes in this segmented table for live use and speed and it also gets shoved in an archive style table. The archive dump gets emptied of some data on schedule (90 days) and it goes into a large annual archive table.
DBA's probably would burn me at the stake for such, but it works and does so fast. Allows for rebuilds in case of catastrophe, allows portability and smaller working sets. Allows for faster rebuilds. Just wait until you lose power and end up with corrupted database with millions of rows. Enjoy that education while standing in fire.
One user should never have 10 million rows
.65 seconds while seeming fast, isn't. That's more than 1/2 second. Real world total page generation times should be < 250ms. You are more than 2x slower than that and with ZERO load.
Try that test with 5 PHP scripts hitting the database selecting random IDs. Then move to 10.. simultaneous accesses. Let me know how that performs.
Try that test with 5 PHP scripts hitting the database selecting random IDs. Then move to 10.. simultaneous accesses. Let me know how that performs.
You didn't get it. These won't be queries ran on pages. I know for a page that's awful. These are background script ran weekly maybe (if).
Yes, but you still have to do archiving. Whereas in partitioning, you just leave it there.
I agree at top view seems the same. It is similar, but different.
I mentioned partitioning early in the thread. I've tinkered with it. But my hacker method of doing this beats partitioning and does most of the same beneficial things.
Partitioning is a depend on MySQL to do best for you. That approach is random in real world. We see that with query optimization MySQL does wrongly routinely.
I tend to prefer to know my data and make intelligent rule based decisions on what to do. Call it being impowered and in control of my data mess.
MySQL is amazing software, but it's far from perfect.
But because you have a lot of data, maybe do some experiments first with several ideas. So that you can be confident in the long run.
Will the double writes method save you somehow from this?
Yes. I believe each application is unique, and solution should be based on how you understand your data and your application.
When power loss happens it is random what MySQL fubars.
Assuming tables are all writing, might corrupt them all. Where I typically see corruption, is on the HUGE table spaces though. Probably due to queuing and wait times due to the large filesize.
When we get smacked like that, I have a simple survival routine that:
1. Renames the big table to another name.
2. Duplicates the schema of the original tables (saved elsewhere) to the name of the corrupted table.
Now we have data coming into the empty new table and we are free to repair the broken table.
When repair is done we copy the data over from the temp table, delete the data range just inserted, rename the temp table, rename the rebuilt table.
Then we do a check 30 seconds later to make sure that temp renamed table is indeed empty, move the data over if so.
It's tedious, but works.
Same thing applies to smaller tables, although typically we can rebuild the table real fast and minimize downtime.