From 78e40c6fe3afe7f815ef9d32646610e2d5436ba3 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 4 Dec 2025 19:11:31 +0100 Subject: Scaling of user statistics (#8277) Fix https://github.com/FreshRSS/FreshRSS/issues/8268 To better support user management on FreshRSS instance with many users. SQL speed improved. On a reduced test with 5 users, including some large accounts (PostgreSQL on a very tiny and slow server), improving from ~2.3s to ~1.8s, which gives ~20% speed improvement. Then tested with 1000 users, with only the default feed (on my old desktop computer): ```sh for i in {1..1000}; do ./cli/create-user.php --user=freshrss$i --password=freshrss; done app/actualize_script.php cli/access-permissions.sh ``` SQLite: ```console $ time cli/user-info.php | wc -l 1001 real 0m1.366s user 0m0.908s sys 0m0.475s ``` PostgreSQL: ```console $ time cli/user-info.php | wc -l 1001 real 0m28.498s user 0m12.137s sys 0m2.217s ``` MariaDB: ```console # time ./cli/user-info.php | wc -l 1001 real 0m49.485s user 0m1.276s sys 0m2.258s ``` Yes, SQLite is much faster - not a surprise for such use-cases, where the TCP connection is not re-used. I have added some CLI options to disable some statistics: ```sh cli/user-info.php --no-db-size --no-db-counts ``` For the Web UI, I have disabled detailed user statistics if it takes too long, and retrieve missing user statistics asynchronously via JavaScript. Lazy loading of the user details based on IntersectionObserver, with maximum 10 requests in parallel. Web UI tested on 1000 users as well. Checked with SeaMonkey. --- app/Models/DatabaseDAO.php | 9 +---- app/Models/EntryDAO.php | 93 +++++++++++++++++----------------------------- app/Models/View.php | 4 +- 3 files changed, 39 insertions(+), 67 deletions(-) (limited to 'app/Models') diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index b34c0fc66..1a6a824e5 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -347,13 +347,8 @@ SQL; //SQLite is the only one with database-level optimization, instead of at table level. $this->optimize(); } - } else { - if ($databaseDAO->exits()) { - $nbEntries = $entryDAO->countUnreadRead(); - if (isset($nbEntries['all']) && $nbEntries['all'] > 0) { - $error = 'Error: Destination database already contains some entries!'; - } - } + } elseif ($databaseDAO->exits() && $entryDAO->count() > 0) { + $error = 'Error: Destination database already contains some entries!'; } break; default: diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index b4f7451c7..eb800ff1e 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -1733,29 +1733,35 @@ SQL; } } - /** @return array */ - public function countUnreadRead(): array { + /** @return array{all:int,unread:int,read:int,favorites:int} */ + public function countAsStates(?int $minPriority = null): array { + $values = []; $sql = <<<'SQL' -SELECT COUNT(e.id) AS count FROM `_entry` e - INNER JOIN `_feed` f ON e.id_feed=f.id - WHERE f.priority > 0 -UNION -SELECT COUNT(e.id) AS count FROM `_entry` e - INNER JOIN `_feed` f ON e.id_feed=f.id - WHERE f.priority > 0 AND e.is_read=0 -SQL; - $res = $this->fetchColumn($sql, 0); - if ($res === null) { - return ['all' => -1, 'unread' => -1, 'read' => -1]; + SELECT + COUNT(*) AS total, + COUNT(CASE WHEN e.is_read = 0 THEN 1 END) AS unread, + COUNT(CASE WHEN e.is_favorite = 1 THEN 1 END) AS favorites + FROM `_entry` e + SQL; + if ($minPriority !== null) { + $sql .= <<<'SQL' + INNER JOIN `_feed` f ON e.id_feed = f.id + WHERE f.priority > :priority + SQL; + $values[':priority'] = $minPriority; } - rsort($res); - $all = (int)($res[0] ?? 0); - $unread = (int)($res[1] ?? 0); - return ['all' => $all, 'unread' => $unread, 'read' => $all - $unread]; + $res = $this->fetchAssoc($sql, $values); + if ($res === null || !isset($res[0])) { + return ['all' => -1, 'unread' => -1, 'read' => -1, 'favorites' => -1]; + } + $all = (int)($res[0]['total'] ?? 0); + $unread = (int)($res[0]['unread'] ?? 0); + $favorites = (int)($res[0]['favorites'] ?? 0); + return ['all' => $all, 'unread' => $unread, 'read' => $all - $unread, 'favorites' => $favorites]; } public function count(?int $minPriority = null): int { - $sql = 'SELECT COUNT(e.id) AS count FROM `_entry` e'; + $sql = 'SELECT COUNT(*) AS count FROM `_entry` e'; $values = []; if ($minPriority !== null) { $sql .= ' INNER JOIN `_feed` f ON e.id_feed=f.id'; @@ -1766,51 +1772,22 @@ SQL; return isset($res[0]) ? (int)($res[0]) : -1; } - public function countNotRead(?int $minPriority = null): int { - $sql = 'SELECT COUNT(e.id) AS count FROM `_entry` e'; - if ($minPriority !== null) { - $sql .= ' INNER JOIN `_feed` f ON e.id_feed=f.id'; - } - $sql .= ' WHERE e.is_read=0'; - $values = []; - if ($minPriority !== null) { - $sql .= ' AND f.priority > :priority'; - $values[':priority'] = $minPriority; - } - $res = $this->fetchColumn($sql, 0, $values); - return isset($res[0]) ? (int)($res[0]) : -1; - } - /** @return array{'all':int,'read':int,'unread':int} */ public function countUnreadReadFavorites(): array { $sql = <<<'SQL' -SELECT c FROM ( - SELECT COUNT(e1.id) AS c, 1 AS o - FROM `_entry` AS e1 - JOIN `_feed` AS f1 ON e1.id_feed = f1.id - WHERE e1.is_favorite = 1 - AND f1.priority >= :priority1 - UNION - SELECT COUNT(e2.id) AS c, 2 AS o - FROM `_entry` AS e2 - JOIN `_feed` AS f2 ON e2.id_feed = f2.id - WHERE e2.is_favorite = 1 - AND e2.is_read = 0 AND f2.priority >= :priority2 - ) u -ORDER BY o -SQL; - //Binding a value more than once is not standard and does not work with native prepared statements (e.g. MySQL) https://bugs.php.net/bug.php?id=40417 - $res = $this->fetchColumn($sql, 0, [ - ':priority1' => FreshRSS_Feed::PRIORITY_CATEGORY, - ':priority2' => FreshRSS_Feed::PRIORITY_CATEGORY, - ]); - if ($res === null) { + SELECT + COUNT(*) AS total, + COUNT(CASE WHEN e.is_read = 0 THEN 1 END) AS unread + FROM `_entry` e + JOIN `_feed` f ON e.id_feed = f.id + WHERE e.is_favorite = 1 AND f.priority > :priority + SQL; + $res = $this->fetchAssoc($sql, [':priority' => FreshRSS_Feed::PRIORITY_HIDDEN]); + if ($res === null || !isset($res[0])) { return ['all' => -1, 'unread' => -1, 'read' => -1]; } - - rsort($res); - $all = (int)($res[0] ?? 0); - $unread = (int)($res[1] ?? 0); + $all = (int)($res[0]['total'] ?? 0); + $unread = (int)($res[0]['unread'] ?? 0); return ['all' => $all, 'unread' => $unread, 'read' => $all - $unread]; } } diff --git a/app/Models/View.php b/app/Models/View.php index b7970b57e..d1d5d0d8e 100644 --- a/app/Models/View.php +++ b/app/Models/View.php @@ -44,12 +44,12 @@ class FreshRSS_View extends Minz_View { public bool $signalError; // Manage users - /** @var array{feed_count:int,article_count:int,database_size:int,language:string,mail_login:string,enabled:bool,is_admin:bool,last_user_activity:string,is_default:bool} */ + /** @var array{feed_count:?int,article_count:?int,database_size:?int,language:string,mail_login:string,enabled:bool,is_admin:bool,last_user_activity:string,is_default:bool} */ public array $details; public bool $disable_aside; public bool $show_email_field; public string $username; - /** @var array */ + /** @var array */ public array $users; // Updates -- cgit v1.2.3