Re: recently and selectively slow, but very simple, update query....

hello list,

since i got no reply i am afraid i'll go the dump/restore cycle path
hopping this will solve my problem.

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

On Mon, May 5, 2014 at 5:11 PM, Stelios Mavromichalis <mstelios(at)cytech(dot)gr>wrote:

> hello,
>
> 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).
>
> 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.
>
> 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
>