Re: [policyd-users] Redundant MySQL connection
Olivier Smedts wrote: > > I now think that policyd doesn't need even a little modification to make > it work perfectly with MySQL replication. It seems that there is no need > for "IF EXIST" or "IF NOT EXIST" because UPDATE and DELETE can be made > on non-existent keys, and INSERT DELAYED doesn't return an error in case > of duplicate key. `INSERT DELAYED` will never return any errors anyways. Cami - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
Nate a écrit : > At 12:00 PM 10/9/2007, Olivier Smedts wrote: > >> I agree to the idee that replication should not happen in policyd, but >> in MySQL, cause MySQL already supports replication and redundancy. And >> it works nearly like a charm. But here is the problem : >> policyd doesn't use an AUTO_INCREMENT id in its primary keys (in >> "triplet" table mostly), so if two mail servers receive the same mail in >> the same time (between the time at which policyd checks in the entry >> already exists and the time at which it inserts a new one), the first >> server will insert the new entry, and the second server won't be able to >> insert it (primary key constraint violation) so the replication will stop. >> >> > >> > I personnaly like this since I have already replicated system like >> > that with severals slaves... and a loadbalancer have lb read slaves. >> >> I set up 3 mail servers (MX) with policyd and MySQL multi-master chain >> replication (each node is slave of the previous one and master of the >> next one). >> I think a solution would be to insert "IF EXIST" in DELETE and UPDATE >> queries, and "IF NOT EXIST" in INSERT queries of policyd (greylist.c >> source file), so that the same query could be executed two times without >> stopping replication. What do you think of this solution ? Could it be >> done upstream, to make policyd a perfect greylisting daemon for mail >> server clusters ? >> > > We have a similar setup and it is pretty rare that this type of > duplicate key item would occur. A easy fix within mysql is to just > tell mysql to ignore duplicate key errors on replication. It'll > create an extremely slight data inconsistency; however, it will not > affect anything from my experience. Just add the following to my.cnf > [mysqld] section. > > slave-skip-errors=126,1062 > I appreciate your feedback, I haven't checked slave-skip-errors. But what is error 126 ? I don't see it in http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html In this setup with three nodes, do you know what happens in the case that the two first servers receive the same mail at the same time ? The first that receives the mail inserts a new entry in the "triplet" table. The second one will try to insert it to but will fail cause it already exists. It won't stop replication thanks to slave-skip-errors=1062. But will the second server log the insert request in its master log, so that the entry can be replicated on the third server ? Or will it "skip" the request and won't log it because of skip-slave-errors ? I checked policyd source again and made little tests in the MySQL database, with no slave-skip-errors : - when trying to delete a non-existent triplet : DELETE QUICK from triplet WHERE _host='999.999.999'; Query OK, 0 rows affected (0.00 sec) => no errors - when updating a non-existent triplet : UPDATE triplet SET _datelast='1191502133',_count=_count+1 WHERE _host='999.999.999' AND _from='[EMAIL PROTECTED]' AND _rcpt='[EMAIL PROTECTED]'; Query OK, 0 rows affected (0.00 sec) => no errors too, only "0 rows affected" - when trying to insert an already-existing triplet : INSERT DELAYED INTO triplet (_datenew,_datelast,_host,_from,_rcpt) VALUES (1190984813,1191502135,'127.0.0','[EMAIL PROTECTED]','[EMAIL PROTECTED]'); Query OK, 1 row affected (0.00 sec) INSERT DELAYED INTO triplet (_datenew,_datelast,_host,_from,_rcpt) VALUES (1190984813,1191502135,'127.0.0','[EMAIL PROTECTED]','[EMAIL PROTECTED]'); Query OK, 1 row affected (0.00 sec) => the DELAYED keyword make the request work. If you try without it, it will return "ERROR 1062 (23000): Duplicate entry '[EMAIL PROTECTED]@domain.com' for key 1". With INSERT DELAYED, there's no error. The fields aren't updated though. I now think that policyd doesn't need even a little modification to make it work perfectly with MySQL replication. It seems that there is no need for "IF EXIST" or "IF NOT EXIST" because UPDATE and DELETE can be made on non-existent keys, and INSERT DELAYED doesn't return an error in case of duplicate key. -- Olivier > We use this reliably with policyd as well as dozens of other mysql > apps with zero impact on data integrity. > > - Nate > > > > > - > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > ___ > policyd-users mailing list > policyd-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/policyd-users > > - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. D
Re: [policyd-users] Redundant MySQL connection
At 12:00 PM 10/9/2007, Olivier Smedts wrote: >I agree to the idee that replication should not happen in policyd, but >in MySQL, cause MySQL already supports replication and redundancy. And >it works nearly like a charm. But here is the problem : >policyd doesn't use an AUTO_INCREMENT id in its primary keys (in >"triplet" table mostly), so if two mail servers receive the same mail in >the same time (between the time at which policyd checks in the entry >already exists and the time at which it inserts a new one), the first >server will insert the new entry, and the second server won't be able to >insert it (primary key constraint violation) so the replication will stop. > > > > > I personnaly like this since I have already replicated system like > > that with severals slaves... and a loadbalancer have lb read slaves. > >I set up 3 mail servers (MX) with policyd and MySQL multi-master chain >replication (each node is slave of the previous one and master of the >next one). >I think a solution would be to insert "IF EXIST" in DELETE and UPDATE >queries, and "IF NOT EXIST" in INSERT queries of policyd (greylist.c >source file), so that the same query could be executed two times without >stopping replication. What do you think of this solution ? Could it be >done upstream, to make policyd a perfect greylisting daemon for mail >server clusters ? We have a similar setup and it is pretty rare that this type of duplicate key item would occur. A easy fix within mysql is to just tell mysql to ignore duplicate key errors on replication. It'll create an extremely slight data inconsistency; however, it will not affect anything from my experience. Just add the following to my.cnf [mysqld] section. slave-skip-errors=126,1062 We use this reliably with policyd as well as dozens of other mysql apps with zero impact on data integrity. - Nate - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
From: Xavier Beaudouin <[EMAIL PROTECTED]> - 2007-08-06 14:31 > > The approach that should be taken is: > > > > 1) Set up master -> slave replication > > 2) Change policyd so that all READS happen from the slave(s) > > and WRITES only go to the master. I agree to the idee that replication should not happen in policyd, but in MySQL, cause MySQL already supports replication and redundancy. And it works nearly like a charm. But here is the problem : policyd doesn't use an AUTO_INCREMENT id in its primary keys (in "triplet" table mostly), so if two mail servers receive the same mail in the same time (between the time at which policyd checks in the entry already exists and the time at which it inserts a new one), the first server will insert the new entry, and the second server won't be able to insert it (primary key constraint violation) so the replication will stop. > > I personnaly like this since I have already replicated system like > that with severals slaves... and a loadbalancer have lb read slaves. I set up 3 mail servers (MX) with policyd and MySQL multi-master chain replication (each node is slave of the previous one and master of the next one). I think a solution would be to insert "IF EXIST" in DELETE and UPDATE queries, and "IF NOT EXIST" in INSERT queries of policyd (greylist.c source file), so that the same query could be executed two times without stopping replication. What do you think of this solution ? Could it be done upstream, to make policyd a perfect greylisting daemon for mail server clusters ? I can provide technical details and documentation on my setup if necessary. Sincerely, Olivier > > > Such a solution not only makes your environment cleaner and > > problems easier to diagnose, but it makes Policyd more robust. > > :) Good I think :) > > /Xavier > > -- > Xavier Beaudouin - http://oav.net/ -- Olivier Smedts Cemagref de Lyon - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
> The approach that should be taken is: > > 1) Set up master -> slave replication > 2) Change policyd so that all READS happen from the slave(s) >and WRITES only go to the master. I personnaly like this since I have already replicated system like that with severals slaves... and a loadbalancer have lb read slaves. > Such a solution not only makes your environment cleaner and > problems easier to diagnose, but it makes Policyd more robust. :) Good I think :) /Xavier -- Xavier Beaudouin - http://oav.net/ - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
Stanislav Sinyagin wrote: > --- Cami Sardinha <[EMAIL PROTECTED]> wrote: >> And what happens when someone requests a 3rd, 4th and 5th MySQL >> backup option? > > nobody would need that :) > The approach that I suggested would work perfectly for 1+1 redundancy. > If someone wants a bigger redundancy solution, it would anyway require some > customization of the code (if not a complete redesign). > > I understand that Mysql dual-master setup is not a rocket science - I'll > most probably go that way myself. But the way I suggested would minimize the > administrative burden and synchronization efforts. In theory, that could > even lead to a self-healing redundant solution... but then it really needs > a redesign and refactoring, so that the storage level is separated from > the operation logic. It is not Policyd's job to perform such work. Dual writes increases latency for each Postfix -> Policyd transaction, introduces another single point of failure and makes Policyd less robust (especially when troubleshooting weird problems). The approach that should be taken is: 1) Set up master -> slave replication 2) Change policyd so that all READS happen from the slave(s) and WRITES only go to the master. Such a solution not only makes your environment cleaner and problems easier to diagnose, but it makes Policyd more robust. Cami - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
--- Cami Sardinha <[EMAIL PROTECTED]> wrote: > And what happens when someone requests a 3rd, 4th and 5th MySQL > backup option? nobody would need that :) The approach that I suggested would work perfectly for 1+1 redundancy. If someone wants a bigger redundancy solution, it would anyway require some customization of the code (if not a complete redesign). I understand that Mysql dual-master setup is not a rocket science - I'll most probably go that way myself. But the way I suggested would minimize the administrative burden and synchronization efforts. In theory, that could even lead to a self-healing redundant solution... but then it really needs a redesign and refactoring, so that the storage level is separated from the operation logic. - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
> --- Stanislav Sinyagin <[EMAIL PROTECTED]> wrote: > >> It would be great to implement a new option in policyd. >> Technically it should not be difficult. >> >> The new option would list a backup MySQL server/dbname/user/password, >> and if it's specified, every INSERT statement is duplicated on that >> server. >> >> This would allow an elegant redundant solution: two policy gateways >> would update each other's SQL databases, without the need of complex >> database synchronization. Well, IMO MySQL DB replication is not too complex to setup. We have it running in a Dual Master setup (both databases are in use by 2 MTA's) so both MTA's share the same information: if either MX goes down, the other still works with all information. I think you just have to plan your replication carefully so that you don't get duplicate id's. But then, Policd doesn't use id's in it's tables so in this case it is a non-issue... Grts, Rob - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
On Mon, Aug 06, 2007 at 12:13:50PM +0200, Cami Sardinha wrote: > And what happens when someone requests a 3rd, 4th and 5th MySQL backup > option? I agree that duplication INSERT and UPDATE requests to multiple databases is not policyd's job, this should be implemented in a MySQL replication system. An easier solution that allows for greylisting while the master database is down, is policyd simply switching to a (unpopulated) fall-back database server, i.e. it would start again from scratch. For greylisting this would mean some additional delays, but at least mail is still being greylisted/ throttled/... while the master db is down. Geert - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
Stanislav Sinyagin wrote: > It would be great to implement a new option in policyd. > Technically it should not be difficult. > > The new option would list a backup MySQL server/dbname/user/password, > and if it's specified, every INSERT statement is duplicated on that server. > > This would allow an elegant redundant solution: two policy gateways > would update each other's SQL databases, without the need of complex database > synchronization. And what happens when someone requests a 3rd, 4th and 5th MySQL backup option? Cami - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
Re: [policyd-users] Redundant MySQL connection
every INSERT and UPDATE, of course :) --- Stanislav Sinyagin <[EMAIL PROTECTED]> wrote: > It would be great to implement a new option in policyd. > Technically it should not be difficult. > > The new option would list a backup MySQL server/dbname/user/password, > and if it's specified, every INSERT statement is duplicated on that server. > > This would allow an elegant redundant solution: two policy gateways > would update each other's SQL databases, without the need of complex database > > synchronization. > > stan > > > - > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > ___ > policyd-users mailing list > policyd-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/policyd-users > - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
