Avatar of D_wathi
D_wathi
Flag for India asked on

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.
SugarCRMDatabasesLinux NetworkingMySQL Server

Avatar of undefined
Last Comment
D_wathi

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
noci

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
D_wathi

ASKER
thank you very much for the inputs, can we do this directly on production server because already users are experiencing slowness, take the complete backup do indexing in the development server and then import  to production if this is done then during this indexing on development server production server some part of data would have changed as users will be working on it , how to handle this situation please help to understand this and recommend steps.
SOLUTION
noci

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
D_wathi

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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
D_wathi

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck