Hi
I am wondering what stripe size, on a raid 0, is the most suitable for
postgres 8.2?
I read a performance tutorial by Bruce Momjian and it suggest setting
the stripe size to the same block size (as pg uses?)
( http://momjian.us/main/writings/pgsql/hw_performance/index.html )
But I want
data for a single street, when I want it for all streets.
Greg Smith wrote:
On Tue, 12 May 2009, Thomas Finneid wrote:
on a database with 260 GB of data and an index size of 109GB on
separate raid disks. there are
85 city_ids, 2000
street_ids per city,
20 house_ids per
Dont worry about it, this is just for performance testing.
thomas
Thomas Finneid wrote:
Hi
I am wondering what stripe size, on a raid 0, is the most suitable for
postgres 8.2?
Hello
Raid 0 for a database? This is a disaster waiting to happen.
Are you sure you want to use raid0
Matthew Wakeling wrote:
Thomas, the order of columns in the index matters. The index is
basically a tree structure, which resolves the left-most column before
resolving the column to the right of it. So to answer your query, it
will resolve the city_id, then it will have to scan almost all of
Hi
have the following table (theoretical)
table apartment_location (
city_idint,
street_id int,
house_id int,
floor_id int,
owner string
...
)
index .. ( city_id, street_id, house_id, floor_id ) tablespc indexspace;
on a database
Joshua D. Drake wrote:
On Sat, 2009-02-07 at 01:52 +0100, Thomas Finneid wrote:
effective_cache_size
This is just a hint to tell the planner how much cache will generally be
available.
ok, but available for what?
The number should be reflective of your shared buffers +
available
Hi
I am having some problem understanding the details of a couple of
configuration directives. Here is what I think I understand it so far:
effective_cache_size
- specifies in how many B/KB/MB will be used to cache data
between pg and the OS?
- what is actually
Scott Marlowe wrote:
I'm guessing that you just had more data in the table or something by
the time you tested that, or some cron job was running in the
background, or some other issue, not the index.
It starts from scratch and builds up. Every insert has constant time
from the first to the
Kenneth Marshall wrote:
It may be that the smaller index has update contention for the same
blocks that the larger index does not.
Is that an assumption based on both indexes existing? if so I might
agree, but if you are talking about only one index existing at a time
then could you explain
Scott Marlowe wrote:
Also, what other kind of usage patterns are going on.
For this test there was nothing else going on, it was just that one
writer. The complete usage pattern is that there is one writer that
writes this data, about 2 rows per second, and then a small number
of
Scott Marlowe wrote:
On Sun, Jan 25, 2009 at 1:14 AM, Thomas Finneid tfinn...@fcon.no wrote:
Scott Marlowe wrote:
So I don't think you've found the cause of your problem with the smaller
index.
Ok I understand, but why dont you think the index is the problem?
If so, I did the test
Hi
I just experienced a performance loss on inserts when redefining my
index on a table.
I have a database with the following table
table:
id1 int
id2 int
id3 int
id4 int
val1 float
tablespace dbspace
the data id distribution is hierarchical and even, well
Scott Marlowe wrote:
On Sat, Jan 24, 2009 at 3:57 AM, Thomas Finneid tfinn...@fcon.no wrote:
Is this table constantly growing, or is it at a stable plateu? I'd
assume a constantly growing table, or one with bloat problems would
get slower over time. About how many rows does this table have
Hi
I have noticed that my transaction log has quite large activity volume
(up to 15MB per transaction), so with the amount of data I am using I
have manually moved the pg_xlog directory to a different disk. This
allows me to have both the table space and transaction log on two
different high
Hi
A quick question, when pg receives data to be written to a table, does
it cache that data in memory in case a subsequent request/query would
need it?
As I understand it, data is stored in pages and those pages have to be
retrieved in order to write or read data from them. So my
Pavan Deolasee wrote:
On Thu, Jan 22, 2009 at 4:42 PM, Thomas Finneid tfinn...@fcon.no wrote:
As I understand it, data is stored in pages and those pages have to be
retrieved in order to write or read data from them. So my assumption is that
a page used to write data would not be replaced
(Sorry, did not include the list in the reply)
Pavan Deolasee wrote:
Yes. That's how it works.
Is that how it works for an index as well? I just found out that I have
an index that is 35GB, and the table is 85GB. ( I will look into the
index, it works fine, but an index that is almost
Thomas Markus wrote:
try to reorganize your data with CLUSTER and create appropriate indixes
(dont forget to check statistics).
One question. Assume I have clustered and new data has been added after
that, according to the docs that data is added outside of the
clustered data. What happens
Hi I am developing a database and have a couple of questions I havent
found an answer to yet.
1) how do I find the size of an index, i.e. the size on disk?
2) I have a query that is taking a long time to complete because the
table is about 120GB large. Its only returning 2000 rows, so in
Greg Smith wrote:
In practice, the ordered
mode (the default for ext3) seems sufficient to prevent database
corruption. There is a substantial performance hit to running in full
journal mode like you're doing;
where do you see which mode I am running in? I havent specified any
modes in
to force inode syncing (dont rememver the name for the filesystem
structure in unix memory). The same question can be asked about the
async option.
any thoughts?
thomas
Greg Smith wrote:
On Tue, 2 Sep 2008, Thomas Finneid wrote:
You should try setting this to open_sync , that can be considerably
[EMAIL PROTECTED] wrote:
Is there some kind of limit in postgresql about the number of partitions ? Do
you know some tuning in the conf files to improve postgresql management of so
many tables ? I have already used different tablespaces, one for each main table
and its 288 partitions.
Scott Carey wrote:
Are you even getting COPY to work with JDBC? As far as I am aware, COPY
doesn't work with JDBC at the moment:
I used a patched jdbc driver, provided by someone on the list, dont have
the reference at hand. It works perfectly and its about 5 times faster,
for my job, than
Craig Ringer wrote:
Just on a side note, your system is pretty strangely heavy on CPU
compared to its RAM and disk configuration. Unless your workload in Pg
is computationally intensive or you have something else hosted on the
same machine, those CPUs will probably sit mostly idle.
Its a
Scott Carey wrote:
For a development box, just enable write-back caching regardless of the
battery back up situation. As long as its not your only copy of
Will have a look at it, the data is not important and can be reproduced
any time on any machine. The controller I have is a Areca
Hi
I am working on a table which stores up to 125K rows per second and I
find that the inserts are a little bit slow. The insert is in reality a
COPY of a chunk of rows, up to 125K. A COPY og 25K rows, without an
index, is fast enough, about 150ms. With the index, the insert takes
about
Scott Carey wrote:
You may want to investigate pg_bulkload.
http://pgbulkload.projects.postgresql.org/
One major enhancement over COPY is that it does an index merge, rather
than modify the index one row at a time.
This is a command line tool, right? I need a jdbc driver tool, is that
Hi
I couldnt find any specifics on this subject in the documentation, so I
thought I'd ask the group.
how does pg utilise multi cpus/cores, i.e. does it use more than one
core? and possibly, how, are there any documentation about this.
thomas
---(end of
PFC wrote:
Unfortunately its not fast enough, it needs to be done in no more than
1-2 seconds, ( and in production it will be maybe 20-50 columns of
data, perhaps divided over 5-10 tables.)
Additionally it needs to scale to perhaps three times as many columns
and perhaps 2 - 3 times as many
Michael Glaesemann wrote:
As they're individual inserts, I think what you're seeing is overhead
from calling this statement 100,000 times, not just on the server but
also the overhead through JDBC. For comparison, try
CREATE TABLE ciu_data_type_copy LIKE ciu_data_type;
INSERT INTO
Michael Stone wrote:
On Tue, Jul 17, 2007 at 11:01:15PM +0200, Thomas Finneid wrote:
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar
to what happens in there.
No, because it only works on file to db or vice versa not table to table.
I
Kalle Hallivuori wrote:
Hi.
2007/7/8, Thomas Finneid [EMAIL PROTECTED]:
Kalle Hallivuori wrote:
COPY is plentitudes faster than INSERT:
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
If you can't just push the data straight into the final table with
COPY, push
Hi
I was doing some testing on insert compared to select into. I
inserted 100 000 rows (with 8 column values) into a table, which took 14
seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit was turned off and it all
happend on the local
Tom Lane wrote:
Michael Glaesemann [EMAIL PROTECTED] writes:
It would be helpful if you included the actual queries you're using,
as there are a number of variables:
Not to mention which PG version he's testing.
Its pg 8.1, for now, I'll be upgrading to a compile optimised 8.2 when I
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar to
what happens in there.
No, because it only works on file to db or vice versa not table to table.
regards
thoams
---(end of broadcast)---
TIP
PFC wrote:
I was doing some testing on insert compared to select into. I
inserted 100 000 rows (with 8 column values) into a table, which took
14 seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit was turned off and it all
happend on the
Hi
During the somes I did I noticed that it does not necessarily seem to be
true that one needs the fastest disks to have a pg system that is fast.
It seems to me that its more important to:
- choose the correct methods to use for the operation
- tune the pg memory settings
- tune/disable pg
Kalle Hallivuori wrote:
COPY is plentitudes faster than INSERT:
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
If you can't just push the data straight into the final table with
COPY, push it into a temporary table that you go through with the
database procedure.
Shameless
Heikki Linnakangas wrote:
ISTM that a properly normalized schema would look something like this:
create table position (
posX int not null,
posY int not null,
primary key (posX, posY)
);
create table colour (
posX int not null,
posY int not null,
colour varchar(50) not null,
39 matches
Mail list logo
