Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdkMD-0005pz-8S for pgsql-performance@arkaria.postgresql.org; Wed, 28 Nov 2012 16:19:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TdkMB-00028x-RS for pgsql-performance@arkaria.postgresql.org; Wed, 28 Nov 2012 16:19:27 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdkMA-00028q-Mn for pgsql-performance@postgresql.org; Wed, 28 Nov 2012 16:19:26 +0000 Received: from mail-wg0-f50.google.com ([74.125.82.50]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdkM6-0001ja-PM for pgsql-performance@postgresql.org; Wed, 28 Nov 2012 16:19:25 +0000 Received: by mail-wg0-f50.google.com with SMTP id 12so1662505wgh.19 for ; Wed, 28 Nov 2012 08:19:21 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to:x-mailer:x-gm-message-state; bh=ZAfhydBNGTK3oo9QDS7VvNMjaLzw3NFZdl5xmA597vQ=; b=biVXernxCKJyb5i8Yb4uTOU6mxgp+PQcjPMwzSUQJ/uQSXJezF/bAv3KYqm0qY9+4z aKc0t7Zprd9F6+eSBQHObt1Jpzm+Vg2LTPOxONhoaPnOQ0n6N7v61ffWXHSCb5MhcsxM pCmEdKJoNg/zsAwVejHvL8ByU63KK0zD/D9NlWamMZRw2YNKoTjVcwa9iI/Fdo8co4QP 1qeQDBgz8p0wbzicEkXVcBALsa8xkZzS8wNE0jcG8j92q5/v5v0wrHhZRzz1FUOroqFe Hx8Yts0Qtl7jBAk08Vzoi0ZRXNJyyVTFHpAIrFI4AoFeu7il7hvSfD3jfgjSDDJ+7rKp NswA== Received: by 10.180.102.2 with SMTP id fk2mr30340482wib.10.1354119561616; Wed, 28 Nov 2012 08:19:21 -0800 (PST) Received: from [192.168.1.64] (0905ds8-ar.0.fullrate.dk. [90.185.25.212]) by mx.google.com with ESMTPS id dm3sm7585151wib.9.2012.11.28.08.19.19 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 28 Nov 2012 08:19:20 -0800 (PST) Content-Type: multipart/alternative; boundary="Apple-Mail=_66CF4D3E-CAE0-4367-8BBC-59FA942A9C55" Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\)) Subject: Re: Optimize update query From: =?iso-8859-1?Q?Niels_Kristian_Schj=F8dt?= In-Reply-To: Date: Wed, 28 Nov 2012 17:19:17 +0100 Cc: Message-Id: References: <1C199C0D-13DF-47B3-944A-B0560C6D2E5A@autouncle.com> <50B61ABF.4070000@optionshouse.com>, <50B6223B.6000100@mejor.pl> To: Willem Leenen X-Mailer: Apple Mail (2.1499) X-Gm-Message-State: ALoCoQkM6CP0i1zKBGwO534ZpwUuYAHeWmNdIYhM9fmF6xYP4OWAhm14EupLh5bg/g7/SUTCZpcw X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_66CF4D3E-CAE0-4367-8BBC-59FA942A9C55 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=iso-8859-1 Okay guys, Thanks for all the great help and advice already! Let me just clear some = things, to make my question a little easier to answer :-) Now my site is a search engine for used cars - not just a car shop with = a few hundred cars. The update query you look at, is an update that is executed once a day = in chunks for all active adverts, so we know they are still for sale = (one car can be advertised at several places hence several "adverts"). = So it's not a "constant stream" but it has a fairly high volume = especially at night time though. A compressed version of my .conf looks like this (note: there is some = tweaks at the end of the file) data_directory =3D '/var/lib/postgresql/9.2/main' hba_file =3D '/etc/postgresql/9.2/main/pg_hba.conf'=20 ident_file =3D '/etc/postgresql/9.2/main/pg_ident.conf' external_pid_file =3D '/var/run/postgresql/9.2-main.pid'=20 listen_addresses =3D '192.168.0.2, localhost' port =3D 5432 max_connections =3D 1000=20 unix_socket_directory =3D '/var/run/postgresql' wal_level =3D hot_standby synchronous_commit =3D off archive_mode =3D onarchive_command =3D 'rsync -a %p = postgres@192.168.0.4:/var/lib/postgresql/9.2/wals/%f = This shows me, that the by far most time-consuming queries are updates = (in general). On avg. a query like the one I showed you, take 1,3 sec = (but often it takes several minutes - which makes me wonder). So correct = me if I'm wrong here: my theory is, that I have too many too slow update = queries, that then often end up in a situation, where they "wait" for = each other to finish, hence the sometimes VERY long execution times. So = my basic idea here is, that if I could reduce the cost of the updates, = then I could get a hight throughput overall. Here is a sample of the pgBadger analysis: Queries that took up the most time (N) ^ Rank Total duration Times executed Av. duration (s) Query 1 1d15h28m38.71s=09 948,711 0.15s=09 COMMIT; 2 1d2h17m55.43s=09 401,002 0.24s=09 INSERT INTO "car_images" ( "car_id", "created_at", "image", "updated_at" = ) VALUES ( '', '', '', '' ) returning "id"; 3 23h18m33.68s=09 195,093 0.43s=09 SELECT DISTINCT "cars".id FROM "cars" LEFT OUTER JOIN "adverts" ON = "adverts"."car_id" =3D "cars"."id" LEFT OUTERJOIN "sellers" ON = "sellers"."id" =3D "adverts"."seller_id" WHERE "cars"."sales_state" =3D = '' AND "cars"."year" =3D 0 AND"cars"."engine_size" =3D 0.0 AND ( ( = "cars"."id" IS NOT NULL AND "cars"."brand" =3D '' AND = "cars"."model_name" =3D ''AND "cars"."fuel" =3D '' AND "cars"."km" =3D 0 = AND "cars"."price" =3D 0 AND "sellers"."kind" =3D '' ) ) LIMIT 0; 4 22h45m26.52s=09 3,374,133 0.02s=09 SELECT "adverts".* FROM "adverts" WHERE ( source_name =3D '' AND md5 ( = url ) =3D md5 ( '' ) ) LIMIT 0; 5 10h31m37.18s=09 29,671 1.28s=09 UPDATE "adverts" SET "last_observed_at" =3D '', "data_source_id" =3D '' = WHERE "adverts"."id" IN ( ... ) ; 6 7h18m40.65s=09 396,393 0.07s=09 UPDATE "cars" SET "updated_at" =3D '' WHERE "cars"."id" =3D 0; 7 7h6m7.87s=09 241,294 0.11s=09 UPDATE "cars" SET "images_count" =3D COALESCE ( "images_count", 0 ) + 0 = WHERE "cars"."id" =3D 0; 8 6h56m11.78s=09 84,571 0.30s=09 INSERT INTO "failed_adverts" ( "active_record_object_class", = "advert_candidate", "created_at", "exception_class","exception_message", = "from_rescraper", "last_retried_at", "retry_count", "source_name", = "stack_trace","updated_at", "url" ) VALUES ( NULL, '', '', '', '', NULL, = NULL, '', '', '', '', '' ) returning "id"; 9 5h47m25.45s=09 188,402 0.11s=09 INSERT INTO "adverts" ( "availability_state", "car_id", "created_at", = "data_source_id", "deactivated_at","first_extraction", = "last_observed_at", "price", "seller_id", "source_id", "source_name", = "updated_at", "url" )VALUES ( '', '', '', '', NULL, '', '', '', '', '', = '', '', '' ) returning "id"; 10 3h4m26.86s=09 166,235 0.07s=09 UPDATE "adverts" SET "deactivated_at" =3D '', "availability_state" =3D = '', "updated_at" =3D '' WHERE "adverts"."id" =3D 0; (Yes I'm already on the task of improving the selects) Den 28/11/2012 kl. 16.11 skrev Willem Leenen = : >=20 > I assume that SQL databases ( Banks? Telecom?) can handle an used car = shop. No need for an unstructured data tool. >=20 >=20 >=20 > > +1, sql databases has limited number of inserts/updates per second. = Even > > with highend hardware you won't have more than XXX operations per > > second. As Thomas said, you should feed something like nosql = database > > from www server and use other tool to do aggregation and batch = inserts > > to postgresql. It will scale much better. > >=20 > > Marcin --Apple-Mail=_66CF4D3E-CAE0-4367-8BBC-59FA942A9C55 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=iso-8859-1 Okay = guys,

Thanks for all the great help and advice = already! Let me just clear some things, to make my question a little = easier to answer :-)
Now my site is a search engine for used = cars - not just a car shop with a few hundred cars.
The update = query you look at, is an update that is executed once a day in chunks = for all active adverts, so we know they are still for sale (one car can = be advertised at several places hence several "adverts"). So it's not a = "constant stream" but it has a fairly high volume especially at night = time though.

A compressed version of my .conf = looks like this (note: there is some tweaks at the end of the = file)
  data_directory =3D = '/var/lib/postgresql/9.2/main'
  hba_file =3D = '/etc/postgresql/9.2/main/pg_hba.conf' 
  ident_file = =3D '/etc/postgresql/9.2/main/pg_ident.conf'
  = external_pid_file =3D = '/var/run/postgresql/9.2-main.pid' 
  = listen_addresses =3D '192.168.0.2, localhost'
  port =3D = 5432
  max_connections =3D 1000 
  = unix_socket_directory =3D '/var/run/postgresql'
  = wal_level =3D hot_standby
  synchronous_commit =3D = off
  archive_mode =3D onarchive_command =3D 'rsync -a %p = postgres@192.168.0.4:/var/lib/pos= tgresql/9.2/wals/%f </dev/null' 
  = max_wal_senders =3D 1 
  wal_keep_segments =3D = 32
  logging_collector =3D on 
  = log_min_messages =3D debug1 
  = log_min_error_statement =3D debug1
  = log_min_duration_statement =3D 0
  log_checkpoints =3D = on
  log_connections =3D on
  = log_disconnections =3D onlog_line_prefix =3D '%t [%p]: [%l-1] = user=3D%u,db=3D%d '
  log_lock_waits =3D on = log_temp_files =3D 0
  datestyle =3D 'iso, = mdy' 
  lc_messages =3D 'C'
  = lc_monetary =3D 'en_US.UTF-8'
  lc_numeric =3D = 'en_US.UTF-8' 
  lc_time =3D = 'en_US.UTF-8' 
  default_text_search_config =3D = 'pg_catalog.english' 
  default_statistics_target =3D = 100
  maintenance_work_mem =3D 1GB
  = checkpoint_completion_target =3D 0.7
  = effective_cache_size =3D 22GB
  work_mem =3D = 160MB
  wal_buffers =3D 4MB
  = checkpoint_segments =3D 16
  shared_buffers =3D = 7680MB

