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 <mstel...@cytech.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 >
