How to Lower the Load?

filegrasper

Hi,

I do have a big filehosting site . Now I face a problem in main server where i have mysql and site files are hosted.

For few hours my site loads are too high like 250+ and site loads too slow.

And also I find mysql eating a lot of cpu.

What do i need to do now? Move to very high end spec? or Need to have more than one main server for my site?

perennate

Optimize your MySQL queries.

filegrasper

@perennate said:
Optimize your MySQL queries.

it has been done already , still getting more.

r0t3n

Whats your current machine specs?

You may want to consider putting mysql and the files on seperate boxes, to even out the load.

Novalok

i would suggest a server for files and a server for the main site but thats just me

filegrasper

@Novalok said:
i would suggest a server for files and a server for the main site but thats just me

I have site files and mysql on main server.

And i do store other files in 15 dedicated servers.

My main server specs is E3 1240 V2 16gb Ram and 2x500gb RAID 1

Storage server specs are E5 2420 32gb ram and 6x3tb raid 10.

Will making server for database alone will helps? If so what spec?

jimaek

How about a CDN? The cost could be lower that 15 storage dedis. Calculate it.

Also consider moving your DB to separate server with lots of RAM.

r0t3n

I would not host any files from the main server, as your just going to kill disk io which will make mysql performance terrible, and vice versa.

Ideally, i would put mysql on a box with an ssd, disk io would not become a performance problem them. But, if your leverage caching on the main box, and dont serve file downloads from it, it may be good enough. Do you use any caching currently?

filegrasper

@r0t3n said:
I would not host any files from the main server, as your just going to kill disk io which will make mysql performance terrible, and vice versa.

Ideally, i would put mysql on a box with an ssd, disk io would not become a performance problem them. But, if your leverage caching on the main box, and dont serve file downloads from it, it may be good enough. Do you use any caching currently?

Well all other files on seperate machine , just main server has main site files and DB.

I also heard SSD will solve this problem , And also i have heard about CDN , but dont know how useful it is.

So what shall i do , move DB to seperate machine with SSD or CDN ?

xset

how many queries / second does the mysql serve?

Run the following command:
mysql> status;

one option is to set up a mysql cluster on 3 machines minimum.

r0t3n

CDN will not speed up your database, but it will speed up website loading times, as it caches images and javascript etc and can deliver them to end users much quicker.

You can also try out cloudfare's free tier to see if CDN will help you.

I would highly suggest you put mysql on a seperate box though, so when the time comes to expand, you can save money by using cheap frontend proxies to serve the website content.

Coming back to caching though, this will offer performance gains as well.

filegrasper

@xset said:
how many queries / second does the mysql serve?

Run the following command:
mysql> status;

one option is to set up a mysql cluster on 3 machines minimum.

This is what I can see http://prntscr.com/1mk9s8 - Traffic is low now.

xset

@filegrasper

First of all, you're running old mysql. You should be running at least 5.5. 5.6 is out too.
They are faster.

Also how much CPU and memory is Mysql using on the box?

jimaek

@filegrasper My MySQL stats on file sharing website http://d.pr/i/Amgy

Low traffic right now too. A single small server is handling the mysql+php and I have an other one to serve the actual files.

Try switching to Percona server. Configure my.cnf to cache as much as you can.

filegrasper

@xset said:
filegrasper

First of all, you're running old mysql. You should be running at least 5.5. 5.6 is out too.
They are faster.

Also how much CPU and memory is Mysql using on the box?

How to check the mysql usage alone?

filegrasper

@jimaek said:
filegrasper My MySQL stats on file sharing website http://d.pr/i/Amgy

Low traffic right now too. A single small server is handling the mysql+php and I have an other one to serve the actual files.

Try switching to Percona server. Configure my.cnf to cache as much as you can.

Thanks for the info , I get around 140K UV per day .

And my site uses perl not php.

Right now: Am googling about percona server.

jimaek

• Move MySQL to a dedicated server with plenty of RAM. SSD would help a lot too.
• Use Percona Server instead of MySQL
• Make sure you cache everything in RAM. Use https://github.com/major/MySQLTuner-perl to tweak config.
• Calculate the costs of dedicated servers VS using a cheap CDN.
• Cache the website itself. Serving cached content = no DB queries.

Master_Bo

Upgrade MySQL; even better, switch to optimized forks like MariaDB

Use something from the list

https://launchpad.net/mysql-tuning-primer

http://mysqltuner.com/

to analyse what's wrong with SQL.

Optimize every aspect of the site (you provided little information so far to give proper advice).