2012-05-01

Putting Mysql information_schema to work

A colleague of mine asked me to help him to put a PHP routine he found on the Net into production.
‘What does it do’ I asked.
‘It optimizes all tables in my Mysql database’ - he replied.
‘Send it over to me’ I said. I have a soft spot for automatic database admin routines, and I thought we could do something with this code. What I found was a well written piece of PHP code with an SQL query driving the ‘database optimization’ :
select * from information_schema.TABLES WHERE TABLE_SCHEMA LIKE ('dbname%')
But instead of implementing this PHP routine I used my PHP job scheduler, this is something the job iterator lend itself extremely well to.  First I created a job to optimize one table, I added an analyze just for fun and called the job DBA_optilyze.xml:
Here you see the job with an empty job iterator <forevery/>. If you include this job without a <forevery> iterator that job will fail. As you see the iterator needs two columns ‘db’ and ‘table’ which are used to create the Mysql requests within the <sql> tag.
Now we only have to create a schedule and include DBA_optilyze.xml:
Here we have a schedule ‘Optimize_ACTAPRJ.xml that includes the job ‘Optilyze’ optimizing a database called ACTAPRJ. Note, we create a job iterator with two columns ‘db’ and ‘table’, which are the two column the  job DBA_optilyze use.
Let’s take the schedule for a spin:
Running this schedule will optimize and analyze the tables in the database ACTAPRJ one by one creating a log file like this one for each table:
That’s all. If you are interested in my  job scheduler check out my posts on Job Scheduling with PHP .
p.s.
If you are in hurry you can declare the job iterator <forevery parallel=’yes’>  that will fork one thread for each row in the job iterator running all jobs at once instead of one by one.

No comments:

Post a Comment