Webmaster Forum

Go Back   Webmaster Forum > Web Development > Coding Forum

Coding Forum Problems with your code? Discuss coding issues, including JavaScript, PHP & MySQL, HTML & CSS, Flash & ActionScript, and more.


Reply
 
Thread Tools Display Modes
Share |
  #1  
Old 12-15-2015, 03:06 AM
capturts capturts is offline
Junior Member
 
Join Date: 09-24-13
Posts: 12
iTrader: 0 / 0%
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?

Last edited by capturts; 12-15-2015 at 03:28 AM.
 
Reply With Quote

Advertisement

Advertisement

  #2  
Old 12-15-2015, 05:45 AM
Dan Williamson Dan Williamson is offline
The Controversial Coder
 
Join Date: 05-01-06
Location: Manchester; UK
Posts: 2,710
iTrader: 0 / 0%
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:

Quote:
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.
 
Reply With Quote
  #3  
Old 12-15-2015, 07:00 AM
capturts capturts is offline
Junior Member
 
Join Date: 09-24-13
Posts: 12
iTrader: 0 / 0%
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
 
Reply With Quote
  #4  
Old 12-15-2015, 08:22 AM
Dan Williamson Dan Williamson is offline
The Controversial Coder
 
Join Date: 05-01-06
Location: Manchester; UK
Posts: 2,710
iTrader: 0 / 0%
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.
 
Reply With Quote
Go Back   Webmaster Forum > Web Development > Coding Forum

Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


V7N Network
Get exposure! V7N I Love Photography V7N SEO Blog V7N Directory


All times are GMT -7. The time now is 02:35 AM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2018 VIX-WomensForum LLC