Hi All, We are looking for a specific solution related to our MySql database which is effecting our server performance. We have a social networking website which is database intensive (the max. load is on the database server i.e MySql). It so happens that our MySql load reaches 70-85% on an average. More of this happens because we complie some reports on a daily basis where we have to create about 60,000 records everyday (10,000 records x 6 tables). Each cron here takes about 30-40 minutes to generate these 10,000 records and we have to run 6 crons daily. While we have checked slow queries, but these queries are optimized. However, due to the nature of these customized reports, it takes quite a load on the MySql server. What could be a possible solution for us to reduce the load: 1. Do we host a database on an alternate server, then generate records to this database? 2. Do we run a mirror database on another server and run our queries there? Any other possible solution where we could pull more data from MySql whenever required without effecting the normal performance of the database while end users are using the site? Kindly help!
if i were you, i would simply setup a cron to copy the necessary data for the reports to a mysql server on another machine and run the cronjobs to compile the reports there, then simply send back the ready compiled reports..
Query Cache is enables. The MySql runs on the EBS Block of Amazon EC2. You can say it is on a seperate hard disk.
Busy servers might need to disable Query Cache, it became unhandy with busy servers most of the time. and i don't have a background about EBS Block of Amazon EC2, so i'm not going to judge, but as far as i know you always have a limit, so why you don't you create your own limit. get a new server just for MySQL.
We earlier hosted our dedicated server with other company earlier. There we had two servers, one hosted the Apache processes, EXIM and the other hosted just the DB. And the two were connected via private network. However, even then too, the DB had a high load avg. of 60-70%. This particular issue arises when we have to pull out some intricate reports. And sometimes saving the data to the DB. It's like looking at your Adwords data for one year compiled into one single report or lets say looking for your Facebook Page insights for the last 4-6 months at one go.
you should consider installing high performance hard disks such as SSD raid-10, and litespeedtech instead of apache or any other platform. you might also give your MySQL a look to a programmer to optimize your DB. you have so many options but you need a server administrators to give it a look and advice you. try to ask your data center. DC sometimes offer solutions fit with your budget.
Getting a SSD 2nd drive and running MySQL from that is good for a performance boost, but as 3mints said you should get a programmer to check over your queries there is most likely a more efficient way to generate the reports. I setup a high traffic site on a server last week with nginx, php-frm and memcached, 1x ssd for nginx 1x ssd for MySQL and the site flys with very low load, when it was running apache it used to crash the box.
Thanks 3mints and Gavo. We did have an expert in MySql check our queries and said everything was fine, expect for his suggestion to use InnoDb over Myisam, which we did. Also, we are currently using Nginx in frontend, Apache for backend processes (for PHP) and also use Memcached. We are running on Amazon EC2 (Large instance). But guess their servers come with defaults and changes like SSD cannot be done. So you guys are basically suggesting a faster read / write process?
Yes i do recommend to use SSD, it might be what you need, or you need to take a look at load balancing options
What hardware (virtual or not) is this on and how much RAM is available to the database? Are you bogging down when you are inserting or pulling data during your compiling operations? Also can you explain what 1 of the 10,000/60,000 records would look like, table structure, etc.? 60,000 is not a large dataset and you shoudn't be bottlenecked to a 30 minute operation on something this small. 60M is big, 60K is pretty small.