Re: expire_logs_days
Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: expire_logs_days
-- Forwarded message -- From: Jake Peavy [EMAIL PROTECTED] Date: May 4, 2007 7:41 AM Subject: Re: expire_logs_days To: Baron Schwartz [EMAIL PROTECTED] On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. -jp
Re: expire_logs_days
Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. Not according to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html: This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Baron Schwartz wrote: I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 OK even with a slave connected to a master with expire_logs_days, I still see the desired affect. I've made a note on the bug - let's continue discussion on there? Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. Not according to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html: This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. Yes, this quote refers to file locking/concurrent access to the bin files. What I was getting at is if the slave has fallen behind and hasn't yet parsed some particular bin files, you wouldn't want to remove them from the master until the slave I/O thread was able to parse them. Otherwise your slave would lose those database changes and thus be out of sync. When purging master logs in a replicated setup one must first examine the result of SHOW SLAVE STATUS and only PURGE MASTER LOGS up to the log indicated by Master_Log_File. -- -jp Chuck Norris frequently donates blood to the Red Cross. Just never his own.
Re: expire_logs_days
Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. Not according to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html: This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. Yes, this quote refers to file locking/concurrent access to the bin files. What I was getting at is if the slave has fallen behind and hasn't yet parsed some particular bin files, you wouldn't want to remove them from the master until the slave I/O thread was able to parse them. Otherwise your slave would lose those database changes and thus be out of sync. When purging master logs in a replicated setup one must first examine the result of SHOW SLAVE STATUS and only PURGE MASTER LOGS up to the log indicated by Master_Log_File. Understood. But that is a reason for DBA caution, not a reason for disabling the feature as you wrote above. If the feature were disabled when there are any slaves connected, the manual should say so. It looks like other people have found the feature to work when there are slaves, so I'm sure it's just some configuration or other problem with my (and many other people's) setup. -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Paul DuBois wrote: At 8:46 PM -0400 5/2/07, Baron Schwartz wrote: Ofer Inbar wrote: That's a good point, though probably a minor one: At most you would end up with one binary logfile that's old and not deleted. As soon as you create a new one, that one would be deleted (if this feature works). In our case, we flush logs nightly. (but hardly ever restart mysqld) -- Cos We roll many logs every day, but never restart unless we have to. So for us, it looked like it genuinely wasn't working on roll; I have no idea about restart. I have a 4.1.13 server that's been up for 100 days. It has expire_logs_days, and I have 7 binlog files. I do flush my logs once a day to force the logs to rotate. So that's one confirmation that it works, at least in 4.1.13. :-) This seems to work just fine on 5.0.40 as well: medusa:/usr/local/mysql/data root# ls -l total 58352 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102514 May 3 10:55 medusa-bin.01 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.02 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.03 -rw-rw1 mysql wheel102517 May 3 10:56 medusa-bin.04 -rw-rw1 mysql wheel 81473 May 3 10:56 medusa-bin.05 -rw-rw1 mysql wheel 375 May 3 10:56 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test medusa:/usr/local/mysql/data root# cat /etc/my.cnf [mysqld] log-bin max_binlog_size = 100K expire_logs_days = 2 medusa:/usr/local/mysql/data root# date Thu May 3 10:58:22 BST 2007 medusa:/usr/local/mysql/data root# date Sun May 6 10:58:42 BST 2007 medusa:/usr/local/mysql/data root# while [ 1 ] do mysql -u root test -e 'insert into binlog_test (i,j) values (1,1)' done ^C medusa:/usr/local/mysql/data root# ls -l total 57888 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.05 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.06 -rw-rw1 mysql wheel 55853 May 6 10:59 medusa-bin.07 -rw-rw1 mysql wheel 225 May 6 10:59 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test I declare 'No Bug Here' :) At least on the current versions of 5.0 (tested on 5.0.40), anyway. Cheers! Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Mark Leith wrote: Paul DuBois wrote: At 8:46 PM -0400 5/2/07, Baron Schwartz wrote: Ofer Inbar wrote: That's a good point, though probably a minor one: At most you would end up with one binary logfile that's old and not deleted. As soon as you create a new one, that one would be deleted (if this feature works). In our case, we flush logs nightly. (but hardly ever restart mysqld) -- Cos We roll many logs every day, but never restart unless we have to. So for us, it looked like it genuinely wasn't working on roll; I have no idea about restart. I have a 4.1.13 server that's been up for 100 days. It has expire_logs_days, and I have 7 binlog files. I do flush my logs once a day to force the logs to rotate. So that's one confirmation that it works, at least in 4.1.13. :-) This seems to work just fine on 5.0.40 as well: medusa:/usr/local/mysql/data root# ls -l total 58352 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102514 May 3 10:55 medusa-bin.01 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.02 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.03 -rw-rw1 mysql wheel102517 May 3 10:56 medusa-bin.04 -rw-rw1 mysql wheel 81473 May 3 10:56 medusa-bin.05 -rw-rw1 mysql wheel 375 May 3 10:56 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test medusa:/usr/local/mysql/data root# cat /etc/my.cnf [mysqld] log-bin max_binlog_size = 100K expire_logs_days = 2 medusa:/usr/local/mysql/data root# date Thu May 3 10:58:22 BST 2007 medusa:/usr/local/mysql/data root# date Sun May 6 10:58:42 BST 2007 medusa:/usr/local/mysql/data root# while [ 1 ] do mysql -u root test -e 'insert into binlog_test (i,j) values (1,1)' done ^C medusa:/usr/local/mysql/data root# ls -l total 57888 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.05 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.06 -rw-rw1 mysql wheel 55853 May 6 10:59 medusa-bin.07 -rw-rw1 mysql wheel 225 May 6 10:59 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test I declare 'No Bug Here' :) At least on the current versions of 5.0 (tested on 5.0.40), anyway. I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? Thanks Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
At 9:55 PM -0400 5/3/07, Baron Schwartz wrote: Mark Leith wrote: Paul DuBois wrote: At 8:46 PM -0400 5/2/07, Baron Schwartz wrote: Ofer Inbar wrote: That's a good point, though probably a minor one: At most you would end up with one binary logfile that's old and not deleted. As soon as you create a new one, that one would be deleted (if this feature works). In our case, we flush logs nightly. (but hardly ever restart mysqld) -- Cos We roll many logs every day, but never restart unless we have to. So for us, it looked like it genuinely wasn't working on roll; I have no idea about restart. I have a 4.1.13 server that's been up for 100 days. It has expire_logs_days, and I have 7 binlog files. I do flush my logs once a day to force the logs to rotate. So that's one confirmation that it works, at least in 4.1.13. :-) This seems to work just fine on 5.0.40 as well: medusa:/usr/local/mysql/data root# ls -l total 58352 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102514 May 3 10:55 medusa-bin.01 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.02 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.03 -rw-rw1 mysql wheel102517 May 3 10:56 medusa-bin.04 -rw-rw1 mysql wheel 81473 May 3 10:56 medusa-bin.05 -rw-rw1 mysql wheel 375 May 3 10:56 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test medusa:/usr/local/mysql/data root# cat /etc/my.cnf [mysqld] log-bin max_binlog_size = 100K expire_logs_days = 2 medusa:/usr/local/mysql/data root# date Thu May 3 10:58:22 BST 2007 medusa:/usr/local/mysql/data root# date Sun May 6 10:58:42 BST 2007 medusa:/usr/local/mysql/data root# while [ 1 ] do mysql -u root test -e 'insert into binlog_test (i,j) values (1,1)' done ^C medusa:/usr/local/mysql/data root# ls -l total 57888 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.05 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.06 -rw-rw1 mysql wheel 55853 May 6 10:59 medusa-bin.07 -rw-rw1 mysql wheel 225 May 6 10:59 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test I declare 'No Bug Here' :) At least on the current versions of 5.0 (tested on 5.0.40), anyway. I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? Not in my case. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Hi, Ofer Inbar wrote: There's a system variable called expire_logs_days that lets you set a number of days to keep binary logs, and automatically delete logs older than that. I've heard rumors that using this feature is problematic. I notice that in the MySQL documentation about binary logging, it tells you to use purge master logs to delete old logs, and does not mention the expire_logs_days variable as another option. Is there a reason for this omission, or is it safe to use? -- Cos Actually, the manual does mention the variable, but it doesn't work for us. We run a nightly cron job that just runs /usr/bin/mysql -e PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 9 DAY) Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: expire_logs_days
-Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 02, 2007 7:55 AM To: Ofer Inbar Cc: mysql@lists.mysql.com Subject: Re: expire_logs_days Hi, Ofer Inbar wrote: There's a system variable called expire_logs_days that lets you set a number of days to keep binary logs, and automatically delete logs older than that. I've heard rumors that using this feature is problematic. I notice that in the MySQL documentation about binary logging, it tells you to use purge master logs to delete old logs, and does not mention the expire_logs_days variable as another option. Is there a reason for this omission, or is it safe to use? -- Cos Actually, the manual does mention the variable, but it doesn't work for us. We run a nightly cron job that just runs /usr/bin/mysql -e PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 9 DAY) We do the same thing, based on the rumors I read at the time I set it up. (Where rumors means that googling for expire_logs_days reveals many with problems and not much good news.) I would recommend you figure out the value for n day above based on keeping your binlog partition about 50% full. This will allow you to go back more days in the backup scheme if something went wrong with your backups. It also allows more room for large import operations which would otherwise fill the /binlog partition. 21 days is about right for our usage model with a 32G binlog partition, but YMMV. # df -h /binlogs /data FilesystemSize Used Avail Use% Mounted on /dev/sdb1 32G 15G 16G 48% /binlogs /dev/sdb2 237G 50G 176G 22% /data # cat /etc/cron.mysql/20-purgemasterlogs #!/bin/sh /usr/bin/mysql --defaults-file=/root/.my.cnf -e 'show master logs; purge master logs before date_sub(now(), interval 21 day); show master logs;' /var/log/20-purgemasterlogs.log 21 Tim Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Baron Schwartz [EMAIL PROTECTED] wrote: Actually, the manual does mention the variable, but it doesn't work for us. We run a nightly cron job that just runs [purge master logs] When you say it doesn't work for us do you mean that you tried it? In what way did it not work? Tim Lucia [EMAIL PROTECTED] wrote: We do the same thing, based on the rumors I read at the time I set it up. (Where rumors means that googling for expire_logs_days reveals many with problems and not much good news.) Has anyone here had direct experience with expire_logs_days either working or not working? What happened? (note: I'm running 5.0.24) -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] OSI is a beautiful dream, and TCP/IP is living it! -- Einar Stefferud [EMAIL PROTECTED], IETF mailing list, 12 May 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Hi, I'm experience using expire_log_days and don't work. I set this parameters in the CNF and when the time of ( for example 5 days) is in, don't delete anything. On my expirience, this parameters don't work ( 5.0.27). Regards Juan On 5/2/07, Ofer Inbar [EMAIL PROTECTED] wrote: Baron Schwartz [EMAIL PROTECTED] wrote: Actually, the manual does mention the variable, but it doesn't work for us. We run a nightly cron job that just runs [purge master logs] When you say it doesn't work for us do you mean that you tried it? In what way did it not work? Tim Lucia [EMAIL PROTECTED] wrote: We do the same thing, based on the rumors I read at the time I set it up. (Where rumors means that googling for expire_logs_days reveals many with problems and not much good news.) Has anyone here had direct experience with expire_logs_days either working or not working? What happened? (note: I'm running 5.0.24) -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] OSI is a beautiful dream, and TCP/IP is living it! -- Einar Stefferud [EMAIL PROTECTED], IETF mailing list, 12 May 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Ofer Inbar wrote: Baron Schwartz [EMAIL PROTECTED] wrote: Actually, the manual does mention the variable, but it doesn't work for us. We run a nightly cron job that just runs [purge master logs] When you say it doesn't work for us do you mean that you tried it? In what way did it not work? The logs stayed in the directory. They did not get purged. Tim Lucia [EMAIL PROTECTED] wrote: We do the same thing, based on the rumors I read at the time I set it up. (Where rumors means that googling for expire_logs_days reveals many with problems and not much good news.) Has anyone here had direct experience with expire_logs_days either working or not working? What happened? (note: I'm running 5.0.24) We are upgrading now, but at the time, were running 5.0.26. There is no bug report related to this at the moment, which is kind of amazing given how many people have said it doesn't work, but a support engineer is looking into it, and so am I in my copious spare time ;-) Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Juan Eduardo Moreno wrote: Hi, I'm experience using expire_log_days and don't work. I set this parameters in the CNF and when the time of ( for example 5 days) is in, don't delete anything. On my expirience, this parameters don't work ( 5.0.27). I am testing this now (on 5.0.40) and will see how it works out. Cheers! Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Mark Leith wrote: Juan Eduardo Moreno wrote: Hi, I'm experience using expire_log_days and don't work. I set this parameters in the CNF and when the time of ( for example 5 days) is in, don't delete anything. On my expirience, this parameters don't work ( 5.0.27). I am testing this now (on 5.0.40) and will see how it works out. OK initial testing of this has shown no problems with the rolling over that happens when the server starts/stops: medusa:/usr/local/mysql/data root# ls -l total 41024 -rw-rw1 mysql wheel 5242880 May 5 22:34 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 10485760 May 5 22:34 ibdata1 -rw-rw1 mysql wheel 117 May 5 22:33 medusa-bin.02 -rw-rw1 mysql wheel 117 May 5 22:34 medusa-bin.03 -rw-rw1 mysql wheel 117 May 5 22:34 medusa-bin.04 -rw-rw1 mysql wheel98 May 5 22:34 medusa-bin.05 -rw-rw1 mysql wheel 160 May 5 22:34 medusa-bin.index -rw-rw1 mysql wheel 6051 May 5 22:34 medusa.err -rw-rw1 mysql wheel 5 May 5 22:34 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---2 mysql wheel68 Apr 21 06:09 test medusa:/usr/local/mysql/data root# mysqladmin -u root shutdown medusa:/usr/local/mysql/data root# date Sat May 5 22:35:07 BST 2007 medusa:/usr/local/mysql/data root# date Wed May 9 22:35:24 BST 2007 medusa:/usr/local/mysql/data root# ../bin/mysqld --user=mysql [1] 1867 medusa:/usr/local/mysql/data root# 070509 22:35:53 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-enterprise-gpl-5.0.40-osx10.4-i686/data/ is case insensitive 070509 22:35:53 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=/usr/local/mysql-enterprise-gpl-5.0.40-osx10.4-i686/data/medusa-bin' to avoid this problem. 070509 22:35:53 InnoDB: Started; log sequence number 0 43655 070509 22:35:54 [Note] ../bin/mysqld: ready for connections. Version: '5.0.40-enterprise-gpl-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Enterprise Server (GPL) medusa:/usr/local/mysql/data root# ls -l total 41000 -rw-rw1 mysql wheel 5242880 May 9 22:35 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 10485760 May 5 22:35 ibdata1 -rw-rw1 mysql wheel98 May 9 22:35 medusa-bin.06 -rw-rw1 mysql wheel75 May 9 22:35 medusa-bin.index -rw-rw1 mysql wheel 6341 May 5 22:35 medusa.err -rw-rw1 mysql wheel 5 May 9 22:35 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---2 mysql wheel68 Apr 21 06:09 test The only other one to test now is test that this also happens when a binary log rolls over. Do keep in mind that expire_logs_days only gets triggered at a) server start up b) the time a binary log has to roll over. If your binary logs do not roll over for quite a period of time (i.e are lower load systems) that still stay up for long periods - you might not see a log expired for some period. Anyway, I'll still check out binary log rollover as well.. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Mark Leith [EMAIL PROTECTED] wrote: Do keep in mind that expire_logs_days only gets triggered at a) server start up b) the time a binary log has to roll over. If your binary logs do not roll over for quite a period of time (i.e are lower load systems) that still stay up for long periods - you might not see a log expired for some period. That's a good point, though probably a minor one: At most you would end up with one binary logfile that's old and not deleted. As soon as you create a new one, that one would be deleted (if this feature works). In our case, we flush logs nightly. (but hardly ever restart mysqld) -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Ofer Inbar wrote: Mark Leith [EMAIL PROTECTED] wrote: Do keep in mind that expire_logs_days only gets triggered at a) server start up b) the time a binary log has to roll over. If your binary logs do not roll over for quite a period of time (i.e are lower load systems) that still stay up for long periods - you might not see a log expired for some period. That's a good point, though probably a minor one: At most you would end up with one binary logfile that's old and not deleted. As soon as you create a new one, that one would be deleted (if this feature works). In our case, we flush logs nightly. (but hardly ever restart mysqld) -- Cos We roll many logs every day, but never restart unless we have to. So for us, it looked like it genuinely wasn't working on roll; I have no idea about restart. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
At 8:46 PM -0400 5/2/07, Baron Schwartz wrote: Ofer Inbar wrote: Mark Leith [EMAIL PROTECTED] wrote: Do keep in mind that expire_logs_days only gets triggered at a) server start up b) the time a binary log has to roll over. If your binary logs do not roll over for quite a period of time (i.e are lower load systems) that still stay up for long periods - you might not see a log expired for some period. That's a good point, though probably a minor one: At most you would end up with one binary logfile that's old and not deleted. As soon as you create a new one, that one would be deleted (if this feature works). In our case, we flush logs nightly. (but hardly ever restart mysqld) -- Cos We roll many logs every day, but never restart unless we have to. So for us, it looked like it genuinely wasn't working on roll; I have no idea about restart. I have a 4.1.13 server that's been up for 100 days. It has expire_logs_days, and I have 7 binlog files. I do flush my logs once a day to force the logs to rotate. So that's one confirmation that it works, at least in 4.1.13. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
expire_logs_days
There's a system variable called expire_logs_days that lets you set a number of days to keep binary logs, and automatically delete logs older than that. I've heard rumors that using this feature is problematic. I notice that in the MySQL documentation about binary logging, it tells you to use purge master logs to delete old logs, and does not mention the expire_logs_days variable as another option. Is there a reason for this omission, or is it safe to use? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bin-log with expire_logs_days
Hi, The system variable expire_logs_days removes the binary logs automatically after the given number of days. The default is 0, which means no automatic removal. Possible removals happen at startup and at binary log rotation. For transactions, it never causes rotation instead it writes to memory cache. The Autocommit statement and HAVE_REPLICATION symbol have impact over expire_logs_days. As of our understanding, for transactions, if log file size as 100MB, and once it get filled, if thre any new log commit, then the log files content will be removed from begining until the required size is obtained and the new log is appended at the end (FIFO). For more information on this variable, http://bugs.mysql.com/bug.php?id=15580 http://bugs.mysql.com/bug.php?id=7236 Thanks ViSolve DB Team. - Original Message - From: George Law [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 19, 2006 12:16 AM Subject: bin-log with expire_logs_days Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in show variables or show status $ echo show variables | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo show status | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: bin-log with expire_logs_days
Hi, For Info about the 'expire-logs-days' bug fix and new release, http://www.developertutorials.com/mysql-manual/manual_News.html Thanks ViSolve DB Team. - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: George Law [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, October 19, 2006 4:00 PM Subject: Re: bin-log with expire_logs_days Hi, The system variable expire_logs_days removes the binary logs automatically after the given number of days. The default is 0, which means no automatic removal. Possible removals happen at startup and at binary log rotation. For transactions, it never causes rotation instead it writes to memory cache. The Autocommit statement and HAVE_REPLICATION symbol have impact over expire_logs_days. As of our understanding, for transactions, if log file size as 100MB, and once it get filled, if thre any new log commit, then the log files content will be removed from begining until the required size is obtained and the new log is appended at the end (FIFO). For more information on this variable, http://bugs.mysql.com/bug.php?id=15580 http://bugs.mysql.com/bug.php?id=7236 Thanks ViSolve DB Team. - Original Message - From: George Law [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 19, 2006 12:16 AM Subject: bin-log with expire_logs_days Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in show variables or show status $ echo show variables | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo show status | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bin-log with expire_logs_days
Thanks Dan. According to the docs, the BEFORE option was introduced in 4.1. I just tried the purge with the to option : PURGE MASTER LOGS TO 'db1-bin.002'; Query OK, 0 rows affected (0.01 sec) so I think I will just purge a couple log files at a time until I can get the disk space down to a more manageable capacity. The previous DBA had told me that the last time he purged the logs, it took it several minutes - but I can only assume he tried to purge too much at once. Thanks again! -- George -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 3:28 PM To: George Law Cc: mysql@lists.mysql.com Subject: Re: bin-log with expire_logs_days I haven't used the server variable you refer to, but instead have always used an external command piped in via cron - PURGE BINARY LOGS BEFORE date and I just use a DATE_SUB function to subtract X days from today's date. http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html It's a pretty quick command to run, generally a fraction of a second. Since you have 132 files it might be a few seconds but I would not expect longer than that. I don't know whether MySQL willl go back and delete the old logs if you set that variable and restart - presumably it would, but not certain. Dan On 10/18/06, George Law [EMAIL PROTECTED] wrote: Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in show variables or show status $ echo show variables | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo show status | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bin-log with expire_logs_days
Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in show variables or show status $ echo show variables | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo show status | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bin-log with expire_logs_days
I haven't used the server variable you refer to, but instead have always used an external command piped in via cron - PURGE BINARY LOGS BEFORE date and I just use a DATE_SUB function to subtract X days from today's date. http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html It's a pretty quick command to run, generally a fraction of a second. Since you have 132 files it might be a few seconds but I would not expect longer than that. I don't know whether MySQL willl go back and delete the old logs if you set that variable and restart - presumably it would, but not certain. Dan On 10/18/06, George Law [EMAIL PROTECTED] wrote: Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in show variables or show status $ echo show variables | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo show status | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
