Hi, We have a live website that runs fine. We now have wrote some scripts to report on the data. Due to the large amount of data running them brings the server to a standstill. We have now created 2 separate users , one for "live" and one for "reports". We are running mySQL v4. Is there a way of prioritising one user over another? Or even prioritising the queries themselves? Or another method to ensure our live server doesn't get bogged down by a report query? Thanks
I'm not aware of any way of prioritizing who gets to use the actual physical resources of the server, especially in mysql. The CPU, RAM, and Disk drives are all going to be limiting factors that you'll have to contend with. The first thing I'd look at is to determine if your database is indexed properly. This is the most common cause I've found with people mentioning that their reports are running slowly. Make sure you have indexes on your primary/foreign keys, and any columns you're restricting the data on. Also, ensure you're query is structured properly and not doing unnecessary joins or doesnt have any cartesian products being created. If you do have your database indexed properly and a well formed query, then you likely have a huge amount of data your mining or you need more hardware resources. Please doublecheck what I mentioned above though and ensure your database is indexed. I've seen companies spend tens of thousands of dollars to buy better hardware when all that was needed was a simple index. If you do think you need more hardware resources, then you may have to create a "data warehouse" on another physical piece of hardware. You can then query this data without impacting your production database, however the data is usually not real time then. Another option would be to backup and restore your mysql database as it stands to another server each day and query off of it.
On the extreme you could create a hard seperation using two Mysql servers (or instances) Then you keep the reporting server in sync with the live server using a dump and restore strategy or by making the Live server a master (read/write) node in a cluster and the reporting server a slave (read only) node in the cluster. As an alternative you could review the algorithms that are used to create the reports, these might need some work. A better algorithm might give you a 10-100-1000 fold performance increase.