elabuwa Posted March 4, 2011 Share Posted March 4, 2011 Hi guys, This is the scenario. My app runs fine. The number of transactions entered for a day in my client's org is huge. Say min of 200 - 300 receipts, payments etc. However, the server tends to be slowed down when we are calling reports with a large date range, possibly coz the server has to go through each every record. Even though i have written the sql command at the best way possible to minimize processing time, some reports take up to 20 mins. The server is an avg computer with dual core 1.6 ghz ( i think), 1 gb ram, 320 hdd. It is an assembled avg computer used as a server. At the begining all was fine coz the number of records were small. Now the database has expanded up 30000- 35000 records in one table, due to the large number of records. I need a solution to minimize reporting time (possible upgrade may solve the case), but when reports are called, other users of the system cannot even enter records because the server is busy generating the report. What is the best way? I was thinking of using two servers 1. Data storage - where all the data are stored 2. Report server - all data from data storage server is copied to the report server @ a predetermined interval, possible every 30 mins. The users who need reports, can log in to the report server and generate reports. What are your thoughts? I am opened for any type of suggestion.. Your help is much appreciated. Cheers The budget for the client is not too high, so buying an actual server could be impracticle. Link to comment https://forums.phpfreaks.com/topic/229553-mysql-server-mirroring-duplicating-copying/ Share on other sites More sharing options...
mikosiko Posted March 4, 2011 Share Posted March 4, 2011 IMHO... wrong approach.... My app runs fine. according to the scenario that you described your application is not running fine... 30,000 records shouldn't be causing the problems that you mention neither your "server"... personally I will focus in: - Review your Data Model design (bad design could be lead to inefficient SQL's among other things) - Review your Table/Views/etc design - Review your Index strategy and selectivity - Isolate and test your problematic SQL's (Explain/Profiling) your 20 minutes report issue is based on some of these factors most likely imho. Link to comment https://forums.phpfreaks.com/topic/229553-mysql-server-mirroring-duplicating-copying/#findComment-1182789 Share on other sites More sharing options...
elabuwa Posted March 7, 2011 Author Share Posted March 7, 2011 Thanks Miko. I will reviewing the thingis you mentioned. Link to comment https://forums.phpfreaks.com/topic/229553-mysql-server-mirroring-duplicating-copying/#findComment-1183974 Share on other sites More sharing options...
ignace Posted March 7, 2011 Share Posted March 7, 2011 Start here: - Review your Index strategy and selectivity It takes your server 20 mins because it can't find a valid index and thus creates a temporary table which it joins with all other temporary tables and creates you a nice cartesian product to process. Post your table/queries here and we can give you some advice on which indexes you need. Link to comment https://forums.phpfreaks.com/topic/229553-mysql-server-mirroring-duplicating-copying/#findComment-1184112 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.