Change to separate updates from reads in MySQL interface

[KT Walrus]

I don't need it yet, but I wish IPB supported MySQL master/slave db configuration and better INNODB support.

As you know, INNODB engine supports transactions. I think all places in the code that update the DB should be made bullet proof with transactions (by using SELECT...FOR UPDATE to lock rows or other transaction processing SQL especially for update or groups of updates).

Also, with a MySQL master/slaves db setup, all writes should go to the master. The IPB PHP should be written to send writes to the master and not assume that writes make it to the slaves immediately. This might mean doing some of the selects in a transaction that updates the db in the master and subsequent page loads by the user (for a few seconds/minutes) should just use the master.

I think both these feature requests need to be done together. In order to properly support master/slaves, the code should be transaction oriented and then the entire transaction (potentially multiple queries) should be marked as a transaction that requires read only access or update access.

Finally, all this should be implemented on a switch since having two db connections per page load (potentially) wouldn't be necessary in a single database installation (the normal case).

[Luke]

You can change any table to innodb where it makes sense. You're not locked in to the default configuration as you can change it easily with a tool like phpMyAdmin (the sessions table is actually better as the 'memory' type, especially on a dedicated server).

I'm really not sure what you mean about master/slave... but such a thing, if what I'm thinking of is along the same lines, would probably cause more problems than it would solve. If you got to the point where database access was an issue, there are a number of things you can do to improve this:

- If already on a dedicated server, install Spynx to improve search speed and accuracy (instead of using FULLTEXT). I imagine this takes a significant load off of MySQL as searching is one of the larger bottle necks in IPB.
- Take out a second dedicated server and connect the two together via cross over cable or private backend network. Have the second do just database.
- Take advantage of MySQL database clustering. I'm not to familiar with this, but I've heard MySQL offers this.

[KT Walrus]

- Take advantage of MySQL database clustering. I'm not to familiar with this, but I've heard MySQL offers this.

MySQL master/slave configuration is the easiest way to implement replication. That is, all updates from clients need to be made to the master db and then MySQL sends the updates from the master db to all the slave dbs. The clients don't wait for the data to be replicated and the apps, like IPB, need to separate update transactions from read transactions. The read transactions may be routed to one of the slaves so that processing can be distributed. There can be a delay between the time the master is updated and the slaves are in sync, so the client needs to realize this. For example, immediately after inserting a new topic into the database, IPB transfers the user to view the new topic. If the view new topic operation is done on a slave database that hasn't been updated yet, the user will not see the topic. So, in this case, IPB needs to make sure that the view new topic is done using the master database connection and not the slave database for this user. Basically, IPB should "remember" (maybe through a cookie) that the member has posted a topic/reply in the last X minutes and to use the master db until X minutes have passed and IPB can assume the slaves have been updated.

The MySQL database clustering implements a master/master configuration where the client doesn't get to continue processing until the updates have been applied to both databases. It is more complicated to set up and probably only done if you need very high availability.

Note that IPB already supports all tables being created as INNODB tables. IPB just doesn't take advantage of the transaction processing support that INNODB offers to keep the database consistent and that row-level locking is used to its maximum advantage for performance.

Abstracting the existing IPB PHP code so that it groups select/update statements into serializable transactions would make for a more professional app and probably result in a slightly better documented system (identifying begin/end transaction points in the code is a good idea rather than rely on the whole page load to be the transaction - which might tie up rows unnecessarily).

[KT Walrus]

BTW, with the next release probably being 3.1 rather than 3.0.6, this is the perfect time to address these two foundation features. It really isn't that big a deal to separate updates/read-only transactions and to group queries into transactions. The MySQL DB driver could make sure all updates go to the master, and the calls to mark the beginning of a transaction could take a flag as to whether the transaction is read-only or updates the db. Making the master db connection can be deferred until an update is seen and the "set force master connection timed cookie" could also be a flag on the begin transaction and the cookie can be checked in the DB driver to force all master connections.

[bfarber]

Actually, grouping queries into transactions would be a huge fundamental change to the system. I'm not clear on how you would say it wouldn't take much work.

Separating reads and writes isn't very hard to do. I've done it in 2.3 before in fact simply by regexing the query in the database driver, and for selects sending to one server and all other queries to the other (the master). However, tracking when users make changes and forcing them through the master when that happens is another story.

