>
> Long ago in MagicMail we recognized that there was a problem with
> Postgres direct back addressing a large number of AUTH attempts at once,
> and we went down the road to using a high performace DBFILE mechanisms,
> with real time updates from the Postgres database.. allowed us to scale
> greatly..
Thank you, this looks interesting but I don't really understand how to
organize it correctly. Do you have a link to some manual that could serve
as an example?
On Mon, Feb 3, 2025 at 8:39 PM Michael Peddemors <mich...@linuxmagic.com>
wrote:
> Long ago in MagicMail we recognized that there was a problem with
> Postgres direct back addressing a large number of AUTH attempts at once,
> and we went down the road to using a high performace DBFILE mechanisms,
> with real time updates from the Postgres database.. allowed us to scale
> greatly..
>
> Don't know much about you, your product or use case, just thought it
> might help..
>
> On 2025-02-02 21:05, Anatoliy Zhestov via dovecot wrote:
> > Hi. We have a performance problem with imap authentication through
> > postgresql.
> > Our servers(modoboa based) have a big amount of permanent imap
> > connections(5000-50000).
> > Current performance is about 3000 successful authentications per hour. No
> > visible reasons for such low speed. Accordingly, after a network failure
> or
> > server restart, all clients try to reconnect, but restoring the
> connection
> > pool takes hours and even tens of hours. Judging by the logs after the
> > restart, a huge number of auth requests closed by timeout after 70-90
> > seconds. The postgresql database is not overloaded at the restore
> > connections process and the postgresql connection pool (100) does not
> > overflow. Manually started sql auth queries work fast, tables have
> indexes.
> > So I guess there is a bottleneck somewhere in dovecot auth service or
> > postgresql driver.
> >
> > I couldn't find any settings in the documentation that are directly
> > responsible for regulating the number of connections to PostgreSQL from
> the
> > auth service or performance of the driver. Is there any way to manage
> this?
> > Does it make sense to use pgbouncer in front of the base? What else can
> be
> > a bottleneck in our configuration and how to regulate the number of
> > possible simultaneous authentications via PostgreSQL? I would be happy
> for
> > any advice on how to increase the performance to at least 100
> > authentications per second.
> >
> >
> > Thanks in advance for your help.
> > Anatoliy Zhestov.
> >
> > ---------------- configs of server with 48 core, 184 gb mem
> ---------------
> >
> > dovecot 2.3.16
> > # 2.3.16 (7e2e900c1a): /etc/dovecot/dovecot.conf
> > # Pigeonhole version 0.5.16 (09c29328)
> > # OS: Linux 5.15.0-126-generic x86_64 Ubuntu 22.04.5 LTS
> > # Hostname: imap.ourcompany.net
> > auth_cache_negative_ttl = 0
> > auth_cache_size = 20 M
> > auth_cache_ttl = 3 hours
> > auth_master_user_separator = *
> > auth_mechanisms = plain login
> > default_client_limit = 131072
> > default_process_limit = 131072
> > dict {
> > quota = pgsql:/etc/dovecot/dovecot-dict-sql.conf.ext
> > }
> > mail_location = maildir:~/Maildir
> > mail_plugins = quota
> > managesieve_notify_capability = mailto
> > managesieve_sieve_capability = fileinto reject envelope encoded-character
> > vacation subaddress comparator-i;ascii-numeric relational regex
> imap4flags
> > copy include variables body enotify environment mai
> > lbox date index ihave duplicate mime foreverypart extracttext editheader
> > imapflags notify vnd.dovecot.pipe vnd.dovecot.execute
> > namespace inbox {
> > inbox = yes
> > location =
> > mailbox Drafts {
> > auto = subscribe
> > special_use = \Drafts
> > }
> > mailbox Junk {
> > auto = subscribe
> > special_use = \Junk
> > }
> > mailbox Sent {
> > auto = subscribe
> > special_use = \Sent
> > }
> > mailbox Trash {
> > auto = subscribe
> > special_use = \Trash
> > }
> > prefix =
> > }
> > passdb {
> > args = /etc/dovecot/dovecot-sql.conf.ext
> > driver = sql
> > }
> > passdb {
> > args = /etc/dovecot/dovecot-sql-master.conf.ext
> > driver = sql
> > master = yes
> > pass = yes
> > }
> > plugin {
> > quota = dict:User quota::proxy::quota
> > sieve = ~/.dovecot.sieve
> > sieve_dir = ~/sieve
> > sieve_execute_bin_dir = /usr/lib/dovecot/sieve-execute
> > sieve_execute_socket_dir = .
> > sieve_extensions = +notify +imapflags +editheader +body
> > +vnd.dovecot.execute +vnd.dovecot.pipe
> > sieve_pipe_bin_dir = /usr/lib/dovecot/sieve-execute
> > sieve_pipe_socket_dir = .
> > sieve_plugins = sieve_extprograms
> > }
> > protocols = " imap lmtp sieve"
> > service anvil {
> > client_limit = 393219
> > }
> > service auth {
> > client_limit = 524288
> > unix_listener /var/spool/postfix/private/auth {
> > group = postfix
> > mode = 0666
> > user = postfix
> > }
> > unix_listener auth-radicale {
> > group = radicale
> > mode = 0666
> > user = radicale
> > }
> > unix_listener auth-userdb {
> > user = vmail
> > }
> > }
> > service config {
> > vsz_limit = 1 G
> > }
> > service dict {
> > unix_listener dict {
> > mode = 0600
> > user = vmail
> > }
> > }
> > service imap {
> > executable = imap postlogin
> > process_limit = 131072
> > vsz_limit = 1 G
> > }
> > service lmtp {
> > unix_listener /var/spool/postfix/private/dovecot-lmtp {
> > group = postfix
> > mode = 0600
> > user = postfix
> > }
> > vsz_limit = 512 M
> > }
> > service pop3 {
> > executable = pop3 postlogin
> > }
> > service postlogin {
> > executable = script-login /usr/local/bin/postlogin.sh
> > service_count = 1000
> > process_min_avail = 5
> > user = modoboa
> > }
> >
> > service send-to-click-frontapp1 {
> > executable = script
> > /usr/lib/dovecot/sieve-execute/clickhouse_log_frontapp1.sh
> > unix_listener send-to-click-frontapp1 {
> > group = vmail
> > mode = 0666
> > user = vmail
> > }
> > user = dovenull
> > vsz_limit = 2 G
> > }
> > service send-to-click {
> > executable = script /usr/lib/dovecot/sieve-execute/clickhouse_log.sh
> > unix_listener send-to-click {
> > group = vmail
> > mode = 0666
> > user = vmail
> > }
> > user = dovenull
> > vsz_limit = 2 G
> > }
> > service stats {
> > unix_listener stats-reader {
> > group = vmail
> > mode = 0660
> > user = vmail
> > }
> > unix_listener stats-writer {
> > group = vmail
> > mode = 0660
> > user = vmail
> > }
> > vsz_limit = 2 G
> > }
> > ssl_cert = </etc/letsencrypt/live/imap.ourcompany.net/fullchain.pem
> > ssl_cipher_list =
> >
> EECDH+ECDSA+AESGCM:EECDH+aRSA+AESGCM:EECDH+ECDSA+SHA384:EECDH+ECDSA+SHA256:EECDH+aRSA+SHA384:EECDH+aRSA+SHA256:EECDH+aRSA+RC4:EECDH:EDH+aRSA:!aNULL:!eNULL:!LOW:!3DES:!MD5:!EXP:!PSK:!SRP:!DSS:!RC4
> > ssl_client_ca_dir = /etc/ssl/certs
> > ssl_dh = # hidden, use -P to show it
> > ssl_key = # hidden, use -P to show it
> > ssl_min_protocol = TLSv1
> > stats_writer_socket_path =
> > userdb {
> > driver = prefetch
> > }
> > userdb {
> > args = /etc/dovecot/dovecot-sql.conf.ext
> > driver = sql
> > }
> > protocol lmtp {
> > mail_plugins = quota sieve
> > postmaster_address = postmas...@imap.ourcompany.net
> > }
> > protocol imap {
> > mail_max_userip_connections = 50
> > mail_plugins = quota imap_quota
> > }
> >
> >
> >
> >
> > /etc/dovecot/dovecot-sql-master.conf.ex
> > driver = pgsql
> > connect = host=127.0.0.1 port=5432 dbname=modoboa user=modoboa
> > password=XXXXXXXXXXXXXXXXX
> > password_query = SELECT email AS user, password FROM core_user WHERE
> > email='%u' and is_active and master_user
> >
> >
> > /etc/dovecot/dovecot-sql.conf.ext
> > driver = pgsql
> > connect = host=127.0.0.1 port=5432 dbname=modoboa user=modoboa
> > password=XXXXXXXXXXXXXXXXX
> > user_query = SELECT '/srv/vmail/%d/%n' AS home, 1003 as uid, 1003 as gid,
> > '*:bytes=' || mb.quota || 'M' AS quota_rule FROM admin_mailbox mb INNER
> > JOIN admin_domain dom ON mb.domain_id=dom.id INNER JOIN core_user u ON
> > u.id=mb.user_id
> > WHERE (mb.is_send_only IS NOT TRUE OR '%s' NOT IN ('imap', 'pop3',
> 'lmtp'))
> > AND mb.address='%n' AND dom.name='%d'
> > password_query = SELECT email AS user, password, '/srv/vmail/%d/%n' AS
> > userdb_home, 1003 AS userdb_uid, 1003 AS userdb_gid, CONCAT('*:bytes=',
> > mb.quota, 'M') AS userdb_quota_rule FROM core_user u INNER JOIN
> > admin_mailbox mb ON u.id=mb.user_id INNER JOIN admin_domain dom ON
> > mb.domain_id=dom.id WHERE (mb.is_send_only IS NOT TRUE OR '%s' NOT IN
> > ('imap', 'pop3')) AND email='%u' AND is_active AND dom.enabled
> > iterate_query = SELECT email AS user FROM core_user
> >
> >
> >
> > /usr/local/bin/postlogin.sh
> > #!/bin/sh
> > PATH="/usr/bin:/usr/local/bin:/bin"
> > psql -c "UPDATE core_user SET last_login=now() WHERE username='$USER'" >
> > /dev/null
> > exec "$@"
> >
> >
> > /usr/lib/dovecot/sieve-execute/clickhouse_log_frontapp1.sh
> > #!/bin/bash
> > cd /tmp
> > logger -i -t SIEVE_TEST "$1,$2,$3,$4"
> > clickhouse-client -h XXXXXXXXXXXXXXXXX -d default -u some_user --password
> > XXXXXXXXXXXXXXXXX \
> > --param_origin_from="$1" \
> > --param_origin_to="$2" \
> > --param_message_id="$3" \
> > --param_forward="$4" \
> > -q "INSERT INTO frontapp1_redirects
> > (origin_from_email,origin_to_email,message_id,forward_email) VALUES
> >
> ({origin_from:String},{origin_to:String},{message_id:String},{forward:String})"
> >
> > /usr/lib/dovecot/sieve-execute/clickhouse_log.sh
> > #!/bin/bash
> > cd /tmp
> > logger -i -t SIEVE_TEST "$1,$2,$3,$4"
> > clickhouse-client -h XXXXXXXXXXXXXXXXX -d default -u some_user --password
> > XXXXXXXXXXXXXXXXX \
> > --param_origin_from="$1" \
> > --param_origin_to="$2" \
> > --param_message_id="$3" \
> > --param_forward="$4" \
> > -q "INSERT INTO frontapp_redirects
> > (origin_from_email,origin_to_email,message_id,forward_email) VALUES
> >
> ({origin_from:String},{origin_to:String},{message_id:String},{forward:String})"
> > _______________________________________________
> > dovecot mailing list -- dovecot@dovecot.org
> > To unsubscribe send an email to dovecot-le...@dovecot.org
>
>
> --
> "Catch the Magic of Linux..."
> ------------------------------------------------------------------------
> Michael Peddemors, President/CEO LinuxMagic Inc.
> Visit us at http://www.linuxmagic.com @linuxmagic
> A Wizard IT Company - For More Info http://www.wizard.ca
> "LinuxMagic" a Reg. TradeMark of Wizard Tower TechnoServices Ltd.
> ------------------------------------------------------------------------
> 604-682-0300 Beautiful British Columbia, Canada
>
>
_______________________________________________
dovecot mailing list -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-le...@dovecot.org
