Re: about multiprocessingmassdata

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  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.

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