Advice on tuning the startup of a MySQL server on Debian

Hello all,

This is not strictly a DBMail question, but it is close.
Lately I 
noticed that any time the SQL server needs to be restarted
for some 
reason, DBMail starts having problems with inserting
messages. Since the 
error message was changed to 450 from 503, this is not a big
issue, as 
mail is not lost. Nevertheless I want to know if someone
else had such 
problems and what steps if any were taken to solve them.

The situation is as follows. Under Debian the mysql init
script runs the 
following upon the start of the daemon:

/usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf 
--all-databases --fast --silent

This process is pretty resource intensive and causes the
following to 
happen in DBMail from time to time:

Jul 20 09:29:23 Arzamas dbmail-lmtpd[2954]: Error:[sql] 
dbmysql.c,db_query(+293): [Lock wait timeout exceeded; try
restarting 
transaction] [INSERT INTO dbmail_messageblks (is_header, 
messageblk,blocksize, physmessage_id) VALUES (_omitted_)]

My question is - should I try to fix this at all? Is the
mysqlcheck 
process useful at all or should I disable it on startup? Or
can I tune 
some timeouts within dbmail?

Any input would be appreciated.

Thanks

Peter
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Peter Rabbitson wrote:
> Hello all,
> 
> This is not strictly a DBMail question, but it is
close. Lately I 
> noticed that any time the SQL server needs to be
restarted for some 
> reason, DBMail starts having problems with inserting
messages. Since the 
> error message was changed to 450 from 503, this is not
a big issue, as 
> mail is not lost. Nevertheless I want to know if
someone else had such 
> problems and what steps if any were taken to solve
them.
> 
> The situation is as follows. Under Debian the mysql
init script runs the 
> following upon the start of the daemon:
> 
> /usr/bin/mysqlcheck
--defaults-file=/etc/mysql/debian.cnf 
> --all-databases --fast --silent
> 
> This process is pretty resource intensive and causes
the following to 
> happen in DBMail from time to time:
> 
> Jul 20 09:29:23 Arzamas dbmail-lmtpd[2954]: Error:[sql]

> dbmysql.c,db_query(+293): [Lock wait timeout exceeded;
try restarting 
> transaction] [INSERT INTO dbmail_messageblks
(is_header, 
> messageblk,blocksize, physmessage_id) VALUES
(_omitted_)]

I got this every time I restarted the mysql server on a
dbmail host.

You can tune the timeout involved
/etc/mysql/my.cnf: innodb_lock_wait_timeout

But my choice would be to disable the check run.

> My question is - should I try to fix this at all? Is
the mysqlcheck 
> process useful at all or should I disable it on
startup? Or can I tune 
> some timeouts within dbmail?


> 
> Any input would be appreciated.
> 
> Thanks
> 
> Peter
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
> 


-- 
  
____________________________________________________________
____
   Paul Stevens                                      paul at
nfg.nl
   NET FACILITIES GROUP                     GPG/PGP:
1024D/11F8CD31
   The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Paul J Stevens wrote:
> Peter Rabbitson wrote:
>> Hello all,
>>
>> This is not strictly a DBMail question, but it is
close. Lately I 
>> noticed that any time the SQL server needs to be
restarted for some 
>> reason, DBMail starts having problems with
inserting messages. Since 
>> the error message was changed to 450 from 503, this
is not a big 
>> issue, as mail is not lost. Nevertheless I want to
know if someone 
>> else had such problems and what steps if any were
taken to solve them.
>>
>> The situation is as follows. Under Debian the mysql
init script runs 
>> the following upon the start of the daemon:
>>
>> /usr/bin/mysqlcheck
--defaults-file=/etc/mysql/debian.cnf 
>> --all-databases --fast --silent
>>
>> This process is pretty resource intensive and
causes the following to 
>> happen in DBMail from time to time:
>>
>> Jul 20 09:29:23 Arzamas dbmail-lmtpd[2954]:
Error:[sql] 
>> dbmysql.c,db_query(+293): [Lock wait timeout
exceeded; try restarting 
>> transaction] [INSERT INTO dbmail_messageblks
(is_header, 
>> messageblk,blocksize, physmessage_id) VALUES
(_omitted_)]
> 
> I got this every time I restarted the mysql server on a
dbmail host.
> 
> You can tune the timeout involved
> /etc/mysql/my.cnf: innodb_lock_wait_timeout
> 
> But my choice would be to disable the check run.
> 

Considering your experience with MySQL servers, were there
any 
situations in which the check was actually worth it? Or is
it largely 
useless with the current state of the InnoDB engine?

