From superman0920@gmail.com Wed Apr  4 15:53:02 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 61F0F4BB8EB
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 12:53:02 -0300 (ADT)
Received: from mail-pb0-f46.google.com ([209.85.160.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <superman0920@gmail.com>) id 1SFSW5-0006Zh-BJ
	for pgsql-performance@postgresql.org; Wed, 04 Apr 2012 15:53:02 +0000
Received: by pbcun15 with SMTP id un15so429667pbc.19
	for <pgsql-performance@postgresql.org>;
	Wed, 04 Apr 2012 08:52:48 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=date:from:to:reply-to:subject:x-priority:x-guid:x-has-attach
	:x-mailer:mime-version:message-id:content-type;
	bh=TDhodLHzJ/icq7ydk8hm1r7X4LiapgNODbludonAqyE=;
	b=XRr0pxXjOnQIojKRrT4gI7/2BwrdChso0h6iAS7qjUGMkAjfWhtzC+9XPJib7u4tbp
	uBvj69lLvG96nh0V43uj1RRLwoZbEncWokjPC3VURFHPoAIWstOjlERJcTzcUZJy2v+5
	2CtBZOJ/tXmBMmPodptNBSVzjjHL+MaOObldIQO6dWc4UxosSHaO8C0AY00mfr5rQS8T
	Jhfzy5OyQqc5vfxD6gbhhxHUXskdX/5wvBBEorECRIbCDtYAdgCvg6wrOd/2nqze+bvt
	x7e/+fC66JDW3fbGt4plTctbEfjidKhYWuQ4lAAYv1J9hi/afFKompftabagADlQpubb
	67Qw==
Received: by 10.68.197.65 with SMTP id is1mr15561895pbc.70.1333554768310;
	Wed, 04 Apr 2012 08:52:48 -0700 (PDT)
Received: from imsuperman-PC ([113.224.202.112])
	by mx.google.com with ESMTPS id h10sm891842pbe.12.2012.04.04.08.52.45
	(version=SSLv3 cipher=OTHER); Wed, 04 Apr 2012 08:52:47 -0700 (PDT)
Date: Wed, 4 Apr 2012 23:52:51 +0800
From: superman0920 <superman0920@gmail.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Reply-To: superman0920 <superman0920@gmail.com>
Subject: about multiprocessingmassdata
X-Priority: 3
X-GUID: 31691221-7CF6-45E8-86B0-9F518E17D114
X-Has-Attach: no
X-Mailer: Foxmail 7.0.1.88[cn]
Mime-Version: 1.0
Message-ID: <2012040423524938620013@gmail.com>
Content-Type: multipart/alternative;
	boundary="----=_001_NextPart452440284114_=----"
X-Pg-Spam-Score: -2.3 (--)
X-Archive-Number: 201204/41
X-Sequence-Number: 46687

This is a multi-part message in MIME format.

------=_001_NextPart452440284114_=----
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: base64

SGkgbGlzdCENCg0KaSBoYXZlIGEgdGFibGUgd2hpY2ggaGFzIDg1MDAwMDAgcm93cyByZWNvcmRz
LiBpIHdyaXRlIGEgamF2YSBwcm9ncmFtIHRvIHVwZGF0ZSB0aGVzZSByZWNvcmRzLiANCmkgdXNl
IDEwMCB0aHJlYWRzIHRvIHVwZGF0ZSB0aGUgcmVjb3Jkcy4gRm9yIGV4YW1wbGUsIHRocmVhZC0x
IHVwZGF0ZSAxfjg1MDAwIHJlY29yZHM7IHRocmVhZC0yIHVwZGF0ZSA4NTAwMX4xNzAwMDAgYW5k
IHNvIG9uLg0KVGhlIHVwZGF0ZSBzcWwncyBhaW0gaXMgcmVtb3ZlIHRoZSBzcGFjZSBpbiB0aGUg
Y29sdW1uIGFuZCBpdCBpcyBzaW1wbGU6IHVwZGF0ZSBwb2kgc2V0IHB5PXJlcGxhY2UocHksJyAn
LCcnKSB3aGVyZSBpZD0/Ow0KDQpCeSB0aGUgcHJvZ3JhbSBsb2csIGkgZmluZCB0aGUgZGF0YWJh
c2Ugb2YgcHJvY2Vzc2luZyBkYXRhIHNwZWVkIHNvIHNsb3csIHBlciB0aHJlYWQgdXBkYXRpbmcg
MTAwMCByb3dzIG5lZWQgdGFrZSAyNjBzLg0KDQpCVFc6ICBUaGUgUEcgU2VydmVyIGlzIHJ1bm5p
bmcgb24gYSBQQy4gVGhlIFBDJ3MgdG90YWwgbWVtb3J5IGlzIDJHIGFuZCBDUFUgaXMgIkludGVs
KFIpIENvcmUoVE0pMiBEdW8gRTc1MDAgMi45M0dIeiIuDQpXaGVuIHRoZSBwcm9ncmFtJ3MgcnVu
bmluZywgQ1BVIGp1c3QgYmUgdXNlZCAxJSBhbmQgTWVtb3J5ICBsZWZ0IDExMk1CLg0KDQpJcyB0
aGUgUEMgY29uZmlndXJhdGlvbiB0b28gbG93IGNhdXNlIHRoZSBwcm9ibGVtID8NCg0KUGxlYXNl
IGhlbHAgfn4NCg0KDQpUaGFua3MgZm9yIGFueSB0aXBzLA0KDQoNCg0Kc3VwZXJtYW4wOTIw

------=_001_NextPart452440284114_=----
Content-Type: text/html;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dgb2312" http-equiv=3DContent-Type>
<STYLE>
BLOCKQUOTE {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 2em
}
OL {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
UL {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
P {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
DIV.FoxDiv20120404232456117521 {
	LINE-HEIGHT: 1.5; MARGIN: 10px; FONT-FAMILY: =CE=A2=C8=ED=D1=C5=BA=DA; CO=
LOR: #000000; FONT-SIZE: 10.5pt
}
BODY {
	LINE-HEIGHT: 1.5; FONT-FAMILY: =CE=A2=C8=ED=D1=C5=BA=DA; COLOR: #000080; =
FONT-SIZE: 10.5pt
}
</STYLE>

<META name=3DGENERATOR content=3D"MSHTML 9.00.8112.16441">
<STYLE>BLOCKQUOTE {
	MARGIN-TOP: 0px
}
OL {
	MARGIN-TOP: 0px
}
UL {
	MARGIN-TOP: 0px
}
</STYLE>
</HEAD>
<BODY style=3D"MARGIN: 10px">
<DIV>
<DIV class=3DFoxDiv20120404232456117521>
<STYLE>BLOCKQUOTE {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 2em
}
OL {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
UL {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
P {
	MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
</STYLE>

<META name=3DGENERATOR content=3D"MSHTML 9.00.8112.16441">
<DIV>Hi list!</DIV>
<DIV>&nbsp;</DIV>
<DIV>
<DIV>i have a table which has 8500000 rows records. i write a java&nbsp;pr=
ogram=20
to update these records. </DIV>
<DIV>i use 100 threads to update the records. For example, thread-1 update=
=20
1~85000 records; thread-2 update 85001~170000 and so on.</DIV>
<DIV>The update sql's aim&nbsp;is remove the space in the column and it=20
is&nbsp;simple: <SPAN style=3D"COLOR: #008080">update poi set py=3Dreplace=
(py,'=20
','') where id=3D?;</SPAN></DIV>
<DIV><SPAN style=3D"COLOR: #008080"></SPAN>&nbsp;</DIV>
<DIV><SPAN style=3D"COLOR: #000000">By the program log, i find the databas=
e of=20
processing data speed so slow, per thread updating 1000 rows need take=20
260s.</SPAN></DIV>
<DIV><SPAN style=3D"COLOR: #000000"></SPAN>&nbsp;</DIV>
<DIV><SPAN style=3D"COLOR: #000000">BTW:&nbsp;&nbsp;The PG Server is runni=
ng on a=20
PC. The PC's total memory is 2G and CPU is "Intel(R) Core(TM)2 Duo E7500=20
2.93GHz".</SPAN></DIV>
<DIV><SPAN style=3D"COLOR: #000000">When the program's running, CPU just b=
e used=20
1% and Memory&nbsp; left 112MB.</SPAN></DIV>
<DIV><SPAN style=3D"COLOR: #000000"></SPAN>&nbsp;</DIV></DIV>
<DIV>Is the PC configuration too low cause the problem ?</DIV>
<DIV>&nbsp;</DIV>
<DIV>Please help ~~</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV><SPAN=20
style=3D"FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"=
>Thanks=20
for any tips,<?xml:namespace prefix =3D o ns =3D=20
"urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN></DIV>
<HR style=3D"WIDTH: 210px; HEIGHT: 1px" align=3Dleft color=3D#b5c4df SIZE=
=3D1>

<DIV><SPAN>superman0920</SPAN></DIV></DIV></DIV></BODY></HTML>

------=_001_NextPart452440284114_=------




From tv@fuzzy.cz Wed Apr  4 16:35:02 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 6DFC94BB8EB
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 13:35:02 -0300 (ADT)
Received: from [2001:1528:137::f1] (helo=mail.gransy.com)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tv@fuzzy.cz>) id 1SFTAi-0004Xn-37
	for pgsql-performance@postgresql.org; Wed, 04 Apr 2012 16:35:01 +0000
Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	by mail.gransy.com (Postfix) with ESMTP id 604DF508892
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 18:34:47 +0200 (CEST)
Received: from mail.gransy.com ([82.208.29.194])
	by antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	(amavisd-new, port 10024)
	with ESMTP id 3pSn5W8+xaH1 for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 18:34:47 +0200 (CEST)
Received: by mail.gransy.com (Postfix, from userid 5000)
	id 4F76E5088A7; Wed,  4 Apr 2012 18:34:47 +0200 (CEST)
Received: from [192.168.1.237] (ip-78-45-133-255.net.upcbroadband.cz
	[78.45.133.255])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.gransy.com (Postfix) with ESMTPSA id 37C8D508892
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 18:34:47 +0200 (CEST)
Message-ID: <4F7C7827.7080705@fuzzy.cz>
Date: Wed, 04 Apr 2012 18:34:47 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: about multiprocessingmassdata
References: <2012040423524938620013@gmail.com>
In-Reply-To: <2012040423524938620013@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Host-Lookup-Failed: Reverse DNS lookup failed for 2001:1528:137::f1 (failed)
X-Pg-Spam-Score: -1.1 (-)
X-Archive-Number: 201204/45
X-Sequence-Number: 46691

On 4.4.2012 17:52, superman0920 wrote:
> Hi list!
>
> i have a table which has 8500000 rows records. i write a java program to
> update these records.
> i use 100 threads to update the records. For example, thread-1 update
> 1~85000 records; thread-2 update 85001~170000 and so on.
> The update sql's aim is remove the space in the column and it is simple:
> update poi set py=replace(py,' ','') where id=?;

That's a very naive approach. It's very likely each thread will do an
index scan for each update (to evaluate the 'id=?' condition. And that's
going to cost you much more than you gain because index scans are quite
CPU and I/O intensive.

Simply update the whole table by

   UPDATE poi SET py = replace(py, ' ','');

Have you actually tried how this performs or did you guess 'it's
definitely going to be very slow so I'll use multiple threads to make
that faster'?

If you really need to parallelize this, you need to do that differently
- e.g. use 'ctid' to skip to update a whole page like this:

   UPDATE poi SET py = replace(py, ' ','')
    WHERE ctid >= '(n,0)'::tid AND ctid < '(n+1,0)'::tid AND;

where 'n' ranges between 0 and number of pages the table (e.g. in pg_class).

But try the simple UPDATE first, my guess is it's going to be much
faster than you expect.

Tomas



From tv@fuzzy.cz Wed Apr  4 17:00:07 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 6F1314BB8EB
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 14:00:07 -0300 (ADT)
Received: from [2001:1528:137::f1] (helo=mail.gransy.com)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tv@fuzzy.cz>) id 1SFTYx-00054w-84
	for pgsql-performance@postgresql.org; Wed, 04 Apr 2012 17:00:06 +0000
Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	by mail.gransy.com (Postfix) with ESMTP id 0561A508648
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 18:59:51 +0200 (CEST)
Received: from mail.gransy.com ([82.208.29.194])
	by antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	(amavisd-new, port 10024)
	with ESMTP id W7RpVX5xSwvc for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 18:59:50 +0200 (CEST)
Received: by mail.gransy.com (Postfix, from userid 5000)
	id E6C7E5086B4; Wed,  4 Apr 2012 18:59:50 +0200 (CEST)
Received: from [192.168.1.237] (ip-78-45-133-255.net.upcbroadband.cz
	[78.45.133.255])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.gransy.com (Postfix) with ESMTPSA id C2FD3508648
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Apr 2012 18:59:50 +0200 (CEST)
Message-ID: <4F7C7E07.1050507@fuzzy.cz>
Date: Wed, 04 Apr 2012 18:59:51 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: about multiprocessingmassdata
References: <2012040423524938620013@gmail.com>,
	<4F7C7827.7080705@fuzzy.cz> <2012040500491136389424@gmail.com>
In-Reply-To: <2012040500491136389424@gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Host-Lookup-Failed: Reverse DNS lookup failed for 2001:1528:137::f1 (failed)
X-Pg-Spam-Score: -1.1 (-)
X-Archive-Number: 201204/48
X-Sequence-Number: 46694

On 4.4.2012 18:49, superman0920 wrote:
> Thank you for your reply
> I tried executing "UPDATE poi SET py = replace(py, ' ','');", that took
> long long time(about 20+ hours) and no error report. Just like locked.


OK, that's weird. So we need a bit more details - what PostgreSQL
version is this?

How much space does the table actually occupy? Try this:

SELECT relname, relpages, reltuples FROM pg_class WHERE relname = 'poi';

And finally we need EXPLAIN output for both UPDATE commands. Don't post
them here directly - put them to explain.depesz.com and post just the link.

Further, we need to see the actual table definition. Especially if there
are any triggers or foreign keys on the table?

Tomas




From tv@fuzzy.cz Thu Apr  5 14:48:03 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 47AE01BA131A
	for <pgsql-performance@postgresql.org>;
	Thu,  5 Apr 2012 11:48:03 -0300 (ADT)
Received: from [2001:1528:137::f1] (helo=mail.gransy.com)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tv@fuzzy.cz>) id 1SFnyj-0006y9-8z
	for pgsql-performance@postgresql.org; Thu, 05 Apr 2012 14:48:02 +0000
Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	by mail.gransy.com (Postfix) with ESMTP id BA4F450862A
	for <pgsql-performance@postgresql.org>;
	Thu,  5 Apr 2012 16:47:46 +0200 (CEST)
Received: from mail.gransy.com ([82.208.29.194])
	by antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	(amavisd-new, port 10024)
	with ESMTP id kske9IIyCGj6 for <pgsql-performance@postgresql.org>;
	Thu,  5 Apr 2012 16:47:46 +0200 (CEST)
Received: by mail.gransy.com (Postfix, from userid 5000)
	id 9EB4950862D; Thu,  5 Apr 2012 16:47:46 +0200 (CEST)
Received: from [192.168.1.237] (ip-78-45-133-255.net.upcbroadband.cz
	[78.45.133.255])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.gransy.com (Postfix) with ESMTPSA id 7F39650862A
	for <pgsql-performance@postgresql.org>;
	Thu,  5 Apr 2012 16:47:46 +0200 (CEST)
Message-ID: <4F7DB092.1000400@fuzzy.cz>
Date: Thu, 05 Apr 2012 16:47:46 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: about multiprocessingmassdata
References: <2012040423524938620013@gmail.com>,
	<4F7C7827.7080705@fuzzy.cz> <2012040500491136389424@gmail.com>,
	<4F7C7E07.1050507@fuzzy.cz> <201204051028153413205@gmail.com>,
	<4F7D8F28.6000805@fuzzy.cz> <201204052144226175873@gmail.com>
In-Reply-To: <201204052144226175873@gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Host-Lookup-Failed: Reverse DNS lookup failed for 2001:1528:137::f1 (failed)
X-Pg-Spam-Score: -1.1 (-)
X-Archive-Number: 201204/64
X-Sequence-Number: 46710

On 5.4.2012 15:44, superman0920 wrote:
> Sure, i will post that at tomorrow.
>  
> Today I install PG and MySQL at a  Server. I insert 850000 rows record
> to each db.
> I execute "select count(*) from poi_all_new" at two db.
> MySQL takes 0.9s
> PG takes 364s

First of all, keep the list (pgsql-performance@postgresql.org) on the
CC. You keep responding to me directly, therefore others can't respond
to your messages (and help you).

Are you sure the comparison was fair, i.e. both machines containing the
same amount of data (not number of rows, amount of data), configured
properly etc.? Have you used the same table structure (how did you
represent geometry data type in MySQL)?

For example I bet you're using MyISAM. In that case, it's comparing
apples to oranges (or maybe cats, so different it is). MyISAM does not
do any MVCC stuff (visibility checking, ...) and simply reads the number
of rows from a catalogue. PostgreSQL actually has to scan the whole
table - that's a big difference. This is probably the only place where
MySQL (with MyISAM beats PostgreSQL). But once you switch to a proper
storage manager (e.g. InnoDB) it'll have to scan the data just like
PostgreSQL - try that.

Anyway, this benchmark is rubbish because you're not going to do this
query often - use queries that actually make sense for the application.

Nevertheless, it seems there's something seriously wrong with your
machine or the environment (OS), probably I/O.

I've done a quick test - I've created the table (without the 'geometry'
column because I don't have postgis installed), filled it with one
million of rows and executed 'select count(*)'. See this:

    http://pastebin.com/42cAcCqu

This is what I get:

======================================================================
test=# SELECT pg_size_pretty(pg_relation_size('test_table'));
 pg_size_pretty
----------------
 1302 MB
(1 row)

test=#
test=# \timing on
Timing is on.
test=#
test=# SELECT count(*) from test_table;
  count
---------
 1000000
(1 row)

Time: 2026,695 ms
======================================================================

so it's running the 'count(*)' in two seconds. If I run it again, I get
this:

======================================================================
test=# SELECT count(*) from test_table;
  count
---------
 1000000
(1 row)

Time: 270,020 ms
======================================================================

Yes, that's 0,27 seconds. And this is *only* my workstation - Core i5 (4
cores), 8GB of RAM, nothing special.

These results obviously depend on the data being available in page
cache. If that's not the case, PostgreSQL needs to read them from the
drive (and then it's basically i/o bound) - I can get about 250 MB/s
from my drives, so I get this:

======================================================================
test=# SELECT count(*) from test_table;
  count
---------
 1000000
(1 row)

Time: 5088,739 ms
======================================================================

If you have slower drives, the dependency is about linear (half the
speed -> twice the time). So either your drives are very slow, or
there's something rotten.

I still haven's seen iostat / vmstat output ... that'd tell us much more
about the causes.

Tomas



From Kevin.Grittner@wicourts.gov Thu Apr  5 15:01:42 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 56E581BA131A
	for <pgsql-performance@postgresql.org>;
	Thu,  5 Apr 2012 12:01:42 -0300 (ADT)
Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1SFoBw-0004fG-WE
	for pgsql-performance@postgresql.org; Thu, 05 Apr 2012 15:01:41 +0000
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Thu, 05 Apr 2012 10:01:28 -0500
Message-Id: <4F7D6D750200002500046BED@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.2 
Date: Thu, 05 Apr 2012 10:01:25 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Tomas Vondra" <tv@fuzzy.cz>,<pgsql-performance@postgresql.org>
Subject: Re: about multiprocessingmassdata
References: <2012040423524938620013@gmail.com>, <4F7C7827.7080705@fuzzy.cz>
	<2012040500491136389424@gmail.com>, <4F7C7E07.1050507@fuzzy.cz>
	<201204051028153413205@gmail.com>, <4F7D8F28.6000805@fuzzy.cz>
	<201204052144226175873@gmail.com> <4F7DB092.1000400@fuzzy.cz>
In-Reply-To: <4F7DB092.1000400@fuzzy.cz>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Pg-Spam-Score: -4.2 (----)
X-Archive-Number: 201204/65
X-Sequence-Number: 46711

Tomas Vondra <tv@fuzzy.cz> wrote:
> On 5.4.2012 15:44, superman0920 wrote:
 
>> Today I install PG and MySQL at a  Server. I insert 850000 rows
>> record to each db.
>> I execute "select count(*) from poi_all_new" at two db.
>> MySQL takes 0.9s
>> PG takes 364s
 
> Are you sure the comparison was fair, i.e. both machines
> containing the same amount of data (not number of rows, amount of
> data), configured properly etc.?
 
Don't forget the "hint bits" issue -- if the count(*) was run
immediately after the load (without a chance for autovacuum to get
in there), all the data was re-written in place to save hint
information.  I remember how confusing that was for me the first
time I saw it.  It's very easy to get a false impression of overall
PostgreSQL performance from that type of test, and it's the sort of
test a lot of people will do on an ad hoc basis.
 
-Kevin



From mmoncure@gmail.com Mon Apr  9 22:38:13 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 91ABB11C6646
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Apr 2012 19:38:13 -0300 (ADT)
Received: from mail-pz0-f46.google.com ([209.85.210.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mmoncure@gmail.com>) id 1SHNDr-0003Da-Ji
	for pgsql-performance@postgresql.org; Mon, 09 Apr 2012 22:38:12 +0000
Received: by dadz9 with SMTP id z9so6977718dad.33
	for <pgsql-performance@postgresql.org>;
	Mon, 09 Apr 2012 15:37:54 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	bh=Q3KQCymgN+nVguSKCrJwlmDPdfUknqKUy8asJEgabjU=;
	b=E0RvuQ/CdmI3xV0+aBPNkiLvd/oMAwlCdH6Q5W5xPADiDwENviQNBG7EfFNBhqWqu2
	Cx7jrTI8ClBdrTUimtBeMCC/mpIp//kPc3Rt343Beq1r3OX4hsMWFZrnJMyQvtenqg0n
	0HOCOYzTqDojUSKbhAoUzFGAEfGf2jnzoY5xkFdRbFXmGwpSZW+yX7gZMYNw+vfnwWJa
	Fe1TM6152wQo6aMDbyy3645UZLCinefw2Q/ClVCA1OMuf5O6JmlyOaPugJUAhLnLWJuA
	sZriw6D/WxmtdIFSxar7iFxImytP+JEdT4YCGUn61x0pk7Ppkcbe8n+FQNDY67mSTCb2
	NQzg==
MIME-Version: 1.0
Received: by 10.68.222.165 with SMTP id qn5mr23019351pbc.88.1334011074231;
	Mon, 09 Apr 2012 15:37:54 -0700 (PDT)
Received: by 10.142.202.6 with HTTP; Mon, 9 Apr 2012 15:37:54 -0700 (PDT)
In-Reply-To: <4F7DB092.1000400@fuzzy.cz>
References: <2012040423524938620013@gmail.com> <4F7C7827.7080705@fuzzy.cz>
	<2012040500491136389424@gmail.com> <4F7C7E07.1050507@fuzzy.cz>
	<201204051028153413205@gmail.com> <4F7D8F28.6000805@fuzzy.cz>
	<201204052144226175873@gmail.com> <4F7DB092.1000400@fuzzy.cz>
Date: Mon, 9 Apr 2012 17:37:54 -0500
Message-ID: 
 <CAHyXU0zbxgrpSfBumRd-V4GvsXXM1Xh1WC-LTcjBY4c_S9PDmQ@mail.gmail.com>
Subject: Re: about multiprocessingmassdata
From: Merlin Moncure <mmoncure@gmail.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201204/83
X-Sequence-Number: 46729

On Thu, Apr 5, 2012 at 9:47 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 5.4.2012 15:44, superman0920 wrote:
>> Sure, i will post that at tomorrow.
>>
>> Today I install PG and MySQL at a =A0Server. I insert 850000 rows record
>> to each db.
>> I execute "select count(*) from poi_all_new" at two db.
>> MySQL takes 0.9s
>> PG takes 364s
>
> First of all, keep the list (pgsql-performance@postgresql.org) on the
> CC. You keep responding to me directly, therefore others can't respond
> to your messages (and help you).
>
> Are you sure the comparison was fair, i.e. both machines containing the
> same amount of data (not number of rows, amount of data), configured
> properly etc.? Have you used the same table structure (how did you
> represent geometry data type in MySQL)?
>
> For example I bet you're using MyISAM. In that case, it's comparing
> apples to oranges (or maybe cats, so different it is). MyISAM does not
> do any MVCC stuff (visibility checking, ...) and simply reads the number
> of rows from a catalogue. PostgreSQL actually has to scan the whole
> table - that's a big difference. This is probably the only place where
> MySQL (with MyISAM beats PostgreSQL). But once you switch to a proper
> storage manager (e.g. InnoDB) it'll have to scan the data just like
> PostgreSQL - try that.
>
> Anyway, this benchmark is rubbish because you're not going to do this
> query often - use queries that actually make sense for the application.
>
> Nevertheless, it seems there's something seriously wrong with your
> machine or the environment (OS), probably I/O.
>
> I've done a quick test - I've created the table (without the 'geometry'
> column because I don't have postgis installed), filled it with one
> million of rows and executed 'select count(*)'. See this:
>
> =A0 =A0http://pastebin.com/42cAcCqu
>
> This is what I get:
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> test=3D# SELECT pg_size_pretty(pg_relation_size('test_table'));
> =A0pg_size_pretty
> ----------------
> =A01302 MB
> (1 row)
>
> test=3D#
> test=3D# \timing on
> Timing is on.
> test=3D#
> test=3D# SELECT count(*) from test_table;
> =A0count
> ---------
> =A01000000
> (1 row)
>
> Time: 2026,695 ms
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> so it's running the 'count(*)' in two seconds. If I run it again, I get
> this:
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> test=3D# SELECT count(*) from test_table;
> =A0count
> ---------
> =A01000000
> (1 row)
>
> Time: 270,020 ms
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> Yes, that's 0,27 seconds. And this is *only* my workstation - Core i5 (4
> cores), 8GB of RAM, nothing special.
>
> These results obviously depend on the data being available in page
> cache. If that's not the case, PostgreSQL needs to read them from the
> drive (and then it's basically i/o bound) - I can get about 250 MB/s
> from my drives, so I get this:
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> test=3D# SELECT count(*) from test_table;
> =A0count
> ---------
> =A01000000
> (1 row)
>
> Time: 5088,739 ms
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> If you have slower drives, the dependency is about linear (half the
> speed -> twice the time). So either your drives are very slow, or
> there's something rotten.
>
> I still haven's seen iostat / vmstat output ... that'd tell us much more
> about the causes.

geometry column can potentially quite wide.  one thing we need to see
is the table has any indexes -- in particular gist/gin on the
geometry.

merlin



From tv@fuzzy.cz Mon Apr  9 23:51:14 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id F2D1B11C6643
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Apr 2012 20:51:14 -0300 (ADT)
Received: from [2001:1528:137::f1] (helo=mail.gransy.com)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tv@fuzzy.cz>) id 1SHOMa-0004Us-Kd
	for pgsql-performance@postgresql.org; Mon, 09 Apr 2012 23:51:13 +0000
Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	by mail.gransy.com (Postfix) with ESMTP id 63E5650867C
	for <pgsql-performance@postgresql.org>;
	Tue, 10 Apr 2012 01:50:59 +0200 (CEST)
Received: from mail.gransy.com ([82.208.29.194])
	by antispam.gransy.com (antispam.gransy.com [77.78.104.124])
	(amavisd-new, port 10024)
	with ESMTP id 3LtD8EvexWr3 for <pgsql-performance@postgresql.org>;
	Tue, 10 Apr 2012 01:50:59 +0200 (CEST)
Received: by mail.gransy.com (Postfix, from userid 5000)
	id 50B76508680; Tue, 10 Apr 2012 01:50:59 +0200 (CEST)
Received: from [192.168.1.237] (ip-78-45-133-255.net.upcbroadband.cz
	[78.45.133.255])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.gransy.com (Postfix) with ESMTPSA id 2AF6A50867C
	for <pgsql-performance@postgresql.org>;
	Tue, 10 Apr 2012 01:50:59 +0200 (CEST)
Message-ID: <4F8375E3.1030409@fuzzy.cz>
Date: Tue, 10 Apr 2012 01:50:59 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: about multiprocessingmassdata
References: <2012040423524938620013@gmail.com> <4F7C7827.7080705@fuzzy.cz>
	<2012040500491136389424@gmail.com> <4F7C7E07.1050507@fuzzy.cz>
	<201204051028153413205@gmail.com> <4F7D8F28.6000805@fuzzy.cz>
	<201204052144226175873@gmail.com> <4F7DB092.1000400@fuzzy.cz>
	<CAHyXU0zbxgrpSfBumRd-V4GvsXXM1Xh1WC-LTcjBY4c_S9PDmQ@mail.gmail.com>
In-Reply-To: 
 <CAHyXU0zbxgrpSfBumRd-V4GvsXXM1Xh1WC-LTcjBY4c_S9PDmQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Host-Lookup-Failed: Reverse DNS lookup failed for 2001:1528:137::f1 (failed)
X-Pg-Spam-Score: -1.1 (-)
X-Archive-Number: 201204/84
X-Sequence-Number: 46730

On 10.4.2012 00:37, Merlin Moncure wrote:
> On Thu, Apr 5, 2012 at 9:47 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> If you have slower drives, the dependency is about linear (half the
>> speed -> twice the time). So either your drives are very slow, or
>> there's something rotten.
>>
>> I still haven's seen iostat / vmstat output ... that'd tell us much more
>> about the causes.
> 
> geometry column can potentially quite wide.  one thing we need to see
> is the table has any indexes -- in particular gist/gin on the
> geometry.

Yeah, but in one of the previous posts the OP posted this:

relname      | relpages  |  reltuples
-------------+----------+-------------
poi_all_new |  2421133 | 6.53328e+06

which means the table has ~ 19GB for 6.5 million rows, so it's like
2.8GB per 1 million of rows, i.e. ~3kB per row. I've been working with 1
million rows and 1.3GB of data, so it's like 50% of the expected amount.

But this does not explain why the SELECT COUNT(*) takes 364 seconds on
that machine. That'd mean ~8MB/s.

Regarding the indexes, the the OP already posted a description of the
table and apparently there are these indexes:

Indexes:
    "poi_all_new_pk" PRIMARY KEY, btree (ogc_fid)
    "poi_all_new_flname_idx" btree (flname)
    "poi_all_new_geom_idx" btree (wkb_geometry)
    "poi_all_new_ogc_fid_idx" btree (ogc_fid)
    "poi_all_new_pinyin_idx" btree (pinyin)

So none of them is GIN/GIST although some one of them is on the geometry
column.

T.



From mmoncure@gmail.com Tue Apr  10 13:22:11 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 03952189E838
	for <pgsql-performance@postgresql.org>;
	Tue, 10 Apr 2012 10:22:11 -0300 (ADT)
Received: from mail-pb0-f46.google.com ([209.85.160.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mmoncure@gmail.com>) id 1SHb1N-0007SP-29
	for pgsql-performance@postgresql.org; Tue, 10 Apr 2012 13:22:10 +0000
Received: by pbcun15 with SMTP id un15so6211854pbc.19
	for <pgsql-performance@postgresql.org>;
	Tue, 10 Apr 2012 06:21:56 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	bh=xBJgbmiWAnitdzzOw5fsMkoLHRAFIJXLQVLQz9cZOI0=;
	b=vpBXNCc5bGjFcks4S4sy9olmcEtaSqiNpyqeHV/7dUPJ4iudyfaAtuL2KQl3ZGYD1l
	0oHUO0lt1Gt98NdHL0fZnHES74EGQLI0BrCTwu5JUIFB6DcSMsNARr2TWZbbQlUQ8A20
	1PVb0l1WQYIZ6R8b3jVRrTfM7rYot0BX1cksOjHZpnuIqocDJzedtTEkooO5H5S2g232
	sM4mYPyF8fxHWuTYpDrt/ij3xaMZO7FUX18nO3L8r0OKE+NkhhoVKXwM/CK66mPFq/It
	xsRvwvKZD7t3a72CVnOUTcLKRuTeW2FOlSrbnqEVU0+JmbepRZugE0cggY1526q/FGVl
	aKQA==
MIME-Version: 1.0
Received: by 10.68.134.8 with SMTP id pg8mr12083999pbb.152.1334064116224; Tue,
	10 Apr 2012 06:21:56 -0700 (PDT)
Received: by 10.142.202.6 with HTTP; Tue, 10 Apr 2012 06:21:56 -0700 (PDT)
In-Reply-To: <4F8375E3.1030409@fuzzy.cz>
References: <2012040423524938620013@gmail.com> <4F7C7827.7080705@fuzzy.cz>
	<2012040500491136389424@gmail.com> <4F7C7E07.1050507@fuzzy.cz>
	<201204051028153413205@gmail.com> <4F7D8F28.6000805@fuzzy.cz>
	<201204052144226175873@gmail.com> <4F7DB092.1000400@fuzzy.cz>
	<CAHyXU0zbxgrpSfBumRd-V4GvsXXM1Xh1WC-LTcjBY4c_S9PDmQ@mail.gmail.com>
	<4F8375E3.1030409@fuzzy.cz>
Date: Tue, 10 Apr 2012 08:21:56 -0500
Message-ID: 
 <CAHyXU0zKZGAcU3cpdAYGFi846OoZv6my7C7rhxOQaaSMD+8eyw@mail.gmail.com>
Subject: Re: about multiprocessingmassdata
From: Merlin Moncure <mmoncure@gmail.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201204/86
X-Sequence-Number: 46732

On Mon, Apr 9, 2012 at 6:50 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 10.4.2012 00:37, Merlin Moncure wrote:
>> On Thu, Apr 5, 2012 at 9:47 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>> If you have slower drives, the dependency is about linear (half the
>>> speed -> twice the time). So either your drives are very slow, or
>>> there's something rotten.
>>>
>>> I still haven's seen iostat / vmstat output ... that'd tell us much mor=
e
>>> about the causes.
>>
>> geometry column can potentially quite wide. =A0one thing we need to see
>> is the table has any indexes -- in particular gist/gin on the
>> geometry.
>
> Yeah, but in one of the previous posts the OP posted this:
>
> relname =A0 =A0 =A0| relpages =A0| =A0reltuples
> -------------+----------+-------------
> poi_all_new | =A02421133 | 6.53328e+06
>
> which means the table has ~ 19GB for 6.5 million rows, so it's like
> 2.8GB per 1 million of rows, i.e. ~3kB per row. I've been working with 1
> million rows and 1.3GB of data, so it's like 50% of the expected amount.
>
> But this does not explain why the SELECT COUNT(*) takes 364 seconds on
> that machine. That'd mean ~8MB/s.
>
> Regarding the indexes, the the OP already posted a description of the
> table and apparently there are these indexes:
>
> Indexes:
> =A0 =A0"poi_all_new_pk" PRIMARY KEY, btree (ogc_fid)
> =A0 =A0"poi_all_new_flname_idx" btree (flname)
> =A0 =A0"poi_all_new_geom_idx" btree (wkb_geometry)
> =A0 =A0"poi_all_new_ogc_fid_idx" btree (ogc_fid)
> =A0 =A0"poi_all_new_pinyin_idx" btree (pinyin)
>
> So none of them is GIN/GIST although some one of them is on the geometry
> column.

hm. well, there's a duplicate index in there: ogc_fid is indexed
twice.  how much bloat is on the table (let's see an ANALYZE VERBOSE)?
 what's the storage for this database?

merlin