Hi!
We have been using postgresql since a while without problems. But now I find we experience some slowness and the weird thing is that it seems to happen because of a very short table (less than 200 lines), called "lockers" (see below it's structure).
This table is accessed very often by a lot of different sessions, with SELECT, UPDATE and DELETE statements. We keep in this table some shared state about our application users, so we make sure they don't work together on the same things, thus the name.
Maybe we do something really wrong. Maybe we even should not use a database for this kind of persistence (we tried a simple file but it was much worse). We have improved the code by merging many little SELECTs into one bigger with all the results needed. This ridiculously short table should hold in cache memory, I'd suppose, so why is it so slow?
Today I found something that could help me to find an answer: by running the "locks" sql below I have above 100 lines of results, with many locks detected.
I have added the indexes sometime ago because, to my great surprise, it did really improve the speed. I just tried to remove them on the fly and it was worse, *but* I had less locks.
It seems I'm a bit stuck here. I'd appreciate some help. My main general question is "how to handle very small but hot status table that has to be updated every 30 seconds by 100 different persons, read and updated from many sides, and also joined with some more common tables (i.e. much larger but less hot)"
Thanks for your help. Please find below some tech info.
Table structure
Column | Type | Modifiers
-----------------------+--------------------------+--------------------------------------------------------
l_id | integer | not null default nextval('lockers_l_id_seq'::regclass)
l_xref_u_id | integer |
l_type | character varying |
l_what | character varying |
l_status | character varying |
l_tech_modification | timestamp with time zone | not null default now()
l_tech_creation | timestamp with time zone | not null default now()
l_tech_deleted | boolean | not null default false
l_status_modification | timestamp with time zone |
l_comment | character varying |
l_csl | character varying |
Indexes:
"lockers_pkey" PRIMARY KEY, btree (l_id)
"lockers_l_csl_idx" btree (l_csl)
"lockers_l_type_idx" btree (l_type)
"lockers_l_what_idx" btree (l_what)
"lockers_l_xref_u_id_idx" btree (l_xref_u_id)
Lockers SQL
SELECT pg_stat_activity.datname,pg_class.relname,pg_locks.transaction, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename, substr(pg_stat_activity.current_query,1,20),
to_char(pg_stat_activity.query_start,'HH24:MI'),
to_char(age(now(),pg_stat_activity.query_start),'HH24:MI') AS "age", pg_stat_activity.procpid
FROM pg_stat_activity,pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE pg_locks.pid=pg_stat_activity.procpid
ORDER BY query_start;
Sample result of lockers SQL
vf_cn2fr | lockers | 468474452 | RowExclusiveLock | t | vf_cn2fr | UPDATE lockers SET l | 15:50 | 00:00 | 30395
vf_cn2fr | lockers | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_csl_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_what_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_type_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_xref_u_id_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_pkey | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | | 468474458 | ExclusiveLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | pg_locks | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid_oid_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | | 468474459 | ExclusiveLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class_relname_nsp_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class_oid_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid_rolname_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_stat_activity | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database_datname_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database_oid_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
We have been using postgresql since a while without problems. But now I find we experience some slowness and the weird thing is that it seems to happen because of a very short table (less than 200 lines), called "lockers" (see below it's structure).
This table is accessed very often by a lot of different sessions, with SELECT, UPDATE and DELETE statements. We keep in this table some shared state about our application users, so we make sure they don't work together on the same things, thus the name.
Maybe we do something really wrong. Maybe we even should not use a database for this kind of persistence (we tried a simple file but it was much worse). We have improved the code by merging many little SELECTs into one bigger with all the results needed. This ridiculously short table should hold in cache memory, I'd suppose, so why is it so slow?
Today I found something that could help me to find an answer: by running the "locks" sql below I have above 100 lines of results, with many locks detected.
I have added the indexes sometime ago because, to my great surprise, it did really improve the speed. I just tried to remove them on the fly and it was worse, *but* I had less locks.
It seems I'm a bit stuck here. I'd appreciate some help. My main general question is "how to handle very small but hot status table that has to be updated every 30 seconds by 100 different persons, read and updated from many sides, and also joined with some more common tables (i.e. much larger but less hot)"
Thanks for your help. Please find below some tech info.
Table structure
Column | Type | Modifiers
-----------------------+--------------------------+--------------------------------------------------------
l_id | integer | not null default nextval('lockers_l_id_seq'::regclass)
l_xref_u_id | integer |
l_type | character varying |
l_what | character varying |
l_status | character varying |
l_tech_modification | timestamp with time zone | not null default now()
l_tech_creation | timestamp with time zone | not null default now()
l_tech_deleted | boolean | not null default false
l_status_modification | timestamp with time zone |
l_comment | character varying |
l_csl | character varying |
Indexes:
"lockers_pkey" PRIMARY KEY, btree (l_id)
"lockers_l_csl_idx" btree (l_csl)
"lockers_l_type_idx" btree (l_type)
"lockers_l_what_idx" btree (l_what)
"lockers_l_xref_u_id_idx" btree (l_xref_u_id)
Lockers SQL
SELECT pg_stat_activity.datname,pg_class.relname,pg_locks.transaction, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename, substr(pg_stat_activity.current_query,1,20),
to_char(pg_stat_activity.query_start,'HH24:MI'),
to_char(age(now(),pg_stat_activity.query_start),'HH24:MI') AS "age", pg_stat_activity.procpid
FROM pg_stat_activity,pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE pg_locks.pid=pg_stat_activity.procpid
ORDER BY query_start;
Sample result of lockers SQL
vf_cn2fr | lockers | 468474452 | RowExclusiveLock | t | vf_cn2fr | UPDATE lockers SET l | 15:50 | 00:00 | 30395
vf_cn2fr | lockers | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_csl_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_what_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_type_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_xref_u_id_idx | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_pkey | 468474458 | AccessShareLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | | 468474458 | ExclusiveLock | t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | pg_locks | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid_oid_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | | 468474459 | ExclusiveLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class_relname_nsp_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class_oid_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid_rolname_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_stat_activity | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database_datname_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database_oid_index | 468474459 | AccessShareLock | t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
