2

I want to upgrade my server because of the high mysql loads causing slowness + whenever there were backup processes running on the DB, the DB would be inaccessible, so wanted some solution. My current server is an Opteron 2212 with 8GB RAM.

My web host suggested a lower configuration server (Phenom Hexacore with 8GB RAM) but to take 3 servers + load balancer...they said that it would help in situations where there is a high amount of traffic and also help when one of the server fails, that there will be a backup server.

On inquiring further they said that no mysql replication will be set up because they charge extra for that but will still be able to configure a set up which will meet my needs.

I am not really sure, will my purpose of an upgrade even be achieved if I have 3 such servers and no mysql replication...they're not very clear on how they'd set it up. Any ideas on whether this is a good solution and what I could propose to them?

Sam
  • 51
  • 1
  • 2

5 Answers5

2

They want to make more money off you, it's that simple.

First things first: taking a backup makes your DB inaccessible? Either you've got a very poor backup solution, or you have serious I/O issues on the server. It's most likely I/O. I'd look at getting this fixed first of all. If you're on a VPS then your host might be able to assign raw LUN mappings to you, rather than giving you a virtual disk on a shared, abstracted LUN.

If they're not going to set up MySQL replication, then you're going to either be on your own, or charge you a fortune for it. I'm not familiar with MySQL replication (only MSSQL) but I'm guessing this is not a trivial task and needs to be undertaken by someone who knows what they're doing. If you're not going to be duplicating your SQL server, only your web server, then I fail to see how this is going to help your site.

Thirdly, you've not given us any details about the database, so this is just a stab in the dark, but perhaps there are places you can optimise inside your DB. An Opteron 2212 and 8Gb of RAM is not a slow system. We've got 400 concurrent users 18 hours a day hammering out over 500,000 requests a day on our portal resulting in 10s of millions of queries on a 30Gb database and it runs beautifully on a machine on similar spec. Well designed indexes can save you a lot of money, time and headaches.

I think there's a lot of other avenues to look at first, before deciding to go down the load balancing path, especially if you're going to cluster SQL servers. There is one point that they got right though - if one server goes down, you do have instant failover (if your load balancer is configured correctly), but even if you don't have failover, you do have backups (hence the first point) so its probably not the end of the world.

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
  • +1 for well-designed indexes. If you have trouble with your MySQL DB performance, it's always worth having a look at the slow query logs. Run each query through a "EXPLAIN" and see what comes out. The difference between optomised queries and non-optimised is just phenomenal. – wolfgangsz Sep 30 '10 at 13:37
  • I have a dedicated server..my GB is around 35 GB in size and a mysqldump process is configured to run everyday at midnight but that takes about 30-35 mins to complete, and while that's running at some point in time the big tables get locked for really long, which poses a problem... – Sam Sep 30 '10 at 14:05
  • @Sam - there must, must be a way to dump a MySQL database without locking the tables, otherwise nobody would ever use the system. Check out this SO question: http://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables (which would also be a great candidate for Server Fault, except it was asked before SF existed) – Mark Henderson Sep 30 '10 at 23:05
2

While I appreciate Farseeeker's perspective and believe that it does have value, I would suggest that their only motive may not be only to make money from you.

It sounds like that may be offering close to an ideal solution or at least a more professional solution than what you have implemented now. Nevertheless, it may still be over-engineered for your needs. That is partially a business question. Is the additional expense justified?

The high loads require substantially more research. Rhetorically.. Is it read queries? Can they be optimized? Is your schema optimized? If it is write queries, you might need to scale up or out. Otherwise, you could consider archiving the data that is no longer necessary.

A common solution for backing up MySQL databases is using a slave relicant and then taking a snapshot of it, which prevents DB locking. While I/O contention is a possible cause, using mysqldump or mysqlhotcopy often introduces some level of locking. Other solutions include Xtrabackup. Chances are there is room for substantial improvement with your backup solution.

While what they propose is certainly closer to ideal and better than what you have now, whether or not it is justified is up for you to decide. You can likely get more bang for your buck and reduce recurring expenses by improving what you have now, as that will solidify your platform for future growth.

Community
  • 1
Warner
  • 23,756
  • 2
  • 59
  • 69
  • It's a mix of read and write queries, 65:35. I am looking at a solution that involves little chance of my server ever being down, so given that I guess what they've proposed might work, but if they don't use replication, won't it mean that they just use the DB on one server and use the other 2 as web servers? Will that help my cause because I am facing mysql slowness and because of which I want to upgrade. – Sam Sep 30 '10 at 14:18
  • +1 for replication slave and snapshots from that instead, however he didn't have that option. – jishi Sep 30 '10 at 14:21
  • A solution that involves little chance of your server ever being down involves substantially more investment than that, which is detailed further in my answer to your previous question. Replication is not necessary unless you want to use it to spread load, enable high availability, or as part of your backup strategy. It does sound like they're separating the server roles. As far as the performance issues, it will probably help. But if your application architecture suffers more issues, it will not eliminate it. Difficult to say without knowing more intimate details. – Warner Sep 30 '10 at 14:39
1

You should be able to run a backup on that machine without locking the database. I would take a look at other backup options. What are you using now?

Unless your concurrent user load is the problem, a load balancer+servers will cause you more problems than solutions. Depending on your database usage (what kind of I/O? Mostly reads? Mostly writes?) there are several ways to optimize your database. MySQL replication is not difficult to set up, but I don't think it is a solution for your problem.

Craig
  • 1,354
  • 6
  • 14
  • My read:write ratio would be something like 65:35...I don't have more than 40-50 people at any given point of time. For backups, I used mysqldump (WHM backup is enabled and it uses that) – Sam Sep 30 '10 at 14:08
1

You seem to be arguing that the source of your performance problems is exclusively the load from the mysql server - while the proposed solution would handle the HTTP load more effecticely it seems to do nothing for the mysql problem. Are you sure that the DBMS is the cause of the problems when you're not doing backups?

I disagree with Farseeker - mysql replication for the purposes of getting a consistent backup is very simple to set up (simple master-slave replication). It does seem a waste to not take advantage of the potential load-balancing capability - but combined with the backup reuirement, this would make the config rather complicated.

You should be able to run a backup on that machine without locking the database.

? Wow. How? Every DBMS I've used can potentially lock during a backup - including Oracle and Sybase. Certainly locking is far less frequent with the more recent c-isam engines in MySQL - but it still exists.

symcbean
  • 21,009
  • 1
  • 31
  • 52
  • Simple to setup or not, I wouldn't do that for free-- especially as an ISP. – Warner Sep 30 '10 at 14:40
  • `You should be able to run a backup on that machine without locking the database.` - I've never had to lock a table whilst doing a backup. Admittedly 99% of my experience is with Microsoft SQL or Pervasive Btrieve (Hellloo 1995) but the built-in backup tools just do it. Admittedly it's a lot more complicated than MySQL's sqldump though... – Mark Henderson Sep 30 '10 at 23:02
  • (p.s. in my defense I did mention that I hadn't set up replication in MySQL. In MSSQl to get a merge replication working you have to actually make sure your apps and tables are designed for it) – Mark Henderson Sep 30 '10 at 23:02
0

You are mentioning a high amount of writes for a web application, are you sure its 65:35? If you have high MySQL-load, then your issue wouldn't be solved by extending the amount of servers, since you cannot distribute database writes in an easy manner without (usually) heavy modifications to your code.

You have a few options:

  1. Optimize your database, as suggested by the other answers. That means, make sure all data is indexed correctly.

  2. Cache your results on the webserver. There are different strategies for this, one of the easiest being to write down generated HTML into a file that you just read and output if cache is not invalidated. Serializing PHP objects into files is another. Read up on different caching techniques for your platform (I'm guessing PHP?)

  3. Use another backup solution. If you only use MyISAM tables, look into mysqlhotcopy. It's usually MyISAM tables that gives you trouble because of complete table locks on backup.

  4. Take backup from a slave-database, residing on another machine, but your provider tells you they cannot do this? Seems incompetent to me.

jishi
  • 868
  • 2
  • 11
  • 25