Multi MySQL Server (Write and Read)

[valendono]

Hi,

Do you guys know how to create mysql separation between read and write database in IPB 3.3.0 ?

and reduncandcy between master slave on write database and master slave read database ?

Thanks

[Grumpy]

For live mirrored mysql servers, replication is the way to go...
http://dev.mysql.com...eplication.html
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

But, this isn't easy to maintain. I would suggest you simply upgrade your database server instead if possible. Like upgrade to (raid) SSD, better cpu, more ram. If a single machine is truly incapable of it, there's replication.

Edit: Actually, now I'm rather confused on what you want...
You want (a master AND slave(s) that can only read) AND (a master AND slave(s) that can only write)?
If yes, then I want to ask the question... what for?

[valendono]

We are using 11 vps and 3 memcache server and also using cdn server, but only 1 mysql server, when you post something, sometimes it will slowing down the database also about the marking post in IPB. I knew about replication and slave db, but how to implement in IPB coding php ? how to manage it ? which code I must customized ?

So I can tell to the software / ipb, when you want to read use this database, when you want write use this database, and use this slave server when you capacity more than 50% loads.

and I want to split read database, write database and also slave as the backup or for dailybackup purposes, so when we are doing cronjob backup (dump) its not affecting production server performance.

[Grumpy]

I think you're quite over due to for an upgrade to a dedicated server(s). It'll be more cost efficient and reliable than running so many vpses.

If you want IPB to read/write to only one database (ie the master), just write the master IP inside IPB.
Alternatively, you can setup a cluster (failover, high availablity, etc) of mysql databases via load balancing. This isn't commonly done at application level, purely at server administrative level. So, the application constantly has 1 interface, or at least it believes that it only has 1 interface.
See this for more details
http://agiletesting.blogspot.ca/2009/04/mysql-load-balancing-and-read-write.html

I do not know if there's any inbuilt IPB setting for an option such as this as it seems an uncommon practise.

You can also setup your backup to run on low priority so it doesn't cause further lagging when it runs. Backup is also purely read, as long as your disk can push out the iops for read, it shouldn't cause locking to slow down the website by a large noticeable margin -- assuming done properly.

My recommendation: Just try to get a raid SSD dedicated server (raid 1 with 2 disks or raid 10 with 4+ ssd disks). See if you're still bottlenecked on mysql. Only afterwards, OR if you have a lot of $$ to spend, go for a mysql HA setup with load balacing. You can just ask the datacenter for it and they might setup for you (with fees if unmanaged).

[yacenty]

definetly UPGRADE HARDWARE!!!
Then upgrade mysql to something like percona or mariadb, then try other solutions

[mat206]

This might help you get things started.. it's a new mysql database driver. If you start to experiment with it all I ask is that you send me some of your changes so we can continue to make it better. I never really got a chance to fully utilize it. It could use a ton of upgrades / smart enhancements for load balancing.

http://community.inv...e-mysql-driver/

The way the driver works is simple.. direct all writes to the master server, and direct all reads to a random server from the master/slave pool. It uses the standard default mysql driver for most of the heavy lifting.

[valendono]

This might help you get things started.. it's a new mysql database driver. If you start to experiment with it all I ask is that you send me some of your changes so we can continue to make it better. I never really got a chance to fully utilize it. It could use a ton of upgrades / smart enhancements for load balancing.

http://community.inv...e-mysql-driver/

The way the driver works is simple.. direct all writes to the master server, and direct all reads to a random server from the master/slave pool. It uses the standard default mysql driver for most of the heavy lifting.

@mat206 : is it still working on 3.3.0

[mat206]

@mat206 : is it still working on 3.3.0

No clue.. but probably. I doubt they made major revisions to the database driver. Only one way to find out..

[valendono]

this is completly confusion for me, i have another software forum but they exactly have how to do the slave and master in config without change any other source code.

you think IPB is really ready for big board ? so far I see, with sphinx half way to go with big board, I didnt how other big board survive wihout multiple database server and can handled users more than 5000 users at once.

:(

[Grumpy]

this is completly confusion for me, i have another software forum but they exactly have how to do the slave and master in config without change any other source code.

you think IPB is really ready for big board ? so far I see, with sphinx half way to go with big board, I didnt how other big board survive wihout multiple database server and can handled users more than 5000 users at once.

:sad:

5000 users online over a single SSD is quite possible for IPB. lol

[valendono]

5000 users online over a single SSD is quite possible for IPB. lol

I wish..