On Sunday 10 October 2010 21:15:56 Neil Whelchel wrote:
> Right now, I am building a test machine with two dual core Intel processors
> and two 15KRPM mirrored hard drives, 1 GB ram. I am using a small amount of
> ram because I will be using small test tables. I may do testing in the
> future with
On Tue, Oct 12, 2010 at 1:07 PM, Tom Lane wrote:
> Anyway, if anyone is hot to make COUNT(*) faster, that's where to look.
> I don't think any of the previous discussion in this thread is on-point
> at all, except for the parts where people suggested avoiding it.
I kind of hope that index-only sc
On Tue, Oct 12, 2010 at 10:35 AM, Kevin Grittner
wrote:
> (1) Heavily used data could be kept fully cached in RAM and not
> driven out by transient activity.
We've attempted to address this problem by adding logic to prevent the
buffer cache from being trashed by vacuums, bulk loads, and sequenti
Kevin Grittner wrote:
>
> ...Sybase named caches...segment off portions of the memory for
> specific caches... bind specific database
> objects (tables and indexes) to specific caches. ...
>
> When I posted to the list about it, the response was that LRU
> eviction was superior to any tuning an
On Tue, Sep 21, 2010 at 4:30 PM, Tom Lane wrote:
> Ogden writes:
> > SELECT tr.id, tr.sid
> > FROM
> > test_registration tr,
> > INNER JOIN test_registration_result r on (tr.id =
> r.test_registration_id)
> > WHERE.
> >
> tr.test_administration_id=
On Sep 21, 2010, at 6:30 PM, Tom Lane wrote:
> Ogden writes:
>> SELECT tr.id, tr.sid
>>FROM
>>test_registration tr,
>>INNER JOIN test_registration_result r on (tr.id =
>> r.test_registration_id)
>>WHERE.
>>
>> tr.test_administration_id
On 10/12/10 4:33 PM, Neil Whelchel wrote:
On Tuesday 12 October 2010 08:39:19 Dan Harris wrote:
On 10/11/10 8:02 PM, Scott Carey wrote:
would give you a 1MB read-ahead. Also, consider XFS and its built-in
defragmentation. I have found that a longer lived postgres DB will get
extreme file
On Tuesday 12 October 2010 14:35:01 you wrote:
> > suggest that 99% instances of the "select count(*)" idiom are probably
> >
> >> bad use of the SQL language.
>
> Well, suppose you paginate results. If the user sees that the search query
> returns 500 pages, there are two options :
>
> - you're
On Tuesday 12 October 2010 08:39:19 Dan Harris wrote:
> On 10/11/10 8:02 PM, Scott Carey wrote:
> > would give you a 1MB read-ahead. Also, consider XFS and its built-in
> > defragmentation. I have found that a longer lived postgres DB will get
> > extreme file fragmentation over time and sequen
Pierre C wrote:
Well, suppose you paginate results. If the user sees that the search query
returns 500 pages, there are two options :
With Google, I usually lose patience on the page 3. All that I, as an
end user, need to know is whether there are more than 10 pages. The
fact that ther
On Tuesday 12 October 2010 07:19:57 you wrote:
> >> The biggest single problem with "select count(*)" is that it is
> >> seriously overused. People use that idiom to establish existence, which
> >> usually leads to a performance disaster in the application using it,
> >> unless the table has no mor
suggest that 99% instances of the "select count(*)" idiom are probably
bad use of the SQL language.
Well, suppose you paginate results. If the user sees that the search query
returns 500 pages, there are two options :
- you're google, and your sorting algorithms are so good that the answe
Samuel Gendler writes:
> Is there some way to do the drop+rename in a manner which will preserve the
> OID or otherwise allow blocked queries to execute correctly once they
> unblock?
No, but you could consider
begin;
truncate original_table;
insert into original_table se
Chris Browne writes:
> jnelson+pg...@jamponi.net (Jon Nelson) writes:
>> Are there any performance implications (benefits) to executing queries
>> in a transaction where
>> SET TRANSACTION READ ONLY;
>> has been executed?
> Directly? No.
> Indirectly, well, a *leetle* bit...
> Transactions don
I've got a few tables that periodically get entirely refreshed via a COPY. I
don't really have a good mechanism for detecting only rows which have
changed so even though the differences are small, a full copy is easiest.
However, the data includes a primary key column, so I can't simply load
into
jnelson+pg...@jamponi.net (Jon Nelson) writes:
> Are there any performance implications (benefits) to executing queries
> in a transaction where
> SET TRANSACTION READ ONLY;
> has been executed?
Directly? No.
Indirectly, well, a *leetle* bit...
Transactions done READ ONLY do not generate actual
Jon Nelson writes:
> Are there any performance implications (benefits) to executing queries
> in a transaction where
> SET TRANSACTION READ ONLY;
> has been executed?
No.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To
Jon Nelson wrote:
> Are there any performance implications (benefits) to executing
> queries in a transaction where
> SET TRANSACTION READ ONLY;
> has been executed?
I don't think it allows much optimization in any current release.
It wouldn't be a bad idea to use it where appropriate, thoug
Jesper Krogh writes:
> On 2010-10-12 19:07, Tom Lane wrote:
>> Anyway, if anyone is hot to make COUNT(*) faster, that's where to look.
> Just having 32 bytes bytes of "payload" would more or less double
> you time to count if I read you test results correctly?. .. and in the
> situation where dis
On 2010-10-12 19:07, Tom Lane wrote:
Anyway, if anyone is hot to make COUNT(*) faster, that's where to look.
I don't think any of the previous discussion in this thread is on-point
at all, except for the parts where people suggested avoiding it.
I would have to say that allthough it is nice
da...@lang.hm wrote:
On Tue, 12 Oct 2010, Mladen Gogala wrote:
da...@lang.hm wrote:
from a PR point of view, speeding up the trivil count(*) case could be
worth it, just to avoid people complaining about it not being fast.
Fixing PR stuff is not the approach that I would take
Are there any performance implications (benefits) to executing queries
in a transaction where
SET TRANSACTION READ ONLY;
has been executed?
--
Jon
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailp
So I spent a bit of quality time with oprofile this morning, and found
once again that there's no substitute for having actual data before
theorizing.
Test case software: current Git HEAD (plus one code change explained
below), compiled with --enable-debug to support oprofile, cassert off;
no othe
On 10/12/10 10:44 AM, Scott Carey wrote:
On Oct 12, 2010, at 8:39 AM, Dan Harris wrote:
On 10/11/10 8:02 PM, Scott Carey wrote:
would give you a 1MB read-ahead. Also, consider XFS and its built-in
defragmentation. I have found that a longer lived postgres DB will get extreme
file fragmen
mladen.gog...@vmsinfo.com (Mladen Gogala) writes:
> I have a logical problem with asynchronous commit. The "commit"
> command should instruct the database to make the outcome of the
> transaction permanent. The application should wait to see whether the
> commit was successful or not. Asynchronous
cr...@postnewspapers.com.au (Craig Ringer) writes:
> Hey, maybe I should try posting YouTube video answers to a few
> questions for kicks, see how people react ;-)
And make sure it uses the same voice as is used in the "MongoDB is web
scale" video, to ensure that people interpret it correctly :-).
On Oct 12, 2010, at 9:46 AM, Scott Carey wrote:
>
> On Oct 12, 2010, at 8:54 AM, wrote:
>
>> On Tue, 12 Oct 2010, Craig Ringer wrote:
>>
>>> On 10/12/2010 04:22 PM, da...@lang.hm wrote:
>>>
from a PR point of view, speeding up the trivil count(*) case could be
worth it, just to avo
On Oct 12, 2010, at 8:54 AM, wrote:
> On Tue, 12 Oct 2010, Craig Ringer wrote:
>
>> On 10/12/2010 04:22 PM, da...@lang.hm wrote:
>>
>>> from a PR point of view, speeding up the trivil count(*) case could be
>>> worth it, just to avoid people complaining about it not being fast.
>>
>> At the c
On Oct 12, 2010, at 8:39 AM, Dan Harris wrote:
> On 10/11/10 8:02 PM, Scott Carey wrote:
>> would give you a 1MB read-ahead. Also, consider XFS and its built-in
>> defragmentation. I have found that a longer lived postgres DB will get
>> extreme
>> file fragmentation over time and sequential
On 2010-10-12 18:02, Scott Carey wrote:
However, for large reporting queries and sequential scans, XFS will
win in the long run if you use the online defragmenter. Otherwise,
your sequential scans won't be all that sequential on any file system
over time if your tables aren't written once, f
>>
>
> A count with any joins or filter criteria would still have to scan all
> pages with visible tuples in them. So the visibility map helps speed up
> scanning of bloated tables, but doesn't provide a magical "fast count"
> except in the utterly trivial "select count(*) from tablename;" cas
sgend...@ideasculptor.com (Samuel Gendler) writes:
> Geez. I wish someone would have written something quite so bold as
> 'xfs is always faster than ext3' in the standard tuning docs. I
> couldn't find anything that made a strong filesystem
> recommendation. How does xfs compare to ext4? I woun
On Tue, Oct 12, 2010 at 9:02 AM, Scott Carey wrote:
>
>
> However, for large reporting queries and sequential scans, XFS will win in
> the long run if you use the online defragmenter. Otherwise, your sequential
> scans won't be all that sequential on any file system over time if your
> tables aren
On Oct 11, 2010, at 9:21 PM, Samuel Gendler wrote:
On Mon, Oct 11, 2010 at 9:06 PM, Scott Carey
mailto:sc...@richrelevance.com>> wrote:
I can't speak to documentation, but it is something that helps as your I/O
subsystem gets more powerful, and how much it helps depends more on your
hardware
On Tue, 12 Oct 2010, Craig Ringer wrote:
On 10/12/2010 04:22 PM, da...@lang.hm wrote:
from a PR point of view, speeding up the trivil count(*) case could be
worth it, just to avoid people complaining about it not being fast.
At the cost of a fair bit more complexity, though, and slowing ever
On Tue, 12 Oct 2010, Mladen Gogala wrote:
da...@lang.hm wrote:
from a PR point of view, speeding up the trivil count(*) case could be
worth it, just to avoid people complaining about it not being fast.
Fixing PR stuff is not the approach that I would take. People are complaining
about selec
On Tue, 12 Oct 2010, Joe Uhl wrote:
The biggest single problem with "select count(*)" is that it is
seriously overused. People use that idiom to establish existence, which
usually leads to a performance disaster in the application using it,
unless the table has no more than few hundred records.
On 10/11/10 8:02 PM, Scott Carey wrote:
would give you a 1MB read-ahead. Also, consider XFS and its built-in
defragmentation. I have found that a longer lived postgres DB will get extreme
file fragmentation over time and sequential scans end up mostly random.
On-line file defrag helps treme
Jon Nelson wrote:
> Greg Smith wrote:
>> Usually the sequence used to remove all cached data from RAM
>> before a benchmark is:
>
> All cached data (as cached in postgresql - *not* the Linux system
> caches)..., right?
No. The stop and start of PostgreSQL causes empty PostgreSQL
caches. Th
On Sat, Oct 9, 2010 at 4:26 PM, Neil Whelchel wrote:
> Maybe an
> estimate(*) that works like count but gives an answer from the index without
> checking visibility? I am sure that this would be good enough to make a page
> list, it is really no big deal if it errors on the positive side, maybe th
wrote:
> -- None of that is anything for amateurs to play with.
>
> Not jam a stick in anybody's eye, but shouldn't database pros not
> be amateurs?
While many PostgreSQL installations are managed by professional
DBAs, or programmers or consultants with a deep enough grasp of the
issues to tu
Couldn't have said it better myself; covered all the bases. If PG wants to
become an industrial strength database, worthy of replacing DB2/etc., then
these are the sorts of knobs and switches it will need.
-- None of that is anything for amateurs to play with.
Not jam a stick in anybody's ey
wrote:
> An approach that works can be found in DB2, and likely elsewhere.
>
> The key is that tablespaces/tables/indexes/buffers are all
> attached through the bufferpool (the DB2 term). A tablespace/
> bufferpool match is defined. Then tables and indexes are assigned
> to the tablespace (an
The biggest single problem with "select count(*)" is that it is
seriously overused. People use that idiom to establish existence, which
usually leads to a performance disaster in the application using it,
unless the table has no more than few hundred records. SQL language, of
which PostgreSQL offe
The discussions I've seen indicated that, in use, tablespaces were at the
database level, but, yes, the docs do say that a table can be assigned to a
defined tablespace. What I still can't find is syntax which establishes
buffers/caches/whatever and assigns them to tablespaces. Without that, I
Tom Lane wrote:
Mladen Gogala writes:
The number of rows is significantly smaller, but the table contains
rather significant "text" field which consumes quite a bit of TOAST
storage and the sizes are comparable. Postgres read through 27GB in 113
seconds, less than 2 minutes and oracle took
Neil Whelchel wrote:
> What is the best method to make a page of results and a list of
> links to other pages of results?
For our most heavily used web app we decided to have the renderer
just read the list of cases and render the pages to disk, and then
present the first one. We set a limit
Mladen Gogala writes:
> The number of rows is significantly smaller, but the table contains
> rather significant "text" field which consumes quite a bit of TOAST
> storage and the sizes are comparable. Postgres read through 27GB in 113
> seconds, less than 2 minutes and oracle took 2 minutes 37
On Tue, Oct 12, 2010 at 8:18 AM, Greg Smith wrote:
> No. Usually the sequence used to remove all cached data from RAM before a
> benchmark is:
All cached data (as cached in postgresql - *not* the Linux system
caches)..., right?
--
Jon
--
Sent via pgsql-performance mailing list (pgsql-perfor
On Tue, Oct 12, 2010 at 3:07 PM, Jon Nelson wrote:
> On Tue, Oct 12, 2010 at 7:27 AM, Mladen Gogala
> wrote:
>>
>> So, the results weren't cached the first time around. The explanation is the
>> fact that Oracle, as of the version 10.2.0, reads the table in the private
>> process memory, not in t
Jon Nelson wrote:
Is there a command to tell postgresql to drop/clear/reset it's buffer_cache?
No. Usually the sequence used to remove all cached data from RAM before
a benchmark is:
pg_ctl stop
sync
echo 3 > /proc/sys/vm/drop_caches
pg_ctl start
--
Greg Smith, 2ndQuadrant US g...@2ndqu
On Tue, Oct 12, 2010 at 7:27 AM, Mladen Gogala
wrote:
>
> So, the results weren't cached the first time around. The explanation is the
> fact that Oracle, as of the version 10.2.0, reads the table in the private
> process memory, not in the shared buffers. This table alone is 35GB in
> size, Or
On Fri, Oct 8, 2010 at 12:53 AM, Sander, Ingo (NSN - DE/Munich)
wrote:
> The difference to my test is that we use the ODBC interface in our C program.
> Could it be that the difference in the runtimes is caused by the ODBC?
I've heard tell that ODBC is substantially slower than a native libpq
co
On Mon, Oct 11, 2010 at 11:11 PM, wrote:
> An approach that works can be found in DB2, and likely elsewhere.
>
> The key is that tablespaces/tables/indexes/buffers are all attached through
> the bufferpool (the DB2 term). A tablespace/bufferpool match is defined.
> Then tables and indexes are
da...@lang.hm wrote:
from a PR point of view, speeding up the trivil count(*) case could be
worth it, just to avoid people complaining about it not being fast.
Fixing PR stuff is not the approach that I would take. People are
complaining about select count(*) because they're using it in all
On 10/12/2010 04:22 PM, da...@lang.hm wrote:
from a PR point of view, speeding up the trivil count(*) case could be
worth it, just to avoid people complaining about it not being fast.
At the cost of a fair bit more complexity, though, and slowing
everything else down.
The proper solution he
12.10.10 11:14, Craig Ringer написав(ла):
On 10/12/2010 03:56 PM, Vitalii Tymchyshyn wrote:
BTW: There is a lot of talk about MVCC, but is next solution possible:
1) Create a page information map that for each page in the table will
tell you how may rows are within and if any write (either succ
On Tue, 12 Oct 2010, Craig Ringer wrote:
BTW: There is a lot of talk about MVCC, but is next solution possible:
1) Create a page information map that for each page in the table will
tell you how may rows are within and if any write (either successful or
not) were done to this page. This even c
On 10/12/2010 03:56 PM, Vitalii Tymchyshyn wrote:
BTW: There is a lot of talk about MVCC, but is next solution possible:
1) Create a page information map that for each page in the table will
tell you how may rows are within and if any write (either successful or
not) were done to this page. This
11.10.10 20:46, Craig James написав(ла):
First of all, it's not true. There are plenty of applications that
need an exact answer. Second, even if it is only 1%, that means it's
1% of the queries, not 1% of people. Sooner or later a large fraction
of developers will run into this. It's probabl
60 matches
Mail list logo