# All the log stuff is mainly = temporary requirement for pgBadger
# The database has been = tuned with pgtuner

You might be familiar = with new relic, and I use that for quite a lot of monitoring. So, this = is what I see at night time (a lot of I/O). So I went to play = around with pgBadger to get some insights at database = level.
<iframe src=3D"https://rpm.ne= wrelic.com/public/charts/h2dtedghfsv" width=3D"500" height=3D"300" = scrolling=3D"no" = frameborder=3D"no"></iframe>

This = shows me, that the by far most time-consuming queries are updates (in = general). On avg. a query like the one I showed you, take 1,3 sec (but = often it takes several minutes - which makes me wonder). So correct me = if I'm wrong here: my theory is, that I have too many too slow update = queries, that then often end up in a situation, where they "wait" for = each other to finish, hence the sometimes VERY long execution times. So = my basic idea here is, that if I could reduce the cost of the updates, = then I could get a hight throughput = overall.

Here is a sample of the pgBadger = analysis:

Queries that took up the most time = (N) ^
Rank Total duration Times = executed = Av. duration (s) Query
1 = 1d15h28m38.71s
948,711
0.15s =
COMMIT;

2 1d2h17m55.43s =
401,002
0.24s =
INSERT INTO "car_images" ( "car_id", "= created_at", "image", "updated_at" ) VALUES (&nbs= p;'', '', '', '' ) returning "id";

3<= span class=3D"Apple-tab-span" style=3D"white-space:pre"> = 23h18m33.68s
195,093
0.43s =
SELECT DISTINCT "cars".id FROM "cars" = LEFT OUTER JOIN "adverts" ON "adverts"."car_id"&n= bsp;=3D "cars"."id" LEFT OUTERJOIN "sellers" ON&n= bsp;"sellers"."id" =3D "adverts"."seller_id" WHERE "ca= rs"."sales_state" =3D '' AND "cars"."year" =3D&nb= sp;0 AND"cars"."engine_size" =3D 0.0 AND ( (=  "cars"."id" IS NOT NULL AND "cars"."brand"&= nbsp;=3D '' AND "cars"."model_name" =3D ''AND&nbs= p;"cars"."fuel" =3D '' AND "cars"."km" =3D 0=  AND "cars"."price" =3D 0 AND "sellers"."kin= d" =3D '' ) ) LIMIT 0;

4 = 22h45m26.52s
3,374,133
0.02s =
SELECT "adverts".* FROM "adverts" WHERE&nbs= p;( source_name =3D '' AND md5 ( url&nb= sp;) =3D md5 ( '' ) ) LIMIT 0;
=
5 = 10h31m37.18s
29,671
1.28s =
UPDATE "adverts" SET "last_observed_at" =3D=  '', "data_source_id" =3D '' WHERE "adverts"= ."id" IN ( ... ) ;

6 = 7h18m40.65s
396,393
0.07s =
UPDATE "cars" SET "updated_at" =3D ''&= nbsp;WHERE "cars"."id" =3D 0;

7 = 7h6m7.87s =
241,294
0.11s =
UPDATE "cars" SET "images_count" =3D C= OALESCE ( "images_count", 0 ) + 0 WHERE=  "cars"."id" =3D 0;

8 6h56m11.78s =
84,571
0.30s =
INSERT INTO "failed_adverts" ( "active_reco= rd_object_class", "advert_candidate", "created_at", "except= ion_class","exception_message", "from_rescraper", "last_retried_= at", "retry_count", "source_name", "stack_trace","updated_a= t", "url" ) VALUES ( NULL, '', '', = ;'', '', NULL, NULL, '', '', '', '',&nb= sp;'' ) returning "id";

9 = 5h47m25.45s
188,402
0.11s =
INSERT INTO "adverts" ( "availability_state= ", "car_id", "created_at", "data_source_id", "deactiva= ted_at","first_extraction", "last_observed_at", "price", "s= eller_id", "source_id", "source_name", "updated_at", "= url" )VALUES ( '', '', '', '', NULL,&nb= sp;'', '', '', '', '', '', '', '' = ) returning "id";

10 3h4m26.86s =
166,235
0.07s =
UPDATE "adverts" SET "deactivated_at" =3D&n= bsp;'', "availability_state" =3D '', "updated_at" = ;=3D '' WHERE "adverts"."id" =3D 0;

(Yes I'm already on the task of improving the = selects)

Den 28/11/2012 kl. 16.11 skrev Willem = Leenen <willem_leenen@hotmail.com>= ;:


I assume that SQL databases ( Banks? Telecom?) can = handle an used car shop. No need for an unstructured data = tool.



> +1, = sql databases has limited number of inserts/updates per second. = Even
> with highend hardware you won't have more than XXX = operations per
> second. As Thomas said, you should feed something = like nosql database
> from www server and use other tool to do = aggregation and batch inserts
> to postgresql. It will scale much = better.
> 
>= = Marcin

= --Apple-Mail=_66CF4D3E-CAE0-4367-8BBC-59FA942A9C55--