Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We have a server running our web app (PHP / MySQL) which is SLOW.

My predecessor says that:

"We use to do database maintenance, which use 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.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer
    
FLUSH TABLES? Thanks, I didn't think of that. Yeah, they might simply have been restarting the MySQL service. The tuning scripts you mention are great BTW and I'm already all over them :-) –  Dougal Feb 22 '11 at 14:25

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;
share|improve this answer

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.

share|improve this answer
    
Despite the present down-vote, I'm sticking with this one -- things don't get faster because you clear cached data. There's not enough information in the question to let me have a hint of why it's slow, but there is enough to let me know that the predecessor's path isn't the one follow. –  Jeff Ferland Jan 20 '12 at 2:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.