From: Tomas Vondra <tv@fuzzy.cz>
To: Grzegorz Blinowski <g.blinowski@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: poor pefrormance with regexp searches on large tables
Date: Wed, 10 Aug 2011 17:08:29 +0200
Message-ID: <247fdd1ef5d3d7d5745b9c0318264908.squirrel@sq.gransy.com> (raw)
In-Reply-To: <CAF=aNMHHzBKE8_P0LykDb8Bvk7eHKdytcZ9xAkz=gtpeTj87+w@mail.gmail.com>
References: <CAF=aNMHHzBKE8_P0LykDb8Bvk7eHKdytcZ9xAkz=gtpeTj87+w@mail.gmail.com>
On 10 Srpen 2011, 16:26, Grzegorz Blinowski wrote:
> Now, the query above takes about 60sec to execute; exactly: 70s for the
> first run and 60s for the next runs. In my opinion this is too long: It
> should take 35 s to read the whole table into RAM (assuming 100 MB/s
> transfers - half the HDD benchmarked speed). With 12 GB of RAM the whole
> table should be easily buffered on the operating system level. The regexp
And is it really in the page cache? I'm not an expert in this field, but
I'd guess no. Check if it really gets the data from cache using iostat or
something like that. Use fincore to see what's really in the cache, it's
available here:
http://code.google.com/p/linux-ftools/> Some performance params from postgresql.conf:
> max_connections = 16
> shared_buffers = 24MB
Why just 24MBs? Have you tried with more memory here, e.g. 256MB or 512MB?
I'm not suggesting the whole table should fit here (seq scan uses small
ring cache anyway), but 24MB is just the bare minimum to start the DB.
> Database is vacuumed.
Just vacuumed or compacted? The simple vacuum just marks the dead tuples
as empty, it does not compact the database. So if you've done a lot of
changes and then just run vacuum, it may still may occupy a lot of space
on the disk. How did you get that the table size is 3.5GB? Is that the
size of the raw data, have you used pg_relation_size or something else?
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, g.blinowski@gmail.com
Subject: Re: poor pefrormance with regexp searches on large tables
In-Reply-To: <247fdd1ef5d3d7d5745b9c0318264908.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox