4

I'm running very hard php script on my cheap virtual machine, which has about half million page views a week so it's a bit problematic. I restart my mysql twice a day. That wouldn't be problem, but when restarting mysql 

/etc/init.d/mysql restart

it restarts and then it sometimes says that Table './forum/phpbb_posts' is marked as crashed and last (automatic?) repair failed

This table has nothing to do with that hard script, however it's 250 000 (1.8 gig) table which seems to crash. It takes about 14 minutes to repair that one.

Is there a safer way to stop mysql? I know I should do backups, and I do so, but only once a day and datas are still growing (not fast these days)

Currently running on Debian GNU/Linux 6.0

genesis
  • 343
  • 4
  • 15
  • 1
    Why do you restart mysql twice a day? Also, have you considered moving to InnoDB - it does not have such problems by design. – rvs Oct 07 '11 at 21:18
  • @rvs: Yes I have had. I'll probably try to do so. I restart mysql twice a day when server is overloaded. – genesis Oct 07 '11 at 21:20
  • 2
    Define 'overloaded'. Restarting is probably not the right answer. Perhaps you have your memory values set wrong or something. – Zoredache Oct 07 '11 at 22:41
  • @Zoredache: I've got swap 1GB. overlaoded? when I try `top` it hits 9.XX AVG (5 minutes) – genesis Oct 08 '11 at 00:05
  • 1
    Ok, but why is it doing that? Try running mytop or something, perhaps you have a badly design query that is periodically running. – Zoredache Oct 08 '11 at 00:12
  • @Zoredache: "I'm running very hard php script " up to 200 queries per page – genesis Oct 08 '11 at 00:18
  • I'd suggest you either need to do some work on the code (200 queries per page is the result of poor design) or upgrade the server (which you've told us nothing about). Perhaps even consider doing both. – John Gardeniers Oct 10 '11 at 01:31

3 Answers3

4

Shutting down mysqld can be a time-consuming task. One of the biggest concerns is that all data is properly flushed and all tables closed.

There are two way to ensure proper closure of data. It falls into two basic categories:

CATEGORY #1: All the data is MyISAM

If absolutely all data is MyISAM, you should make sure InnoDB is totally disabled with this

[mysqld]
skip-innodb

Using this option on startup bypass any InnoDB protocols for crash recovery cycles that are normally executed on startup. Combine this with the fact that MyISAM data is never cached. Only the index pages are stored in the MyISAM Key Cache (sized by key_buffer_size). Thus, you have fast startup and fast shutdown.

CATEGORY #2: All or the majority of the data is InnoDB

If most or all of the data is InnoDB, you have to watch for the amount of space you have configured for innodb_buffer_pool_size. The bigger the innodb_buffer_pool_size, the longer it will take to flush out dirty pages.

Sidenote: Performing mysqldumps of InnoDB tables will automatically trigger the flushing of any lingering dirty pages in the Buffer Pool that belong to the table being dumped.

Whether you are mysqldumping all the InnoDB tables or just shutting down mysql, there is only one to hasten the process of flushing dirty pages.

You must set the innodb_max_dirty_pages_pct. By default, it is 90 for MySQL 5.0/5.1. For MySQL 5.5, the default is 75. Surprising, just set innodb_max_dirty_pages_pct to 0. That will keep the number of dirty pages in the Buffer Pool to a bare minimum. This may slight increase disk I/O but still within tolerable levels.

You must do two things:

Please place it in /etc/my.cnf

[mysqld]
innodb_max_dirty_pages_pct=0

Second, you will not have to restart mysql. Just execute this as the root user:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Server load may spike a little and stay that way until the buffer pool is less than 1% dirty pages.

Give it a Try !!!

UPDATE 2013-03-04 21:31 EST

You should also run this MySQL command first

SET GLOBAL innodb_fast_shutdown = 0;

This will cause a complete flush of everything in the InnoDB Transaction Logs (ib_logfile0, ib_logfile1) followed by

service mysql stop
Community
  • 1
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
2

503 the website to stop activity (.htaccess maintenance mode) and start top to monitor mysqld cpu usage

mysqladmin -p flush-tables

watch mysql activity (may take several minutes, depending on your installation) and when it tapers off

mysqladmin -p flush-tables

to just be sure and then

mysqladmin -p shutdown

This sequence ensures that your website ceases to create more database requests, tells mysql to flush data in memory and transaction log files to the database tables and then initiates a proper database daemon shutdown.

Fiasco Labs
  • 563
  • 4
  • 10
0

What about breaking up the stop and start regiment? Maybe the restart is not giving the database enough time to get to a quiescent stop before it initiates a start?

i.e. 

/etc/init.d/mysql stop;
sync;
sleep 30;
/etc/init.d/mysql start
genesis
  • 343
  • 4
  • 15
mdpc
  • 11,856
  • 28
  • 53
  • 67