Peter
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Peter Rabbitson wrote:

> Considering your experience with MySQL servers, were
there any 
> situations in which the check was actually worth it? Or
is it largely 
> useless with the current state of the InnoDB engine?

Powerfailure. If you have those, run the check manually. But
then, under those 
circumstatnce you may need to run a special recovery
procedure (some config 
option I don't remember) if the data file is really broken.
Happened to me once.

But don't take my word for it. I'm not a innodb specialist.


-- 
  
____________________________________________________________
____
   Paul Stevens                                      paul at
nfg.nl
   NET FACILITIES GROUP                     GPG/PGP:
1024D/11F8CD31
   The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

> This is not strictly a DBMail question, but it is
close. Lately I 
> noticed that any time the SQL server needs to be
restarted for some 
> reason, DBMail starts having problems with inserting
messages. Since the 
> error message was changed to 450 from 503, this is not
a big issue, as 
> mail is not lost. Nevertheless I want to know if
someone else had such 
> problems and what steps if any were taken to solve
them.

What about having an option to fallback to a
temp-deliver-to-filesystem 
mode when the DB is temporarily unavailable (and then move
messages from 
the filesystem to the DB when DB connectivity is restored)?

You could then have an option that if the DB is unavailable
for longer 
than x minutes, *then* start temp failing delivery.

Just a thought...

-- 

Best regards,

Charles
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

On Wed, Jul 25, 2007, Charles Marcus <CMarcusMedia-Brokers.com> said:

>> This is not strictly a DBMail question, but it is
close. Lately I 
>> noticed that any time the SQL server needs to be
restarted for some 
>> reason, DBMail starts having problems with
inserting messages. Since the 
>> error message was changed to 450 from 503, this is
not a big issue, as 
>> mail is not lost. Nevertheless I want to know if
someone else had such 
>> problems and what steps if any were taken to solve
them.
> 
> What about having an option to fallback to a
temp-deliver-to-filesystem 
> mode when the DB is temporarily unavailable (and then
move messages from 
> the filesystem to the DB when DB connectivity is
restored)?
> 
> You could then have an option that if the DB is
unavailable for longer 
> than x minutes, *then* start temp failing delivery.

DBMail never accepts responsibility for a message that
cannot be
immediately delivered. To do so would make it into a full
MTA -- the key
function of an MTA is to say to another MTA, "Yes, I'll
take this message,
and I'll agree to hold it for as long as I need to,
periodically retrying
delivery, and I'll be responsible for sending a bounce in
four-five days
if the delivery continues to be delayed that long." By
the time you've
written enough code to handle all the corner cases in there,
you've got
another Sendmail, Postfix, Exim, QMail on your hands.

Aaron
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Charles Marcus wrote:
>> This is not strictly a DBMail question, but it is
close. Lately I 
>> noticed that any time the SQL server needs to be
restarted for some 
>> reason, DBMail starts having problems with
inserting messages. Since 
>> the error message was changed to 450 from 503, this
is not a big 
>> issue, as mail is not lost. Nevertheless I want to
know if someone 
>> else had such problems and what steps if any were
taken to solve them.
> 
> What about having an option to fallback to a
temp-deliver-to-filesystem 
> mode when the DB is temporarily unavailable (and then
move messages from 
> the filesystem to the DB when DB connectivity is
restored)?
> 
> You could then have an option that if the DB is
unavailable for longer 
> than x minutes, *then* start temp failing delivery.
> 
> Just a thought...
> 

Err... Isn't this exactly what the MTA in front of dbmail is
for?
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

On 25 Jul 2007, at 18:48, Aaron Stone wrote:

> DBMail never accepts responsibility for a message that
cannot be
> immediately delivered. To do so would make it into a
full MTA --  
> the key
> function of an MTA is to say to another MTA, "Yes,
I'll take this  
> message,
> and I'll agree to hold it for as long as I need to,
periodically  
> retrying
> delivery, and I'll be responsible for sending a bounce
in four-five  
> days
> if the delivery continues to be delayed that
long." By the time you've
> written enough code to handle all the corner cases in
there, you've  
> got
> another Sendmail, Postfix, Exim, QMail on your hands.

At that point the name would probably have to be changed to
Sendfix,  
Postmail or possibly Quickfiix.


_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

> Err... Isn't this exactly what the MTA in front of
dbmail is for?

Never mind... thats what I get for commenting on something I
haven't 
actually installed/run yet...

I've just been lurking because the idea of an SQL based mail
server has 
always intrigued me, and I forgot that DBMail isn't an smtp
server...

Sorry for the noise...

-- 

Best regards,

Charles
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail