4

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.

Improve this question

3 Answers 3

Reset to default
3

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

Improve this answer
1
  • 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, 2011 at 14:25
4

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.

Improve this answer
1
  • 1
    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, 2012 at 2:17
2

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;
Improve this answer
2
  • Why is it only 99.1% instead of 100%?
    – Pacerier
    Mar 12, 2017 at 15:18
  • If you want your changes flushed asap, then performance wise it's probably better to set innodb_max_dirty_pages_pct_lwm=1 (down from the default of 10) and leave innodb_max_dirty_pages_pct at its default.
    – Brian Cryer
    Aug 7, 2020 at 13:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .