Re: [Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
On Sonntag 26 Juli 2009 Mantis Bug Tracker wrote: I think you can close this. Query's that were not processed got queue by MySQL and inserted when table gets unlocked. To confirm this, i filled an account with 2GB of email, deleted all them, started dbmail-util, and send a messages in this form: echo blabla|mail m...@domain.com -s test1 echo blabla|mail m...@domain.com -s test2 and so on, and get several that gave the lock timeout error, but after dbmail-util finishes, i have all emails on the destination account. So, no problem at all this! I think the problem was when dbmail-util runs so long (30 minutes) that the lmtp process times out on an insert, because there's a limit on how long the DBMS waits for a select/insert etc. What I did not understand: Is it that the e-mail gets lost when that happens? That would be a bug. If it just times out and gets re-queued in postfix, it will be delivered later anyway. Then the only problem is that the query takes too long. AFAIK it's good optimized, so if it takes too long the DBMS or the hardware need some tuning. Please do not split up a good query that works. mfg zmi -- // Michael Monnerie, Ing.BSc- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: curl -s http://zmi.at/zmi.asc | gpg --import // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4 signature.asc Description: This is a digitally signed message part. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
What I did not understand: Is it that the e-mail gets lost when that happens? That would be a bug. Doesn't get lost, it's resent when the DB is available. I've tested that 3 times and no email got lost. If it just times out and gets re-queued in postfix, it will be delivered later anyway. Then the only problem is that the query takes too long. AFAIK it's good optimized, so if it takes too long the DBMS or the hardware need some tuning. Please do not split up a good query that works. It may require some tuning I believe. If it can be tuned for better performance, good. If not, in the future when I'll have 50GB of data, this will be a nightmare, right now I have 20GB. Hardware can also be upgraded in the future to help on this, but I keep saying that 20GB of data it's not a big DB. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
A NOTE has been added to this issue. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To:netvulture == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: feedback target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 25-Jul-09 13:45 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == -- (0002842) netvulture (developer) - 24-Jul-09 19:01 http://dbmail.org/mantis/view.php?id=792#c2842 -- From the logs, it would appear that you are running a partlists cleanup operation when the message came in. I am not sure if the query was successfully canceled or did finally go through when the partlists table was unlocked. If you use the filelog version of these lines, you should be able to see the phymessage_id it was for, and then do a select * from dbmail_partlists where phymessage_id=x; If you see the part_* that were trying to be inserted, then they were, however with out more of the log I couldn't tell you if the message failed for insertion and returned an error to the MTA or not. -- (0002847) jasb (reporter) - 25-Jul-09 13:45 http://dbmail.org/mantis/view.php?id=792#c2847 -- I'm running at night this: /usr/local/sbin/dbmail-util -d -y /usr/local/sbin/dbmail-util -p -y /usr/local/sbin/dbmail-util -ty anyway i'm going to do that test, to know if it gets delivered or not and let you know. Issue History Date ModifiedUsername FieldChange == 24-Jul-09 10:24 jasb New Issue 24-Jul-09 19:01 netvulture Note Added: 0002842 24-Jul-09 19:22 netvulture Status new = assigned 24-Jul-09 19:22 netvulture Assigned To = netvulture 24-Jul-09 19:38 netvulture Status assigned = feedback 25-Jul-09 13:45 jasb Note Added: 0002847 == ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
A NOTE has been added to this issue. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To:netvulture == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: feedback target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 25-Jul-09 22:15 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == -- (0002842) netvulture (developer) - 24-Jul-09 19:01 http://dbmail.org/mantis/view.php?id=792#c2842 -- From the logs, it would appear that you are running a partlists cleanup operation when the message came in. I am not sure if the query was successfully canceled or did finally go through when the partlists table was unlocked. If you use the filelog version of these lines, you should be able to see the phymessage_id it was for, and then do a select * from dbmail_partlists where phymessage_id=x; If you see the part_* that were trying to be inserted, then they were, however with out more of the log I couldn't tell you if the message failed for insertion and returned an error to the MTA or not. -- (0002847) jasb (reporter) - 25-Jul-09 13:45 http://dbmail.org/mantis/view.php?id=792#c2847 -- I'm running at night this: /usr/local/sbin/dbmail-util -d -y /usr/local/sbin/dbmail-util -p -y /usr/local/sbin/dbmail-util -ty anyway i'm going to do that test, to know if it gets delivered or not and let you know. -- (0002848) jasb (reporter) - 25-Jul-09 22:15 http://dbmail.org/mantis/view.php?id=792#c2848 -- Hi, http://neotrix.decimal.pt/dbmail/dbmail.err.zip Download the filelog, and search for the folowing strings: (3537156, | (3537157, | (3537158, | (3537159, Anyway the messages were received, so the question only remains in: When the table gets looked for 20/30 minutes, theres nothing that can be done, setting a higher value like this on MySQL is out of question, so the best if just to leave it as it is, right? Do not close this as i'm going to perform another test to be sure that messages really get delired. Issue History Date ModifiedUsername FieldChange == 24-Jul-09 10:24 jasb New Issue 24-Jul-09 19:01 netvulture Note Added: 0002842 24-Jul-09 19:22 netvulture Status new = assigned 24-Jul-09 19:22 netvulture Assigned To = netvulture 24-Jul-09 19:38 netvulture Status assigned = feedback 25-Jul-09 13:45 jasb Note Added: 0002847 25-Jul-09 22:15 jasb Note Added: 0002848 == ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
A NOTE has been added to this issue. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To:netvulture == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: feedback target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 26-Jul-09 00:39 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == -- (0002842) netvulture (developer) - 24-Jul-09 19:01 http://dbmail.org/mantis/view.php?id=792#c2842 -- From the logs, it would appear that you are running a partlists cleanup operation when the message came in. I am not sure if the query was successfully canceled or did finally go through when the partlists table was unlocked. If you use the filelog version of these lines, you should be able to see the phymessage_id it was for, and then do a select * from dbmail_partlists where phymessage_id=x; If you see the part_* that were trying to be inserted, then they were, however with out more of the log I couldn't tell you if the message failed for insertion and returned an error to the MTA or not. -- (0002847) jasb (reporter) - 25-Jul-09 13:45 http://dbmail.org/mantis/view.php?id=792#c2847 -- I'm running at night this: /usr/local/sbin/dbmail-util -d -y /usr/local/sbin/dbmail-util -p -y /usr/local/sbin/dbmail-util -ty anyway i'm going to do that test, to know if it gets delivered or not and let you know. -- (0002848) jasb (reporter) - 25-Jul-09 22:15 http://dbmail.org/mantis/view.php?id=792#c2848 -- Hi, http://neotrix.decimal.pt/dbmail/dbmail.err.zip Download the filelog, and search for the folowing strings: (3537156, | (3537157, | (3537158, | (3537159, Anyway the messages were received, so the question only remains in: When the table gets looked for 20/30 minutes, theres nothing that can be done, setting a higher value like this on MySQL is out of question, so the best if just to leave it as it is, right? Do not close this as i'm going to perform another test to be sure that messages really get delired. -- (0002849) netvulture (developer) - 26-Jul-09 00:39 http://dbmail.org/mantis/view.php?id=792#c2849 -- It would appear that lmtpd is trying to insert the message, but timing out on the insertion of the partlists. So it is successfully inserting the unique mimeparts, and inserting a new physmessage for the message, but can't add the pointers to the mimeparts for the phymessage and just fails message insertion until the pointers can be inserted. My guess is there is a write lock on dbmail_partlists being caused by a delete on partlists to clean up left over part pointers from deleted messages. We can probably rework dbmail-util to do a delete of the specific rows instead of the combined delete from table where id not in (select ...) If you do a mysqladmin process at the time of those messages, you will probably see the above mentioned query in place and the other inserts as write locked. Issue History Date ModifiedUsername FieldChange == 24-Jul-09 10:24 jasb New Issue 24-Jul-09 19:01 netvulture Note Added: 0002842 24-Jul-09 19:22 netvulture Status new = assigned 24-Jul-09 19:22 netvulture Assigned To = netvulture 24-Jul-09 19:38 netvulture Status assigned = feedback 25-Jul-09
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
A NOTE has been added to this issue. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To:netvulture == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: feedback target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 26-Jul-09 00:57 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == -- (0002842) netvulture (developer) - 24-Jul-09 19:01 http://dbmail.org/mantis/view.php?id=792#c2842 -- From the logs, it would appear that you are running a partlists cleanup operation when the message came in. I am not sure if the query was successfully canceled or did finally go through when the partlists table was unlocked. If you use the filelog version of these lines, you should be able to see the phymessage_id it was for, and then do a select * from dbmail_partlists where phymessage_id=x; If you see the part_* that were trying to be inserted, then they were, however with out more of the log I couldn't tell you if the message failed for insertion and returned an error to the MTA or not. -- (0002847) jasb (reporter) - 25-Jul-09 13:45 http://dbmail.org/mantis/view.php?id=792#c2847 -- I'm running at night this: /usr/local/sbin/dbmail-util -d -y /usr/local/sbin/dbmail-util -p -y /usr/local/sbin/dbmail-util -ty anyway i'm going to do that test, to know if it gets delivered or not and let you know. -- (0002848) jasb (reporter) - 25-Jul-09 22:15 http://dbmail.org/mantis/view.php?id=792#c2848 -- Hi, http://neotrix.decimal.pt/dbmail/dbmail.err.zip Download the filelog, and search for the folowing strings: (3537156, | (3537157, | (3537158, | (3537159, Anyway the messages were received, so the question only remains in: When the table gets looked for 20/30 minutes, theres nothing that can be done, setting a higher value like this on MySQL is out of question, so the best if just to leave it as it is, right? Do not close this as i'm going to perform another test to be sure that messages really get delired. -- (0002849) netvulture (developer) - 26-Jul-09 00:39 http://dbmail.org/mantis/view.php?id=792#c2849 -- It would appear that lmtpd is trying to insert the message, but timing out on the insertion of the partlists. So it is successfully inserting the unique mimeparts, and inserting a new physmessage for the message, but can't add the pointers to the mimeparts for the phymessage and just fails message insertion until the pointers can be inserted. My guess is there is a write lock on dbmail_partlists being caused by a delete on partlists to clean up left over part pointers from deleted messages. We can probably rework dbmail-util to do a delete of the specific rows instead of the combined delete from table where id not in (select ...) If you do a mysqladmin process at the time of those messages, you will probably see the above mentioned query in place and the other inserts as write locked. -- (0002850) jasb (reporter) - 26-Jul-09 00:57 http://dbmail.org/mantis/view.php?id=792#c2850 -- I think you can close this. Query's that were not processed got queue by MySQL and inserted when table gets unlocked. To confirm this, i filled an account with 2GB of email, deleted all them, started dbmail-util, and send a messages in this form: echo blabla|mail m...@domain.com -s test1 echo blabla|mail m...@domain.com
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
The following issue has been SUBMITTED. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To: == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: new target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 24-Jul-09 10:24 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == Issue History Date ModifiedUsername FieldChange == 24-Jul-09 10:24 jasb New Issue == ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
A NOTE has been added to this issue. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To: == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: new target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 24-Jul-09 19:01 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == -- (0002842) netvulture (developer) - 24-Jul-09 19:01 http://dbmail.org/mantis/view.php?id=792#c2842 -- From the logs, it would appear that you are running a partlists cleanup operation when the message came in. I am not sure if the query was successfully canceled or did finally go through when the partlists table was unlocked. If you use the filelog version of these lines, you should be able to see the phymessage_id it was for, and then do a select * from dbmail_partlists where phymessage_id=x; If you see the part_* that were trying to be inserted, then they were, however with out more of the log I couldn't tell you if the message failed for insertion and returned an error to the MTA or not. Issue History Date ModifiedUsername FieldChange == 24-Jul-09 10:24 jasb New Issue 24-Jul-09 19:01 netvulture Note Added: 0002842 == ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
The following issue has been ASSIGNED. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To:netvulture == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: assigned target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 24-Jul-09 19:22 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == -- (0002842) netvulture (developer) - 24-Jul-09 19:01 http://dbmail.org/mantis/view.php?id=792#c2842 -- From the logs, it would appear that you are running a partlists cleanup operation when the message came in. I am not sure if the query was successfully canceled or did finally go through when the partlists table was unlocked. If you use the filelog version of these lines, you should be able to see the phymessage_id it was for, and then do a select * from dbmail_partlists where phymessage_id=x; If you see the part_* that were trying to be inserted, then they were, however with out more of the log I couldn't tell you if the message failed for insertion and returned an error to the MTA or not. Issue History Date ModifiedUsername FieldChange == 24-Jul-09 10:24 jasb New Issue 24-Jul-09 19:01 netvulture Note Added: 0002842 24-Jul-09 19:22 netvulture Status new = assigned 24-Jul-09 19:22 netvulture Assigned To = netvulture == ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
[Dbmail-dev] [DBMail 0000792]: Lock timeout on lmtpd message insertation during nightly dbmail-util
The following issue requires your FEEDBACK. == http://dbmail.org/mantis/view.php?id=792 == Reported By:jasb Assigned To:netvulture == Project:DBMail Issue ID: 792 Category: LMTP daemon Reproducibility:have not tried Severity: major Priority: normal Status: feedback target: == Date Submitted: 24-Jul-09 10:24 CEST Last Modified: 24-Jul-09 19:38 CEST == Summary:Lock timeout on lmtpd message insertation during nightly dbmail-util Description: Hi, During the nightly dbmail-util, when it is running, new messages wont get in. My question is: i believe they don't really get inserted, i ask, are the transaction really being restarted/were this messages really delivered? Log of lmtpd below. == -- (0002842) netvulture (developer) - 24-Jul-09 19:01 http://dbmail.org/mantis/view.php?id=792#c2842 -- From the logs, it would appear that you are running a partlists cleanup operation when the message came in. I am not sure if the query was successfully canceled or did finally go through when the partlists table was unlocked. If you use the filelog version of these lines, you should be able to see the phymessage_id it was for, and then do a select * from dbmail_partlists where phymessage_id=x; If you see the part_* that were trying to be inserted, then they were, however with out more of the log I couldn't tell you if the message failed for insertion and returned an error to the MTA or not. Issue History Date ModifiedUsername FieldChange == 24-Jul-09 10:24 jasb New Issue 24-Jul-09 19:01 netvulture Note Added: 0002842 24-Jul-09 19:22 netvulture Status new = assigned 24-Jul-09 19:22 netvulture Assigned To = netvulture 24-Jul-09 19:38 netvulture Status assigned = feedback == ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev