MySQL maintenance - how to clear the buffer?

We have a server running our PHP/MySQL-based web application which is SLOW.

My predecessor says:

We used to do database maintenance, which used to clear the buffer, cached and unwanted variables.

And I wonder what on earth he means with that statement? Does he mean a simple optimize of the tables? Or the query cache? I understand MySQL but don't really know what he is describing.

I would appreciate any pointers.

If their maintenance operations were performed on a running server, I can only think of a "flush table" (From MySQL manual: Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache).

But maybe they were just restarting the server ...

For the "slow" part, assuming you're running an unix system, you could try to use mysql-tuning-primer or mysqltuner.pl to have some clue about what you could change in your server configuration

I cringe at this idea of database maintenance involving clearing the buffer to increase speed. If your system is crawling and this is the answer, I'm skeptical that things were done properly.

The proper way to approach this is to find out what your bottle neck is. It's usually either disk (often an extension of insufficient memory allocation) or CPU.

What's your database load? Should you be using InnoDB tables if you're not? Are the queries written efficiently?

"How to setup a MySQL server under load" is a big book of information. Start with the assumption that everything is wrong, and consider getting an outsider to spend an hour or two looking at it for you.

Here is something else to consider:

If majority of tables is InnoDB, you may want to consider setting the following in /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct=0;

In MySQL 5.1 and back, the default is 90;
In MySQL 5.5, the default is 75;

Setting innodb_max_dirty_pages_pct to zero(0) keeps InnoDB data pages maximally flushed to disk (up to 99.1 %) from the innodb buffer pool.

Without tweaking anything else, this also provides for a faster shutdown of mysqld in the presence of loads of InnoDB data since mysqld flushes the innodb buffer pool on shutdown.

You can actually set this on the fly without a mysql restart:

mysql> SET GLOBAL innodb_max_dirty_pages_pct=0;