Dear All,
I have some problems with regexp queries performance - common sense tells me
that my queries should run faster than they do.
The database - table in question has 590 K records, table's size is 3.5GB. I
am effectively querying a single attribute "subject" which has an average
size of 2KB
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 b
Try to use single regular expression.
2011/8/10, Grzegorz Blinowski :
> Dear All,
>
> I have some problems with regexp queries performance - common sense tells me
> that my queries should run faster than they do.
>
> The database - table in question has 590 K records, table's size is 3.5GB. I
> am
Grzegorz Blinowski wrote:
> Some performance params from postgresql.conf:
Please paste the result of running the query on this page:
http://wiki.postgresql.org/wiki/Server_Configuration
For a start, the general advice is usually to start with
shared_buffers at the lesser of 25% of system R
Greetings,
I've been hitting a "out of memory error" during autovacuum of
relatively large tables (compared to the amount of RAM available). I'm
trying to trace the cause of the issue; the answer is somewhere below
and I don't know how to interpret the data. I can solve the issue
right now by usin
Thnaks for all the help so far, I increased the shared_mem config parameter
(Postgress didn't accept higher values than default, had to increase
systemwide shared mem). The current config (as suggested by Kevin Grittner)
is as follows:
version | PostgreSQL 8.4.7 on x86_64-redha
Dne 10.8.2011 19:01, Grzegorz Blinowski napsal(a):
> However, changing shared_mem didn't help. We also checked system I/O
> stats during the query - and in fact there is almost no IO (even with
> suboptimal shared_memory). So the problem is not disk transfer/access
> but rather the way Postgres han
Grzegorz Blinowski wrote:
> the problem is not disk transfer/access but rather the way
> Postgres handles regexp queries.
As a diagnostic step, could you figure out some non-regexp way to
select about the same percentage of rows with about the same
distribution across the table, and compare ti
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes:
> I've been hitting a "out of memory error" during autovacuum of
> relatively large tables (compared to the amount of RAM available).
> The error message is:
> [10236]: [1-1] user=,db=,remote= ERROR: out of memory
> [10236]: [2-1] user=,db=,remote= DE
Grzegorz Blinowski wrote:
> autovacuum| off
BTW, that's generally not a good idea -- it leaves you much more
vulnerable to bloat which could cause performance problems to
manifest in any number of ways. You might want to calculate your
heap bloat on this table.
-Kevin
--
S
"Kevin Grittner" wrote:
> So far I haven't seen any real indication that the time is spent
> in evaluating the regular expressions
Just as a reality check here, I ran some counts against a
moderately-sized table (half a million rows). Just counting the
rows unconditionally was about five time
On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote:
> Alexis Le-Quoc writes:
>> I've been hitting a "out of memory error" during autovacuum of
>> relatively large tables (compared to the amount of RAM available).
>
>> The error message is:
>> [10236]: [1-1] user=,db=,remote= ERROR: out of memory
>>
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes:
> On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote:
>> However, I find it a bit odd that you're getting this failure in what
>> appears to be a 64-bit build. That means you're not running out of
>> address space, so you must actually be out of RAM+swap
On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane wrote:
> Alexis Le-Quoc writes:
>> On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote:
>>> However, I find it a bit odd that you're getting this failure in what
>>> appears to be a 64-bit build. That means you're not running out of
>>> address space, so yo
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes:
> On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane wrote:
>> Hmph. Is there other stuff being run on the same instance? Are there a
>> whole lot of active PG processes? Maybe Amazon isn't really giving you
>> a whole 8GB, or there are weird address space r
15 matches
Mail list logo