Every single page load results in inserts/updates, so how would you determine which should be forced through the master? i.e. your session is updated on every page load as an example.

[KT Walrus]

Actually, grouping queries into transactions would be a huge fundamental change to the system. I'm not clear on how you would say it wouldn't take much work.

This isn't an "all or none" type of thing. Right now, IPB stores summary counts when posting a new topic or reply in various tables (e.g., a count of the number of replies in the topic and counts for the number of topics/replies in the forum, and a bunch of other data gets updated). There is currently a possibility that counts and data can get out of sync if two posts are processed simultaneously. So, you just need to look at the SQL calls for this main operation and change to use START TRANSACTION and COMMIT statements before and after the selects and updates. I don't think for the most part you will need to change any of the queries or where they are done, just find the appropriate places in the code to insert calls to the MySQL driver to begin/end a transaction.

There are probably a few places where you don't even need the begin/end transaction. If a single update is being done one the data gathered from one or more previous select queries, it might only take adding FOR UPDATE to the selects (to lock the rows that will subsequently be updated).

I bet you could make the main operations that users do (like posting) transaction safe in a single afternoon of coding. You really aren't changing any logic, only specifying groups of statements that should be done atomically or specifying row locks to be held for an upcoming update. You can only make the system more bullet proof since right now, each individual update is committed to the database immediately. These changes would make groups of queries atomic.

I think

Separating reads and writes isn't very hard to do. I've done it in 2.3 before in fact simply by regexing the query in the database driver, and for selects sending to one server and all other queries to the other (the master). However, tracking when users make changes and forcing them through the master when that happens is another story.

No, you don't want to blindly just send writes to the master and reads to a slave. If that were the case, I could just use a simple MySQL proxy to do that and none of the apps/clients would need to be modified at all (MySQL is developing a proxy that already does this). Rather, you want to send groups of queries (in a transaction) to the master. This is because the updates may be dependent on the data from a previous select. You want both the selects and the subsequent updates to go to the master so the operation is consistent. Remember the slaves may be behind the master by a lot of updates.

This is why I think you have to implement transactions first and then you can mark those transactions (in the begin call) as either read-only transactions or update transactions.

Every single page load results in inserts/updates, so how would you determine which should be forced through the master? i.e. your session is updated on every page load as an example.

You need to have both a read-only connection to the database and a writable connection open to the database. That is, the config would give two IPs for connecting to the database (one for the master and the other for a slave - they could be the same IP if there is only one database). The IPB code would connect to both IPs on page load, and send the transactions to the appropriate connection (that is why you need to insert calls to the MySQL driver to start a transaction to choose either the read-only connection or the updatable connection. So, for every page load, the session table update would be sent to the master (as well as the query to get the session), but most of the other queries for the page load could be sent to the read-only connection.

There is one thing you might have to think about to handle better in this scenario. You normally defer the session update to after page output (I think) to keep the user from "feeling" the time it takes to do this update. But, the query to get the session info is done before page output. Since you only operate on your own session row, the select and the update don't need to be in the same transaction. But, you must mark the select and the update to be done on the master connection. But, I bet there are other examples where the select is done before page output and the update is deferred to after page output. You can't really put these queries in the same transaction without including all the intervening queries in the transaction. If it is only the occasional page load that has this happen, then I probably wouldn't mind having the intervening queries in the transaction. But, you might end up having to not defer some updates (but do them right after the select) so they can be sent to the master without extras getting in there too.

Anyway, I estimate that it is probably a weeks worth of work to get all this working properly so IPB can really scale for busy boards. I'm working on a project right now where the board may get very busy occasionally for an hour or so. It would really be nice if IPB could really scale up and transactions and master/slave support are really needed for scale.

[KT Walrus]

Just thinking about this a bit more. If you don't want to add support for Master/Slave dbs, you could do a quick and dirty fix for keeping the db consistent with INNODB transactions.

I think you could disable autocommit for the MySQL connection (when you make the connection). Then, you just need to remember to issue a COMMIT call after the last update query is performed for a page load.

This isn't optimal since all queries for the entire page load would be bundled into a single transaction. This might lock some rows in the database much longer than really needed (for the entire duration of the page load).

I'm still hoping that you do the begin/end transaction calls around groups of queries that logically should be in the same transaction since this makes the DB much more scalable and holds locks for the shortest period of time needed. With servers having 8 or more cores and MySQL running many more threads simultaneously, it is important to minimize row locking to get maximum throughput (otherwise, you won't get any more scale over having just a couple of cores for MySQL).

