diff options
author | Yorhel <git@yorhel.nl> | 2008-11-29 18:27:52 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2008-11-29 18:27:52 +0100 |
commit | 9d2976410afbc101574d7dfabbdb70b68bf8e419 (patch) | |
tree | df33cfd85f39b4edba6a36e3866b053206a6adc9 /util | |
parent | 2fccaa6db5ca4bf5e2c9f50b810ac24dc14c500e (diff) |
Caching the global database statistics
Because I can't say no to a performance increase of 4 to 7ms
for -every- pageview!
Makes use of postgresql triggers and stored procedures.
Diffstat (limited to 'util')
-rw-r--r-- | util/dump.sql | 80 | ||||
-rw-r--r-- | util/updates/update_2.0.sql | 58 |
2 files changed, 132 insertions, 6 deletions
diff --git a/util/dump.sql b/util/dump.sql index 487950e7..0bbbecbd 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -137,6 +137,12 @@ CREATE TABLE screenshots ( height smallint NOT NULL DEFAULT 0 ); +-- stats_cache +CREATE TABLE stats_cache ( + section varchar(25) NOT NULL PRIMARY KEY, + count integer NOT NULL DEFAULT 0 +); + -- threads CREATE TABLE threads ( id SERIAL NOT NULL PRIMARY KEY, @@ -404,7 +410,17 @@ END; $$ LANGUAGE plpgsql; --- trigger function to keep the c_* columns in the users table up to date + + + + + +----------------------- +-- T R I G G E R S -- +----------------------- + + +-- keep the c_* columns in the users table up to date CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$ BEGIN IF TG_TABLE_NAME = 'votes' THEN @@ -424,6 +440,53 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; +CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); +CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); + + +-- the stats_cache table +CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP = 'INSERT' THEN + IF TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSIF NEW.hidden = FALSE THEN + IF TG_TABLE_NAME = 'threads_posts' THEN + IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + ELSE + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + END IF; + + ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN + IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + + ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + RETURN NULL; +END; +$$ LANGUAGE 'plpgsql'; + +CREATE TRIGGER vn_stats_update AFTER INSERT OR UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER producers_stats_update AFTER INSERT OR UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER releases_stats_update AFTER INSERT OR UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER threads_stats_update AFTER INSERT OR UPDATE ON threads FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); + @@ -434,16 +497,21 @@ $$ LANGUAGE 'plpgsql'; --------------------------------- --- triggers -CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); -CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); - - -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; + +-- Rows that are assumed to be available INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0); INSERT INTO users (username, mail, rank, registered) VALUES ('multi', 'multi@vndb.org', 0, EXTRACT(EPOCH FROM NOW())); +INSERT INTO stats_cache (section, count) VALUES + ('users', 1), + ('vn', 0), + ('producers', 0), + ('releases', 0), + ('threads', 0), + ('threads_posts', 0); + diff --git a/util/updates/update_2.0.sql b/util/updates/update_2.0.sql index 2f8edac4..f074dfee 100644 --- a/util/updates/update_2.0.sql +++ b/util/updates/update_2.0.sql @@ -74,3 +74,61 @@ UPDATE threads_posts SET msg = translate(msg, E'\r', ''); + + +-- cache some database statistics +CREATE TABLE stats_cache ( + section varchar(25) NOT NULL PRIMARY KEY, + count integer NOT NULL DEFAULT 0 +); +INSERT INTO stats_cache (section, count) VALUES + ('users', (SELECT COUNT(*) FROM users)-1), + ('vn', (SELECT COUNT(*) FROM vn WHERE hidden = FALSE)), + ('producers', (SELECT COUNT(*) FROM producers WHERE hidden = FALSE)), + ('releases', (SELECT COUNT(*) FROM releases WHERE hidden = FALSE)), + ('threads', (SELECT COUNT(*) FROM threads WHERE hidden = FALSE)), + ('threads_posts', (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE))); + +CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP = 'INSERT' THEN + IF TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSIF NEW.hidden = FALSE THEN + IF TG_TABLE_NAME = 'threads_posts' THEN + IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + ELSE + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + END IF; + + ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN + IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + + ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + RETURN NULL; +END; +$$ LANGUAGE 'plpgsql'; + +CREATE TRIGGER vn_stats_update AFTER INSERT OR UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER producers_stats_update AFTER INSERT OR UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER releases_stats_update AFTER INSERT OR UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER threads_stats_update AFTER INSERT OR UPDATE ON threads FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); + + |