measures and steps to be taken to perform database indexing
Dear Experts
We are having web based CRM application running on LAMP stack web server and data base servers are running two different server. Our software developers have decided to create indexes based on their analysis due to slow queries observations, the database server is mysql 5.4 version.
As it is production server the indexing will affect the work and also the tables size is huge and overall database size is 65GB. Please suggest the best practice, following is the approach can think about but again not sure if this is best recommended but there is a challenge as mentioned, please suggest on how such cases are handled.
1.Setup new webserver and mysql server with same config and same versions and take backup of production server database and import it to the new database server and create indexes and test, this process may take few days to week and by the time indexing gets completed which may take few days during this period production server database would have undergone changes as users are working on production instance, can you please suggest the best way to handle this.
We are having web based CRM application running on LAMP stack web server and data base servers are running two different server. Our software developers have decided to create indexes based on their analysis due to slow queries observations, the database server is mysql 5.4 version.
As it is production server the indexing will affect the work and also the tables size is huge and overall database size is 65GB. Please suggest the best practice, following is the approach can think about but again not sure if this is best recommended but there is a challenge as mentioned, please suggest on how such cases are handled.
1.Setup new webserver and mysql server with same config and same versions and take backup of production server database and import it to the new database server and create indexes and test, this process may take few days to week and by the time indexing gets completed which may take few days during this period production server database would have undergone changes as users are working on production instance, can you please suggest the best way to handle this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the input, please correct me if iam wrong in the understanding on following
1. setup new server LAMP stack (testing)bring entire database of production to here
2. do indexing here and observe the effects
3.go back the production and do the same indexing based on observed facts testing server.
please suggest me is this correct or help me with correct steps please.
1. setup new server LAMP stack (testing)bring entire database of production to here
2. do indexing here and observe the effects
3.go back the production and do the same indexing based on observed facts testing server.
please suggest me is this correct or help me with correct steps please.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
please help me understand the representative queries that you have mentioned.
2. perform indexing on test server observe and then do the same in production is this correct please suggest.
2. perform indexing on test server observe and then do the same in production is this correct please suggest.
ASKER