Re: recently and selectively slow, but very simple, update query.... - Mailing list pgsql-performance
| From | Stelios Mavromichalis |
|---|---|
| Subject | Re: recently and selectively slow, but very simple, update query.... |
| Date | |
| Msg-id | CAOfbx2YKcUOMcOxc==rgLOAum5i=4zqhB132Q3QWoVpw04z7cg@mail.gmail.com Whole thread Raw |
| Responses |
Re: recently and selectively slow, but very simple, update
query....
|
| List | pgsql-performance |
hello list,
since i got no reply i am afraid i'll go the dump/restore cycle path hopping this will solve my problem.Stelios Mavromichalis
Cytech Ltd. - http://www.cytech.gr/
Science & Technology Park of Crete
fax: +30 2810 31 1045
tel.: +30 2810 31 4127
mob.: +30 697 7078013
skype: mstelios
Cytech Ltd. - http://www.cytech.gr/
Science & Technology Park of Crete
fax: +30 2810 31 1045
tel.: +30 2810 31 4127
mob.: +30 697 7078013
skype: mstelios
On Mon, May 5, 2014 at 5:11 PM, Stelios Mavromichalis <mstelios@cytech.gr> wrote:
hello,i would dump+restore cycle the database without bothering you, hoping that that would solve my problem, but then i though i wouldn't learn anything out of it, nor you would have the chance to potentially trace a problem/bug thus help the community.
after reading this guide: http://wiki.postgresql.org/wiki/SlowQueryQuestions
i decided to seek for your help.
my problem is that the same query/function some times run fast/normal (as expected) and, recently like 5 days now, some/most of the times, it run really slow to _very_ slow.
the query is in essence a very simple update on a balance of _a certain user_ (no other user has this issue). yes, this user has the most frequent updates to his balance.
i've tried restarting,manual vacuuming (with analyze full etc or not), reindex the database with no improvement. also it's not a hardware problem. diagnostics run fine and no kernel messages or anything weird/unexpected. the load of the machine is also low (like 0.2).
so, without further due:
Full Table and Index Schema
the function that has the problems(easysms_jb_pay(int,int) return int):
Source code
-----------------------------------------------------------
DECLARE
user_id ALIAS FOR $1;
amount ALIAS FOR $2;
myuser record;
mg record;
newbalance float;
BEGIN
SELECT INTO myuser es.login, es.balance as esbalance
from
easysms_users es
where
es.usid = user_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Cannot find user';
return -2;
END IF;
IF myuser.login = 'jbuser' THEN
return -3;
END IF;
IF myuser.esbalance < amount THEN
return -1;
END IF;
UPDATE easysms_users SET balance = balance - amount
WHERE usid = user_id;
return 1;
END;
the related table:
Table "public.easysms_users"
Column | Type | Modifiers
------------------------+-----------------------------+-------------------------------------------------------------
login | character varying(20) |
passwd | character varying(32) | not null
mobile | character varying(16) | not null
name | character varying(20) |
lastname | character varying(20) |
balance | bigint | not null default 0
email | character varying(40) |
status | character varying(1) | default 'p'::character varying
lang | character varying(2) |
trusted | boolean | default false
opt_originator | character varying(16) |
opt_fullname | character varying(50) |
opt_afm | character varying(30) |
opt_invoice_details | text |
opt_postal_address | text |
opt_want_invoice | smallint | default 0
bulklimit | integer | default 100
lastlogin | timestamp without time zone |
daily_report | boolean | default false
pro | boolean | default true
country_code | integer |
mobnumber | character varying(10) |
cctld | character varying(2) |
mpid | integer |
ifee | boolean |
gsm_code | character varying(8) |
account_reminder_email | boolean | default false
usid | integer | default (-2)
namedays | boolean | default true
opt_concat | boolean | default false
opt_smtype | character(1) | default 't'::bpchar
opt_url | text |
opt_permit_concat | boolean | default true
opt_email | boolean | default false
suser | boolean | default false
susid | integer |
perm | character varying(20) |
opt_statsperiod | character varying(3) |
opt_balance | boolean |
opt_lblimit | integer |
opt_override | boolean | default false
opt_letstamp | timestamp with time zone | default (now() - '1 day'::interval)
opt_lbststamp | timestamp with time zone | default now()
opt_pushdlr_enabled | boolean | default false
opt_pushdlr_ltstamp | timestamp with time zone | default now()
opt_pushdlr_rperiod | integer | default 300
opt_pushdlr_dperiod | integer | default 2
opt_pushdlrs | boolean | default false
regdate | timestamp with time zone | not null default ('now'::text)::timestamp(6) with time zone
opt_occupation | character varying(50) |
opt_invoice_address | text |
opt_city | character varying(50) |
opt_invoice_city | character varying(50) |
opt_pcode | character varying(30) |
opt_invoice_pcode | character varying(30) |
opt_doy | character varying(50) |
opt_phone | character varying(50) |
opt_invoice_country | character varying(50) |
opt_country | character varying(50) |
billid | integer |
opt_smpp_enabled | boolean | default false
Indexes:
"idx_easysms_users_usid" UNIQUE, btree (usid)
"easysms_users_cctld_idx" btree (cctld)
"easysms_users_email_idx" btree (email)
"easysms_users_mobile_idx" btree (mobile)
"easysms_users_mpid_idx" btree (mpid)
"easysms_users_status_idx" btree (status)
Table Metadata
done not contain large objects
has a fair amount of nulls
does receive a large number of updates, no deletes
is not growing rapidly, but very slow
indexes you can see the schema
does not use triggers
History
what i've mentioned at the start of this email. i can't think of any event that could link to this behavior.
Hardware Components (Dedicated to dbs, also runs a low traffic mysql, runs open suse 12.3 x86-64bit)
Harddisk 2x 2000 GB SATA 3,5" 7.200 rpm (in raid 1)
RAM 32x Gigabyte RAM
RAID-Controller HP SmartArrayP410 (battery backed, write back is enabled)
Barebone Hewlett Packard DL320e G8
CPU Intel Xeon E3-1230v2
Maintenance Setup
autovacuuming on default settings. manual vacuum only on cases like this and not regularly. see db config
WAL Configuration
nothing special here, all run on same disk/part. see db config
GUC Settings
name | current_setting | source
------------------------------+-------------------+----------------------
application_name | psql | client
checkpoint_completion_target | 0.9 | configuration file
checkpoint_segments | 64 | configuration file
client_encoding | UTF8 | client
client_min_messages | log | configuration file
DateStyle | ISO, DMY | configuration file
deadlock_timeout | 10s | configuration file
debug_print_rewritten | off | configuration file
default_statistics_target | 100 | configuration file
default_text_search_config | pg_catalog.simple | configuration file
effective_cache_size | 8GB | configuration file
fsync | off | configuration file
lc_messages | el_GR.UTF-8 | configuration file
lc_monetary | el_GR.UTF-8 | configuration file
lc_numeric | el_GR.UTF-8 | configuration file
lc_time | el_GR.UTF-8 | configuration file
listen_addresses | * | configuration file
log_connections | off | configuration file
log_destination | syslog | configuration file
log_disconnections | off | configuration file
log_error_verbosity | verbose | configuration file
log_hostname | on | configuration file
log_line_prefix | %d %u | configuration file
log_lock_waits | on | configuration file
log_min_duration_statement | 1s | configuration file
log_min_error_statement | debug5 | configuration file
log_min_messages | info | configuration file
log_statement | none | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 704MB | configuration file
max_connections | 400 | configuration file
max_prepared_transactions | 1000 | configuration file
max_stack_depth | 2MB | environment variable
random_page_cost | 1.5 | configuration file
shared_buffers | 2816MB | configuration file
TimeZone | Europe/Athens | configuration file
wal_buffers | 16MB | configuration file
work_mem | 28MB | configuration file
(38 rows)
Postgres version
# select version();
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.7 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.1 20130909 [gcc-4_8-branch revision 202388], 64-bit
(1 row)
normal speed query that really stacks: <http://explain.depesz.com/s/XeQm>
slow version of it: <http://explain.depesz.com/s/AjwK>thank you so very much in advance for your time and efforts to help.best regards,/mstelios
Stelios Mavromichalis
Cytech Ltd. - http://www.cytech.gr/
Science & Technology Park of Crete
fax: +30 2810 31 1045
tel.: +30 2810 31 4127
mob.: +30 697 7078013
skype: mstelios
pgsql-performance by date: