From: | "Burak Seydioglu" <buraks78(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Query performance on session table |
Date: | 2007-06-28 20:26:41 |
Message-ID: | 1b8a973c0706281326r266c8cf7n5336581fec1a8be1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello,
I am having performance issues with a table that holds session_data.
This table is heavily updated and daily vacuumed. Please see the
information below. I was not able to pinpoint the issue so any help
would be appreciated.
Burak
####################
### Table Structure ###
####################
CREATE TABLE session (
session_id char(40) NOT NULL PRIMARY KEY,
session_expires integer NOT NULL,
session_data text
);
##################
### Sample Row ###
##################
1987b8db3ab36c18d0da7f9c2915194092f7bdf7 | 1183066733 |
hkmTZblHHF+cY8g 6Dx/K0ioEc98QdmOKST ocRd P2gUfsTsMeMBV
wGiGbhTJ0CuimDVpv hH8TdWjqMc3rJW7dHJ wjdsrNaqXUpEG+9HvnbKgngG9cqa
p2IxjeTD7k8G/5ZIDrvk3+DSoFu2FB47qvacNmH+hzM U
d1Fn8oKERa1qc+AcuLxLQKdwQUV H8ZE7WXNG etkGq/LSlgIOpTyb44oy5C5evlWSiT1
A2iwCIT8kxrCn5+Avrrg/zLQ muZkBqXd5 vvPcL5 mXEhnu4b96Zy/YpyEnUcV
8coPBw1p0s1i5lwjWyMHYo7H 64HfON8prMizrEoTNyTeMt7jbxo3v
I0Ds+xP9QxY8hpv+4Hc+GN ........ encrypted data continues....
######################################
### EXPLAIN ANALYZE before VACUUM ###
######################################
# explain analyze select * from session;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on session (cost=0.00..373745.36 rows=736 width=269)
(actual time=32824.964..75997.206 rows=710 loops=1)
Total runtime: 75998.003 ms
(2 rows)
#####################################
### EXPLAIN ANALYZE after VACUUM ###
#####################################
# explain analyze select * from session;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on session (cost=0.00..373753.47 rows=747 width=282)
(actual time=1357.697..60344.110 rows=731 loops=1)
Total runtime: 60344.971 ms
(2 rows)
60 seconds for 747 rows!
#################
### Other tables ###
#################
Other tables in the database are performing just fine. Please see the
below query plan for reference.
# explain analyze select * from user;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on user (cost=0.00..2650.49 rows=25849 width=254) (actual
time=22.087..3946.991 rows=25866 loops=1)
Total runtime: 3971.146 ms
(2 rows)
~4 secs for 25849 rows.
################
### More tests ###
################
# explain analyze select * from session order by session_expires desc limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=373789.12..373789.12 rows=1 width=282) (actual
time=49065.062..49065.064 rows=1 loops=1)
-> Sort (cost=373789.12..373790.99 rows=747 width=282) (actual
time=49065.058..49065.058 rows=1 loops=1)
Sort Key: session_expires
-> Seq Scan on session (cost=0.00..373753.47 rows=747
width=282) (actual time=5514.580..49063.397 rows=729 loops=1)
Total runtime: 49065.152 ms
(5 rows)
# explain analyze select * from session where
session_id='1987b8db3ab36c18d0da7f9c2915194092f7bdf7';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using session_pkey on ce_session (cost=0.00..12.05 rows=1
width=282) (actual time=28.113..28.115 rows=1 loops=1)
Index Cond: (session_id = '1987b8db3ab36c18d0da7f9c2915194092f7bdf7'::bpchar)
Total runtime: 28.144 ms
(3 rows)
##################
### Configuration ###
##################
RedHat EL3, PostgreSQL 7.4
# - Memory -
shared_buffers = 30000
sort_mem = 5120
vacuum_mem = 32768
# - Free Space Map -
max_fsm_pages = 20000
max_fsm_relations = 1000
# - Kernel Resource Usage -
max_files_per_process = 1000
preload_libraries = ''
# - Settings -
fsync = true
wal_sync_method = fsync
wal_buffers = 8
# - Checkpoints -
checkpoint_segments = 20
checkpoint_timeout = 300
checkpoint_warning = 30
commit_delay = 0
commit_siblings = 5
# - Planner Cost Constants -
effective_cache_size = 40000
random_page_cost = 4
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.001
cpu_operator_cost = 0.0025
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2007-06-28 21:38:37 | Re: Query performance on session table |
Previous Message | Carol Cheung | 2007-06-27 22:09:08 | Re: convert column of integer type to time type? |