Webmaster Forum

Webmaster Forum (http://www.v7n.com/forums/index.php)
-   Coding Forum (http://www.v7n.com/forums/forumdisplay.php?f=16)
-   -   MYSQL master-master replication (http://www.v7n.com/forums/showthread.php?t=395336)

capturts 12-15-2015 03:06 AM

MYSQL master-master replication
My website uses Twitter's api and I believe it is causing problems due to the latency in each API call. I think the problem is with mysql...

1) Page connects to mysql
2) Various sql queries
3) Twitter API call
4) Various sql queries
5) Twitter API call
6) etc
7) Disconnect mysql

The thing that takes longest in a php script is the Twitter API. The script blocks while waiting for the data. I know there is an asynchronous twitter library for PHP, but I need the result before continuing.

So what I think is causing the problem is that many people at once loading many pages one after the other is sometimes hogging connections to the mysql server.

My idea was to use master-slave replication to balance the load somewhat. But it would require major rewrites (write to master, read from slave) and I don't have time. So i discovered master-master replication. Each script would randomly pick a server and connect.

Will this work as a stop-gap solution?

Dan Williamson 12-15-2015 05:45 AM

I'm very much a MSSQL guy - but generally yes you could have a MySQL cluster which replicates and you could look into multiple low-cost shards.

From the MySQL Website:


Auto-Sharding Database is automatically and transparently partitioned across low cost commodity nodes, allowing scale-out of read and write queries, without requiring changes to the application.
Multi-Master Replication Each data node can accept write operations. Coupled with auto-sharding, this gives very high write scalability.
Have you looked into optimising your queries, without a broader understanding of the problem I can't help too much further, but often people tend to skate around the issue of their queries/design and try to up the infrastructure behind them, but it could be something as simple as a un-optimised query.

capturts 12-15-2015 07:00 AM

Thanks for the reply.

I think I might have missed actually stating the problem.

The twitter calls take as long as they take, but they make the whole script take way longer than they would without twitter involved.

The script taking 10 times longer than than it should means the script stays connected to mysql for longer than ideal and I belive that this is preventing other scripts from connecting at peak times.

I toyed with the idea of disconnecting before the Twitter API call, maybe just at peak times. But it might involve several disconnect/reconnect cycles, which might be a problem in it's self.

It's possible I could rewrite the scripts to do it more efficiently, but it'd take a lot of time. I felt two servers might be the way to go. But rewriting for a read-from-slave, write-to-master wouldn't help, as the script would still be connected to the mysql server for the duration of the script including the lag due to the Twitter API

Dan Williamson 12-15-2015 08:22 AM

What are you doing with the Twitter API? I wouldn't have thought it would be slow unless you're searching/sorting/reading hundreds of thousands of tweets/collections. Does the post twitter action need to be completed before the script moves on? If not you could look into making them both run at the same time.

All times are GMT -7. The time now is 05:33 AM.