• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 49
  • Last Modified:

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.
0
motioneye
Asked:
motioneye
  • 3
  • 2
2 Solutions
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now