[bfarber]

I can say it is very unlikely this will be added for 3.1. Things change all the time of course, but we have a rough list of features and a rough timeframe, and I don't think this will make it.

We can think about it for 3.2 and beyond.

[KT Walrus]

I can say it is very unlikely this will be added for 3.1. Things change all the time of course, but we have a rough list of features and a rough timeframe, and I don't think this will make it.

We can think about it for 3.2 and beyond.

Any chance you would publish a roadmap of these features/timeframe?

I'd love to have a chance to change your priorities a bit by having some open user input.

I'm sure the issue of scalability is not high on your list, but thinking about concurrency and transactions can make any app better even if you don't need scalability.

[bfarber]

Actually, scalability is very high on the list. One of the bigger features we're talking about for 3.1 (again, nothing is set in stone) is post archiving to move old posts to another table (outside ibf_posts) to reduce the primary post table size.

I imagine once we finalize our list of features we'll announce it, but I can't say for sure.

[KT Walrus]

Actually, scalability is very high on the list. One of the bigger features we're talking about for 3.1 (again, nothing is set in stone) is post archiving to move old posts to another table (outside ibf_posts) to reduce the primary post table size.

I don't know much about speeding up huge tables like ibf_posts, but I think MySQL has partitions now where you can administratively offload a range of posts. If the old posts are all clustered in mostly consecutive pids, I think partitions and INNODB might speed things up considerably. I think the trick with using partitions is to always use a where condition that restricts the query to the main partition so the older indexes don't get loaded. Of course, I think if the whole DB fits into memory, it might not matter much to have huge indexes.

Anyway, I was thinking about scalability where you can solve the problem with master/slave replication. I think I may need this some day for a project I'm working on. The first step for master/slave is to make the code transaction aware.

[Luke]

The issue with partitions is that it's not widely available yet. It was introduced in MySQL 5.1, and a huge portion of Linux servers are still serving the 5.0.x branch in their repositories. The same goes for cPanel as well. The only way you'll see anyone using MySQL 5.1 is on a newer Linux kernel like the latest Fedora where it might be available, or compiling MySQL yourself (which most people don't do).

[mbhmirc]

I think the simple matter here is there's no true DBA's at IPB as previous posts I've made have highlighted. You guys should really consider getting someone like percona involved rather than doing "quick fixes" like archive's. You've repeatedly said in the past (from your own posts) that scalability wasn't a feature most of your paying customers would want. Now it's coming back to haunt you, I even offered $10k + to have this developed back in the 2.0 days!

Rant over...

However bfarber if those hacks for 2.3 would be very interesting to have a look at. As for tracking when someone makes an update that's the easy bit memcache, basically when it gets it get's updated invalidate the cache for that post. When people are getting to 2+server level for their boards their going to be willing to dedicate some ram to memcache.

As for sphinx etc the main reason it's needed is because of contention on the master, if the search's are sent to the slave then obviously sphinx isn't needed as much as it can easy handle just reads as there's no real write locks.

I understand you don't want to get out of developing your core product, however there has to be some cross over into sysadmin once you want your product to work well at the scales of 2-3+ Million posts, and people who don't want to delete posts to keep mem's happy for post counts etc. However to grow as a company you need to take on board something that is getting asked for more and more frequently. Even if it's a pay for feature it would help, it's already standard in some competitors products so I can't imagine it's as hard as everyone thinks it is!

[mbhmirc]

A good start for you would be http://develooper.com/talks/Real-World-Scalability-Web-Builder-2006.pdf. I think it shows you your current route and how everyone else does it.

[bfarber]

Interesting pdf, but it's largely unrelated to what we do. A lot of it is specific to managing your OWN site and focuses on splitting up MySQL, what hardware to use, etc. There are some application "tips" (which I'll explore a bit more later) but most of the pdf isn't something WE can do anything about.

Resource improvement is always important. We do hosting for IPB, as you may know, so of course we want it to perform as best as it can at all times (it directly affects our bottom line after all). That said, we also have to keep in mind our target audience. Building in enterprise-level features for maybe 2% of our client base is hard to justify sometimes. There's always a balance.

FYI, I've seen IPB installations with 10 million+ posts.

[mbhmirc]

Yeah I appreciate that, it was just one minor pdf out of many. Most of it's to do with scaling SN sites which i used to run a lot of. It's good that your a lot more open now :) If your up for it we could take this to PM? I have lots more resources that would make for good reads.

I really want to help IPB be one of the better ones out there, as for your own hosted sites slaves alone are good for backups (no locks on master) however I think you'll find you can host a lot more sites with a lot less powerful servers which is why I sent you to that PDF, i.e. the sun server / Force arguments ;) Yes 10 million posts on a busy board is possible however I imagine people want to run more than just the board these days otherwise your users and yourselves wouldn't be bothering with API's ;)

I have to say (and i don't work for them) percona (mysqlperfomanceblog lot) really are worth the money in your case as it will pay back in bottom line for all your hosted boards like you would not believe.

[bfarber]

Yes, by all means, feel free to send me a pm. Or you can alternatively keep discussion here if nothing you want to show me is private in nature. :)

[mbhmirc]

Yes, by all means, feel free to send me a pm. Or you can alternatively keep discussion here if nothing you want to show me is private in nature. :)

Sorry I forgot to put post notfications on, what specfic issues do you have with splitting read's/writes?

For example in our app i manualy found every statement, checked it over and set it as either a read function or a write function, all the I/U/D's now goto the master and all reads goto master and slave(s). Is it a case that one function might do a read or a write depending on how it was called? I do agree that changing over to transactions is a bigger job than simply splitting up the load. I don't think many boards will get to the size of writes overwhelming recent hardware. If you want me to dig out all the old pdf's we used to use for ph when we we're planing on scaling ourselves I will do. Most of them are social network based as that was our core business at the time.

[bfarber]

There's no specific issue doing so, it's just a matter of balancing time vs need. It's possible 2% of our customer base would have a use for this change. At the same time, we've got feature suggestions that 60% or more of our customer base would make use of. As such, as a general rule, it makes sense to focus on the features that more customers will make use of.

Still, we spend a period of time with each release cycle focusing on resource usage, efficiency, and the like. This isn't something we're *not* interested in.

[mbhmirc]

There's no specific issue doing so, it's just a matter of balancing time vs need. It's possible 2% of our customer base would have a use for this change. At the same time, we've got feature suggestions that 60% or more of our customer base would make use of. As such, as a general rule, it makes sense to focus on the features that more customers will make use of.

Still, we spend a period of time with each release cycle focusing on resource usage, efficiency, and the like. This isn't something we're *not* interested in.

Which is fair enough, however this was requested as paid for work in 2006 by the company I then owned. Also on top of the extensive time scale do you not think of common company practices like charging extra for it as a module to make it worth the time.

Many people will be forced to move away from invision or hack it themselves and lose support due to the fact it doesn't scale when you get popular. Other boards do it as standard, but I'd rather pay to keep invision.

In the end your just posting contradictions, you want to make the board scale yet you will not do the key item to make it scale. Your doing micro optomisations vs scalability. I think you missed the whole point of the pdf i posted, no one wants to buy the million dollar sun box they want to buy 1000 servers for 1/10th of the price that does the same thing. Your going to get what 5-10% improvement, against 200-500% go figure.

[bfarber]

You might have missed my last sentence there. I said

This isn't something we're *not* interested in.

What I mean is, methods to allow the software to scale are things we're interested in researching/exploring, and we in fact take time with each release to look into such things. This may or may not be implemented at some point during one of those periods. Hopefully that helps clarify what I was trying to say.

[Ran Yefet]

Actually, scalability is very high on the list. One of the bigger features we're talking about for 3.1 (again, nothing is set in stone) is post archiving to move old posts to another table (outside ibf_posts) to reduce the primary post table size.

I imagine once we finalize our list of features we'll announce it, but I can't say for sure.

Is there any update about this feature?

[Mark]

Is there any update about this feature?

It will not be in 3.1, but is still on our list.

[yacenty]

any news on this?
any possibility to scale the IPB?
Could You help me to divide DB on 2 DB servers?