My company is purchasing a Sunfire x4500 to run our most I/O-bound databases,
and I'd like to get some advice on configuration and tuning. We're currently
looking at:
- Solaris 10 + zfs + RAID Z
- CentOS 4 + xfs + RAID 10
- CentOS 4 + ext3 + RAID 10
but we're open to other suggestions.
>From
Craig A. James schrieb:
Tino Wildenhain wrote:
You guys can correct me if I'm wrong, but the key feature that's
missing from Postgres's flexible indexing is the ability to maintain
state across queries. Something like this:
select a, b, my_index_state() from foo where ...
offset 100 limi
Michael Stone schrieb:
On Thu, Mar 22, 2007 at 02:24:39PM -0400, Merlin Moncure wrote:
Tino was saying that rather that build a complete indexing storage
management solution that lives outside the database, it is better to
do intelligent session management so that you get the simplicity if a
two
"Craig A. James" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> You mean
>> http://archives.postgresql.org/pgsql-performance/2006-10/msg00283.php
>> ? I don't see anything there that bears on Steve's suggestion.
> Mea culpa, it's October 8, not October 18:
>http://archives.postgresql.org/pg
Tom Lane wrote:
"Craig A. James" <[EMAIL PROTECTED]> writes:
Steve Atkins wrote:
As long as you're ordering by some row in the table then you can do that in
straight SQL.
select a, b, ts from foo where (stuff) and foo > X order by foo limit 10
Then, record the last value of foo you read, and
On Thu, Mar 22, 2007 at 02:24:39PM -0400, Merlin Moncure wrote:
Tino was saying that rather that build a complete indexing storage
management solution that lives outside the database, it is better to
do intelligent session management so that you get the simplicity if a
two tier client server syst
On Thu, Mar 22, 2007 at 07:24:38PM +0100, Dimitri wrote:
you're right until you're using a single disk :)
Now, imagine you have more disks
I do have more disks. I maximize the I/O performance by dedicating
different sets of disks to different tables. YMMV. I do suggest watching
your I/O rates
"Craig A. James" <[EMAIL PROTECTED]> writes:
> Steve Atkins wrote:
>> As long as you're ordering by some row in the table then you can do that in
>> straight SQL.
>>
>> select a, b, ts from foo where (stuff) and foo > X order by foo limit 10
>>
>> Then, record the last value of foo you read, and
On Mar 22, 2007, at 11:26 AM, Guido Neitzer wrote:
On 22.03.2007, at 11:53, Steve Atkins wrote:
As long as you're ordering by some row in the table then you can
do that in
straight SQL.
select a, b, ts from foo where (stuff) and foo > X order by foo
limit 10
Then, record the last value
On 22.03.2007, at 11:53, Steve Atkins wrote:
As long as you're ordering by some row in the table then you can do
that in
straight SQL.
select a, b, ts from foo where (stuff) and foo > X order by foo
limit 10
Then, record the last value of foo you read, and plug it in as X
the next
time
On 3/22/07, Michael Stone <[EMAIL PROTECTED]> wrote:
On Thu, Mar 22, 2007 at 06:27:32PM +0100, Tino Wildenhain wrote:
>Craig A. James schrieb:
>>You guys can correct me if I'm wrong, but the key feature that's missing
>>from Postgres's flexible indexing is the ability to maintain state
>>across q
Thanks Bill for the explanation - that really helped me out considerably.
What this showed me was that there were only 1024 buffers configured.
I'm not quite clear as to how this happened as the postgresql.conf files
on both systems have the shared_buffers set to ~5. However it looks
as t
Mike,
you're right until you're using a single disk :)
Now, imagine you have more disks - more I/O operations you may perform, and
you'll need also a CPU time to process them :) until you fully use one CPU
per 'vacuumdb' - and then you stop...
As well, even in case when CPU is not highly us
Steve Atkins wrote:
As long as you're ordering by some row in the table then you can do that in
straight SQL.
select a, b, ts from foo where (stuff) and foo > X order by foo limit 10
Then, record the last value of foo you read, and plug it in as X the next
time around.
We've been over this be
On Mar 22, 2007, at 10:21 AM, Craig A. James wrote:
Tino Wildenhain wrote:
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical),
we actually can't use Postgres indexing at all -- we wrote an
entirely separate indexing system for our data...
...Ther
Tino Wildenhain wrote:
You guys can correct me if I'm wrong, but the key feature that's
missing from Postgres's flexible indexing is the ability to maintain
state across queries. Something like this:
select a, b, my_index_state() from foo where ...
offset 100 limit 10 using my_index(prev_
On Thu, Mar 22, 2007 at 06:27:32PM +0100, Tino Wildenhain wrote:
Craig A. James schrieb:
You guys can correct me if I'm wrong, but the key feature that's missing
from Postgres's flexible indexing is the ability to maintain state
across queries. Something like this:
select a, b, my_index_sta
Craig A. James schrieb:
Tino Wildenhain wrote:
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical), we
actually can't use Postgres indexing at all -- we wrote an entirely
separate indexing system for our data...
...There is no need to store or
maintai
Tino Wildenhain wrote:
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical), we
actually can't use Postgres indexing at all -- we wrote an entirely
separate indexing system for our data...
...There is no need to store or
maintain this information along
On Thu, Mar 22, 2007 at 04:55:02PM +0100, Dimitri wrote:
In my case I have several CPU on the server and quite powerful storage box
which is not really busy with a single vacuum. So, my idea is quite simple -
speed-up vacuum with parallel execution (just an algorithm):
Vacuum is I/O intensive,
In response to David Brain <[EMAIL PROTECTED]>:
> Bill Moran wrote:
>
> >
> > Install the pg_bufferstats contrib module and take a look at how shared
> > memory is being use. I like to use MRTG to graph shared buffer usage
> > over time, but you can just do a SELECT count(*) WHERE NOT NULL to s
Brian Hurt wrote:
One of our biggest single problems is this very thing. It's not a
Postgres problem specifically, but more embedded in the idea of a
relational database: There are no "job status" or "rough estimate of
results" or "give me part of the answer" features that are critical to
man
Bill Moran wrote:
Install the pg_bufferstats contrib module and take a look at how shared
memory is being use. I like to use MRTG to graph shared buffer usage
over time, but you can just do a SELECT count(*) WHERE NOT NULL to see
how many buffers are actually in use.
Can you explain what yo
Am Donnerstag, 22. März 2007 16:17 schrieb Andreas Kostyrka:
> * Mario Weilguni <[EMAIL PROTECTED]> [070322 15:59]:
> > Am Donnerstag, 22. März 2007 15:33 schrieb Jonah H. Harris:
> > > On 3/22/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > > > As others suggest select count(*) from table is ver
Craig A. James wrote:
One of our biggest single problems is this very thing. It's not a
Postgres problem specifically, but more embedded in the idea of a
relational database: There are no "job status" or "rough estimate of
results" or "give me part of the answer" features that are critical
Dimitri escribió:
> But of course it will be much more cool to have something like:
>
>vacuumdb -a -P parallel_degree
>
> What do you think? ;)
I think our time is better spent enhancing autovacuum ... but if you
feel like improving vacuumdb, be my guest. This discussion belongs into
pgsql
count(*). I was just discussing general performance issues on the
phone line and when my colleague asked me about the size of the
database he just wonderd why this takes so long for a job his
MS-SQL server is much faster. [...].
Simple. MSSQL is optimized for this case, and uses "older"
On Thursday 22 March 2007 16:12, Alvaro Herrera wrote:
> Dimitri escribió:
> > On Thursday 22 March 2007 14:52, Alvaro Herrera wrote:
> > > Dimitri escribió:
> > > > Folks,
> > > >
> > > > is there any constrains/problems/etc. to run several vacuum processes
> > > > in parallel while each one is 'v
Bill Moran <[EMAIL PROTECTED]> writes:
> In response to David Brain <[EMAIL PROTECTED]>:
>> I am curious as to why 'top' gives such different output on the two
>> systems - the datasets are large and so I know I benefit from having
>> high shared_buffers and effective_cache_size settings.
> Have
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical), we
actually can't use Postgres indexing at all -- we wrote an entirely
separate indexing system for our data, one that has the following
properties:
1. It can give out "pages" of information (i.e. "
Michael Stone wrote:
On Thu, Mar 22, 2007 at 01:30:35PM +0200, [EMAIL PROTECTED] wrote:
approximated count?
why? who would need it? where you can use it?
Do a google query. Look at the top of the page, where it says "results N
to M of about O". For user interfaces (which is where a lot o
* Mario Weilguni <[EMAIL PROTECTED]> [070322 15:59]:
> Am Donnerstag, 22. März 2007 15:33 schrieb Jonah H. Harris:
> > On 3/22/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > > As others suggest select count(*) from table is very special case
> > > which non-mvcc databases can optimize for.
> >
>
Dimitri escribió:
> On Thursday 22 March 2007 14:52, Alvaro Herrera wrote:
> > Dimitri escribió:
> > > Folks,
> > >
> > > is there any constrains/problems/etc. to run several vacuum processes in
> > > parallel while each one is 'vaccuming' one different table?
> >
> > No, no problem. Keep in mind
On Thursday 22 March 2007 14:52, Alvaro Herrera wrote:
> Dimitri escribió:
> > Folks,
> >
> > is there any constrains/problems/etc. to run several vacuum processes in
> > parallel while each one is 'vaccuming' one different table?
>
> No, no problem. Keep in mind that if one of them takes a very l
Andreas,
On 3/22/07 4:48 AM, "Andreas Tille" <[EMAIL PROTECTED]> wrote:
> Well, to be honest I'm not really interested in the performance of
> count(*). I was just discussing general performance issues on the
> phone line and when my colleague asked me about the size of the
> database he just wo
On Thu, Mar 22, 2007 at 10:18:10AM -0400, Michael Stone wrote:
> IIRC, that's basically what you get with the mysql count anyway, since
> there are corner cases for results in a transaction. Avoiding those
> cases is why the postgres count takes so long; sometimes that's what's
> desired and som
On Thu, Mar 22, 2007 at 09:39:18AM -0400, Merlin Moncure wrote:
You can get the approximate count by selecting reltuples from
pg_class. It is valid as of last analyze.
Of course, that only works if you're not using any WHERE clause.
Here's a (somewhat ugly) example of getting an approximate c
On Thu, Mar 22, 2007 at 01:29:46PM +0100, Andreas Kostyrka wrote:
> * Andreas Tille <[EMAIL PROTECTED]> [070322 13:24]:
> > Well, to be honest I'm not really interested in the performance of
> > count(*). I was just discussing general performance issues on the
> > phone line and when my colleague
1= a better HD comparison resource can be found at www.storagereview.com
http://www.storagereview.com/comparison.html
You will find that storagereview has better information on any and
all things HD than Tom's does.
2= DB servers work best with as many spindles as possible. None of
your exa
Am Donnerstag, 22. März 2007 15:33 schrieb Jonah H. Harris:
> On 3/22/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > As others suggest select count(*) from table is very special case
> > which non-mvcc databases can optimize for.
>
> Well, other MVCC database still do it faster than we do. Howe
On 3/22/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
As others suggest select count(*) from table is very special case
which non-mvcc databases can optimize for.
Well, other MVCC database still do it faster than we do. However, I
think we'll be able to use the dead space map for speeding this
On Thu, Mar 22, 2007 at 01:30:35PM +0200, [EMAIL PROTECTED] wrote:
approximated count?
why? who would need it? where you can use it?
Do a google query. Look at the top of the page, where it says
"results N to M of about O". For user interfaces (which is where a lot
of this count(*) stuff
Dimitri escribió:
> Folks,
>
> is there any constrains/problems/etc. to run several vacuum processes in
> parallel while each one is 'vaccuming' one different table?
No, no problem. Keep in mind that if one of them takes a very long
time, the others will not be able to remove dead tuples that we
On 3/22/07, Andreas Tille <[EMAIL PROTECTED]> wrote:
I just try to find out why a simple count(*) might last that long.
At first I tried explain, which rather quickly knows how many rows
to check, but the final count is two orders of magnitude slower.
You can get the approximate count by select
Folks,
is there any constrains/problems/etc. to run several vacuum processes in
parallel while each one is 'vaccuming' one different table?
Example:
vacuum -d db1 -t table1 &
vacuum -d db1 -t table2 &
vacuum -d db1 -t table3 &
wait
(sorry if it was already asked, but I did not find an e
Am Donnerstag, 22. März 2007 12:30 schrieb [EMAIL PROTECTED]:
> approximated count?
>
> why? who would need it? where you can use it?
>
> calculating costs and desiding how to execute query needs
> approximated count, but it's totally worthless information for any user
> IMO.
No, it is not use
In response to David Brain <[EMAIL PROTECTED]>:
>
> Thanks for the response.
> Bill Moran wrote:
> > In response to David Brain <[EMAIL PROTECTED]>:
> >> I recently migrated one of our large (multi-hundred GB) dbs from an
> >> Intel 32bit platform (Dell 1650 - running 8.1.3) to a 64bit platform
Hi,
Thanks for the response.
Bill Moran wrote:
In response to David Brain <[EMAIL PROTECTED]>:
I recently migrated one of our large (multi-hundred GB) dbs from an
Intel 32bit platform (Dell 1650 - running 8.1.3) to a 64bit platform
(Dell 1950 - running 8.1.5). However I am not seeing the perf
In response to David Brain <[EMAIL PROTECTED]>:
>
> I recently migrated one of our large (multi-hundred GB) dbs from an
> Intel 32bit platform (Dell 1650 - running 8.1.3) to a 64bit platform
> (Dell 1950 - running 8.1.5). However I am not seeing the performance
> gains I would expect
What wer
In response to [EMAIL PROTECTED]:
>
> approximated count?
>
> why? who would need it? where you can use it?
>
> calculating costs and desiding how to execute query needs
> approximated count, but it's totally worthless information for any user
> IMO.
I don't think so.
We have some AJAX s
* Andreas Tille <[EMAIL PROTECTED]> [070322 13:24]:
> On Thu, 22 Mar 2007, Andreas Kostyrka wrote:
>
> >Which version of PG?
>
> Ahh, sorry, forgot that. The issue occurs in Debian (Etch) packaged
> version 7.4.16. I plan to switch soon to 8.1.8.
I'd recommend 8.2 if at all possible :)
>
> >Th
Hi,
I recently migrated one of our large (multi-hundred GB) dbs from an
Intel 32bit platform (Dell 1650 - running 8.1.3) to a 64bit platform
(Dell 1950 - running 8.1.5). However I am not seeing the performance
gains I would expect - I am suspecting that some of this is due to
differences I a
On Thu, 22 Mar 2007, Andreas Kostyrka wrote:
Which version of PG?
Ahh, sorry, forgot that. The issue occurs in Debian (Etch) packaged
version 7.4.16. I plan to switch soon to 8.1.8.
That's the reason why PG (check the newest releases, I seem to
remember that there has been some aggregate o
approximated count?
why? who would need it? where you can use it?
calculating costs and desiding how to execute query needs
approximated count, but it's totally worthless information for any user
IMO.
Ismo
On Thu, 22 Mar 2007, Albert Cervera Areny wrote:
> As you can see, PostgreSQL nee
explain is just "quessing" how many rows are in table. sometimes quess is
right, sometimes just an estimate.
sailabdb=# explain SELECT count(*) from sl_tuote;
QUERY PLAN
--
As you can see, PostgreSQL needs to do a sequencial scan to count because its
MVCC nature and indices don't have transaction information. It's a known
drawback inherent to the way PostgreSQL works and which gives very good
results in other areas. It's been talked about adding some kind of
appro
* Andreas Tille <[EMAIL PROTECTED]> [070322 12:07]:
> Hi,
>
> I just try to find out why a simple count(*) might last that long.
> At first I tried explain, which rather quickly knows how many rows
> to check, but the final count is two orders of magnitude slower.
Which version of PG?
The basic
Hi,
I just try to find out why a simple count(*) might last that long.
At first I tried explain, which rather quickly knows how many rows
to check, but the final count is two orders of magnitude slower.
My MS_SQL server using colleague can't believe that.
$ psql InfluenzaWeb -c 'explain SELECT
Hello
I plan to buy a new development server and I wonder what will be the best HD
combination.
I'm aware that "best combination" also relay on DB structure and usage.
so lets assume, heavy duty large DB with mostly reads and heavy write
actions from time to time ( updates / huge transactions ).
59 matches
Mail list logo
