Mysql 5.6 - Setup weekly db maintenance

Guys,
I'm running Mysql 5.6  on windows server and I would like to know how  to setup db maintenance and optimization on weekly or monthly basis.
motioneyeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
What kind of maintenance and optimization are you talking about specifically? Also, what are your table types (MyISAM, InnoDB, or something else) ?
0
motioneyeAuthor Commented:
Hi,
I have both MyISAM, InnoDB table in our system
0
gr8gonzoConsultantCommented:
Okay. What about my first question?
0
motioneyeAuthor Commented:
Hi,
I'm mostly talking about repair, optimize, analyze and what ever that it needed  to improve Mysql db performance.I also need to know if those  tasks will prevent  users from connecting to the database or not, besides what has been mentioned by me here, is there any additional routine tasks we can do to maintain and improve Mysql ?
0
gr8gonzoConsultantCommented:
So before you go too far down this pathk, I'd make sure you know exactly what the commands do, and what you actually NEED to run. For example, REPAIR TABLE isn't a maintenance task, and not something you should be regularly running - that's a recovery task. As a general rule, you shouldn't try to optimize a table unless it's seen a massive amount of data updates (inserts, updates, and/or deletes) since the last time you've optimized (or created) the table. I wouldn't bother optimizing anything unless you're seeing performance issues that you have absolutely confirmed to be related to indexes that are severely incorrect (e.g. MySQL is choosing the wrong index during a query and resulting in a slower query execution), OR unless you've just deleted a billion rows and want to recover that space.

Here's a good blog article that talks about the operations and what they actually do:
https://blog.pythian.com/mysql-back-to-basics-analyze-check-optimize-and-repair/

It's important to note that most of these operations lock a table in some way - either read or write lock, depending on whether it's MyISAM or InnoDB. For example, running OPTIMIZE TABLE on InnoDB actually -rebuilds- the table from scratch, which means that there's a lock on the entire table for the duration of that procedure.

For some context, I regularly work on databases that are hundreds of gigabytes in size and have a mix of MyISAM and InnoDB tables (about 5%/95% split) and and see hundreds of thousands of inserts and updates and deletes every day and we rarely ever run any of these procedures.

The issues that are addressed by these procedures are rarely ever the cause of poor database performance. More often than not, poor DB performance is caused by slow queries, poorly-defined indexes and/or table schemas (e.g. underuse or overuse of normalization), poor client practices (e.g. persistent connections), poor I/O or resources (e.g. DB server sitting on a slow or very busy HDD), or a combination of all of those. Take a look at my article on MySQL performance:

https://www.experts-exchange.com/articles/1250/3-Ways-to-Speed-Up-MySQL.html

All that said, if you still want to go down the road of doing this, then the simplest solution would be to install PHP or some other basic scripting language that natively supports MySQL connections (or if you're more comfortable with .NET, there are .NET connectors for MySQL), and then write a simple script that would connect to the database and run the queries. Then use Windows Scheduler to run the job on the desired basis.

Example script in PHP:
<?php
$db = new mysqli("HOSTNAME", "USERNAME", "PASSWORD", "DATABASE NAME");
$db->query("OPTIMIZE TABLE table_X");
$db->query("OPTIMIZE TABLE table_Y");
...

Open in new window


I also need to know if those  tasks will prevent  users from connecting to the database or not
None of the queries will prevent users from connecting to the database, but they might prevent the users from being able to query a table if that table is being optimized/repaired.

is there any additional routine tasks we can do to maintain and improve Mysql ?
MySQL is usually very good at maintaining itself, which is why I don't recommend doing anything proactively. If you want to be proactive, make sure you enable the slow query log (mentioned in my "3 Ways..." article that I linked to above) and review it occasionally so you can proactively address those issues.

If you have overall performance problems, ensure you've profiled them thoroughly so that you can tell where the problems are and that you are not spending precious time on things that might not even be problems.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS

From novice to tech pro — start learning today.