~fkooman

Berlin

https://www.tuxed.net/

Trackers

~fkooman/php-saml-sp

Last active 23 days ago

#112 housekeeping takes way too long to run 23 hours ago

Comment by ~fkooman on ~eduvpn/server

Hmm but reenabling it also returns fast now. Hmmm.

#112 housekeeping takes way too long to run 23 hours ago

Comment by ~fkooman on ~eduvpn/server

Confirmed it is generating the aggregate statistics. Disabling that makes the script return immediately.

#112 housekeeping takes way too long to run 23 hours ago

Comment by ~fkooman on ~eduvpn/server

public function statsAggregate(DateTimeImmutable $dateTime): void
    {
        $stmt = $this->db->prepare(
            <<< 'SQL'
                INSERT INTO
                    aggregate_stats
                SELECT
                    DATE(l.date_time) AS date,
                    l.profile_id AS profile_id,
                    MAX(l.connection_count) AS max_connection_count,
                    COUNT(DISTINCT c.user_id) AS unique_user_count
                FROM
                    live_stats l
                LEFT JOIN
                    connection_log c
                ON
                    DATE(l.date_time) = DATE(c.connected_at)
                AND
                    l.profile_id = c.profile_id
                WHERE
                    l.date_time < :date_time
                GROUP BY
                    date,
                    l.profile_id
                SQL
        );
        $stmt->bindValue(':date_time', $dateTime->format(DateTimeImmutable::ATOM), PDO::PARAM_STR);
        $stmt->execute();
    }

#112 housekeeping takes way too long to run 23 hours ago

Comment by ~fkooman on ~eduvpn/server

I suspect that generating the aggregate statistics takes too long.

// aggregate old entries from the connection statistics
$storage->statsAggregate($oneWeekAgo);

#112 housekeeping takes way too long to run 23 hours ago

Comment by ~fkooman on ~eduvpn/server

It seems to result in issues with the SQlite file:

Dec 06 00:01:03 vpn.example.org vpn-user-portal[291355]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:01:04 vpn.example.org vpn-user-portal[290878]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:01:08 vpn.example.org vpn-user-portal[290969]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:01:08 vpn.example.org vpn-user-portal[293256]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:01:09 vpn.example.org vpn-user-portal[293257]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:02:03 vpn.example.org vpn-user-portal[291355]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:02:04 vpn.example.org vpn-user-portal[290878]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:02:08 vpn.example.org vpn-user-portal[290969]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:02:08 vpn.example.org vpn-user-portal[293256]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:02:09 vpn.example.org vpn-user-portal[293257]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:03:03 vpn.example.org vpn-user-portal[291355]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:03:04 vpn.example.org vpn-user-portal[290878]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:03:08 vpn.example.org vpn-user-portal[290969]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:03:08 vpn.example.org vpn-user-portal[293256]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:03:09 vpn.example.org vpn-user-portal[293257]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:04:03 vpn.example.org vpn-user-portal[291355]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:04:04 vpn.example.org vpn-user-portal[290878]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:04:08 vpn.example.org vpn-user-portal[290969]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:04:08 vpn.example.org vpn-user-portal[293256]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:04:09 vpn.example.org vpn-user-portal[293257]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:05:03 vpn.example.org vpn-user-portal[291355]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:05:04 vpn.example.org vpn-user-portal[290878]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:05:08 vpn.example.org vpn-user-portal[290969]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:05:08 vpn.example.org vpn-user-portal[293256]: SQLSTATE[HY000]: General error: 5 database is locked
Dec 06 00:05:09 vpn.example.org vpn-user-portal[293257]: SQLSTATE[HY000]: General error: 5 database is locked

#112 housekeeping takes way too long to run 23 hours ago

Comment by ~fkooman on ~eduvpn/server

It should really not take 40 seconds with ~100 concurrent users...

#112 housekeeping takes way too long to run 23 hours ago

v3.x added by ~fkooman on ~eduvpn/server

#112 housekeeping takes way too long to run 23 hours ago

Ticket created by ~fkooman on ~eduvpn/server

# time sudo -u www-data /usr/libexec/vpn-user-portal/housekeeping

real	0m39.993s
user	0m37.861s
sys	0m1.995s

#111 when disabling user, also delete manual configs, don't keep them 2 days ago

Comment by ~fkooman on ~eduvpn/server

#98 expose WG public key on "Info" page in admin 2 days ago

Comment by ~fkooman on ~eduvpn/server

There's not really a point doing this, the OpenVPN CA could also be removed I guess.