Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm seeking help to understand what happens when the following command line is executed:

root@prodn$ service mysqld stop

Yes, it shuts down the MySQL server so access to it is no longer available until the service is started again. However, more specifically, is there anything else that happens when the service is stopped? Forgive my novice-ness here but when mysqld is restarts, does it mean that logs were flushed, some memory freed, caches emptied, etc.?

The reason I ask is the following:

Our data warehouse DB is a MySQL DB and in the past 4 months, it has taken on average 8.5 hours.

Last Wednesday, I've stopped the mysql service, and then restarted it after 30 minutes. Since, I begin to notice a massive improvement on overall performance -- SELECT/INSERT/UPDATE/DELETE processes were more efficient. DW finished almost 4 hours earlier with the same number of rows of data

However, with each passing day, 15-20 minutes is somehow added to the finish time. So, I suspect I may have to restart the service weekly.

Is there an explanation for this behavior? I don't know what other questions are relevant but it'd be brilliant to know what happens when mysqld service restarts.

Can anyone shed some light on this please?

share|improve this question
1  
try asking on serverfault or dba. –  gbjbaanb Apr 23 '13 at 10:29
    
1  
Sounds like you have lots of data in memory that gets committed. dev.mysql.com/doc/refman/5.0/en/server-shutdown.html –  ethrbunny Apr 23 '13 at 10:47
    
@dat789, right place to look for the answers is already given by ethrbunny. thats the first place u shud go to find it. –  Mrigesh Raj Shrestha Apr 23 '13 at 11:10
    
Many thanks guys! –  dat789 Apr 23 '13 at 13:59
add comment

migrated from stackoverflow.com Apr 23 '13 at 11:06

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

When you issue service mysql stop, a lot more happens than just cutting off DB Connectivity. The link in the comment from @ethrbunny already explains what things happens.

I would like to focus on one particular aspect: The InnoDB Buffer Pool. InnoDB has to flush the InnoDB Buffer Pool's dirty pages. If you want to know how much, run this before shutdown:

SELECT CONCAT(dpbytes/power(1024,expo),' ',SUBSTR(units,expo*2+1,2)) DirtyPages FROM
(SELECT dpbytes,FLOOR(LOG(dpbytes)/LOG(1024)) expo FROM
(SELECT dirty_pages*page_size dpbytes FROM
(SELECT VARIABLE_VALUE dirty_pages FROM information_schema.global_status
WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_dirty') AAA,
(SELECT VARIABLE_VALUE page_size FROM information_schema.global_status
WHERE VARIABLE_NAME='Innodb_page_size') BBB) AA) A,(SELECT ' BKBMBGBTB' units) B;

This will tell you how much data needs to be flushed from the InnoDB Buffer Pool.

Please keep in mind that InnoDB has many moving parts in the system tablespace (the file ibdata1). Click here to see the Pictorial Representation of the entire InnoDB Infrastructure.

Some of transactional information is written in such a way that Crash Recovery is performed when you run service mysql start.

OPTIONAL

You can get all data flushed and all transaction cleanly committed from ibdata1 and the Transaction Logs (ib_logfile0,ib_logfile1) by running this

SET GLOBAL innodb_fast_shutdown = 0;

before running

service mysql stop

Give it a Try !!!

UPDATE 2013-04-24 07:17 EDT

Since you have MyISAM as the main storage engine, then the only thing happening is the flushing of index changes to all .MYI files that have changes pending.

I would also like recommend that the next you shutdown mysqld, run this beforehand in another session:

tail -f /var/log/mysqld.log

and watch the messages that go by and see it it tells you what mysqld is doing with any storage engine. If you have absolutely no InnoDB tables in use, then you should think about disabling InnoDB with

[mysqld]
skip-innodb

as this will make for a quicker startup and possibly a quicker shutdown.

share|improve this answer
    
Thanks for this. I should have been more specific about one other aspect that I did not mention -- we are using the MyISAM engine on most of our tables. Does this make any difference compared with InnoDB? Is there a way to do the same without restarting mysqld? If not, this would mean that I'd have to annoy our systems admin from time to time. Also, the reason is rather simple -- after each passing day since last restart, SELECT/UPDATE/INSERT operations seems to be delayed by 25-40 mins. A restart brings it back to its 'optimized' time. I can't explain why. –  dat789 Apr 24 '13 at 8:10
    
I strongly believe InnoDB was already disabled. See extract: 130422 15:35:31 mysqld_safe Starting mysqld daemon with databases from /srv/mysqldb/mysql 130422 15:35:33 [Note] Plugin 'FEDERATED' is disabled. 130422 15:35:33 [Note] Plugin 'InnoDB' is disabled. 130422 15:35:33 [Note] Event Scheduler: Loaded 0 events 130422 15:35:33 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.17' socket: '/srv/mysqldb/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) How can we then flush index changes to all of the .MYI without restarting? Possible? –  dat789 Apr 25 '13 at 8:42
add comment

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.