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 ).
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
* 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
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
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
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
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
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
* 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 :)
That's the
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 stuff
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 were you
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
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
(Dell 1950 -
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
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
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
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
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. However, I
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
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 asked me
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
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
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 wonderd
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 long
time,
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 that if one of
* 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.
Well, other
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 of
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.
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 you done
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 'vaccuming' one
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
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 to
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 very special
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
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
many
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 see
how
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
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
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
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,
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...
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
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
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 queries.
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
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 plug it in as X
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
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
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 plug
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
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
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
52 matches
Mail list logo
