LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
LinkBack Search this Thread
Old 04-21-2010, 05:39 AM   #1
cooljai
Member
 
Registered: May 2007
Location: /dev/random
Distribution: CentOS, Fedora, RHEL, SuSE
Posts: 62

Rep: Reputation: 15
Question MySQL Scaling, suggestions please


Hi,

We are serving very busy site and needs to scale MySQL server which is currently running on CentOS 5.3(QuadCore/32GB/RAID10). Here are few thoughts:

1. Master-Slave: Site is sensitive about delays and any delay (which is a possibility in master-slave/master-master setup) can hamper its functionality. So we are not looking at this option. Slave is there only for backups etc.

2. MySQL Cluster: same as above, in multiple nodes there's possibility of occurring delay. I'm not arguing for bad site design (as its not able to tolerate any delay) but its what we have to live with.

3. Horizontal scaling: That's the option, I'm considering here. We will partition DB in parts and deploy DB servers for each, say, all data related to product id 1-5k will be stored and served by DB1, product id from 5-10k will be stored and served by DB2 and so on.

Now DB can be scaled like this, but we need complete overhaul of application to distribute requests to different DB servers based on criteria (like based on product id).

What's the best way to implement it? I'm considering Memcached in one of DB server to store ProductID=DBServer mapping which application will use to get know which server have that particular product and then make/use connection of that DB server. But again, I am not aware how to provide some failover to Memcached?

or any other better way to scale DB server here? any other tool which might be helpful? Your suggestions will be highly helpful.

Thanks in advance,
 
Old 04-21-2010, 06:38 AM   #2
nowonmai
Member
 
Registered: Jun 2003
Posts: 481

Rep: Reputation: 47
Can you provide more information about what sort of transaction volume you are experiencing and where you have measured bottlenecks?

Usually, the first point of failure in a system like this is the storage. What sort of drive infrastructure have you got? You specify Raid 10, but nothing more...
 
Old 04-21-2010, 01:04 PM   #3
cooljai
Member
 
Registered: May 2007
Location: /dev/random
Distribution: CentOS, Fedora, RHEL, SuSE
Posts: 62

Original Poster
Rep: Reputation: 15
Hi nowonmai,

Thanks for reply. In current web server, we've two RAID.. RAID1 for Operating system and RAID10 for Database files mounted as /db

You may get some idea from mysql status below showing normal DB server traffic:

# mysqladmin status
Uptime: 423214 Threads: 14 Questions: 4194862055 Slow queries: 61018 Opens: 1985 Flush tables: 1 Open tables: 1585 Queries per second avg: 9911.917

Slow queries are quite high, just because we are marking all queries taking more than 1 sec as slow. There's no major bottleneck we are suspecting. The concern here is that Site is going to have 3x more traffic after few days and current Database server can't handle that much. So some sort of scaling is must.

Any further views will be highly appreciated.

Thanks,
 
  


Reply

Tags
balancing, load, mysql, scaling


Thread Tools Search this Thread
Search this Thread:

Advanced Search

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow mysql query, indexing doesn't help. Suggestions? sneakyimp Linux - Server 3 10-08-2009 08:00 PM
Suggestions To Nighly MySQL Dumps? carlosinfl Linux - Server 5 10-01-2009 05:18 AM
Request for suggestions on searching within a MySQL database graemef Programming 2 05-17-2008 06:59 AM
Looking for suggestions on MySQL one-way replication Pcghost Linux - Software 1 01-25-2005 10:22 AM
Suggestions for power saving tips aside from ACPI and clock scaling... adamis Linux - Laptop and Netbook 1 11-13-2003 11:08 PM


All times are GMT -5. The time now is 07:42 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration