Direct I/O

#21

Anselmo Canfora wrote:

Quote:

I ran a simple dumb test, actually it seems that lowering fillfactor improves update speed and decrease insert speed as it shuld
be in theory, it is a matter of tradeoffs between update speed and read speed.

a) There's no such thing as a free lunch.
b) While I expect tradeoffs, your statement confuses me:
First you write that INSERT speed decreased, then you seem to
refer to that as "read speed".

I would expect slight decreases in INSERT speed (more empty space
is written out to disk) *and* (initially) in the speed of table scans
(more empty space is read in from disk).

Quote:

Perhaps for maintenance updatates on a large part of big tables as I am thinking about it is better to drop related indexes, do
the update and then rebuild the index.

I agree.

Yours,
Laurenz Albe

#22

Il 03/02/2010 10.22, Laurenz Albe ha scritto:

Quote:

Anselmo Canfora wrote:
I ran a simple dumb test, actually it seems that lowering fillfactor improves update speed and decrease insert speed as it shuld
be in theory, it is a matter of tradeoffs between update speed and read speed.

a) There's no such thing as a free lunch.
b) While I expect tradeoffs, your statement confuses me:
First you write that INSERT speed decreased, then you seem to
refer to that as "read speed".

I skip read test, either selects and inserts are impacted

Quote:

I would expect slight decreases in INSERT speed (more empty space
is written out to disk) *and* (initially) in the speed of table scans
(more empty space is read in from disk).

I believe the decrease in index scans should be persistent, because one
has to traverse void index areas too

Quote:

Perhaps for maintenance updatates on a large part of big tables as I am thinking about it is better to drop related indexes, do
the update and then rebuild the index.

I agree.

Yours,
Laurenz Albe

#23

Il 03/02/2010 10.22, Laurenz Albe ha scritto:

Quote:

Anselmo Canfora wrote:
I ran a simple dumb test, actually it seems that lowering fillfactor improves update speed and decrease insert speed as it shuld
be in theory, it is a matter of tradeoffs between update speed and read speed.

a) There's no such thing as a free lunch.
b) While I expect tradeoffs, your statement confuses me:
First you write that INSERT speed decreased, then you seem to
refer to that as "read speed".

I skip read test, either selects and inserts are impacted

Quote:

I would expect slight decreases in INSERT speed (more empty space
is written out to disk) *and* (initially) in the speed of table scans
(more empty space is read in from disk).

I believe the decrease in index scans should be persistent, because one
has to traverse void index areas too

Quote:

Perhaps for maintenance updatates on a large part of big tables as I am thinking about it is better to drop related indexes, do
the update and then rebuild the index.

I agree.

Yours,
Laurenz Albe

#24

Anselmo Canfora wrote:

Quote:

I would expect slight decreases in INSERT speed (more empty space
is written out to disk) *and* (initially) in the speed of table scans
(more empty space is read in from disk).

I believe the decrease in index scans should be persistent, because one has to traverse void index areas too

If you lower fillfactor on the table and not on the index,
an index scan will not be affected at all.

Yours,
Laurenz Albe

#25

Il 03/02/2010 16.59, Laurenz Albe ha scritto:

Quote:

Anselmo Canfora wrote:
I would expect slight decreases in INSERT speed (more empty space
is written out to disk) *and* (initially) in the speed of table scans
(more empty space is read in from disk).

I believe the decrease in index scans should be persistent, because one has to traverse void index areas too

If you lower fillfactor on the table and not on the index,
an index scan will not be affected at all.

combination of table and index fillfator might be interesting, will
check it out!

#26

On 2010-01-27, Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:

Quote:

psql (8.4.2, server 8.3.9)
WARNING: psql version 8.4, server version 8.3.
Some psql features might not work.
Type "help" for help.

news_archive=# set search_path=news,public;
SET
Time: 30.157 ms
news_archive=# select count(*) from news_segments;
count
----------
16192541
(1 row)

Time: 2173193.882 ms
news_archive=#

It seems like you have some other issues here:

jura=# select count(*) from transactions;
count
----------
21113819
(1 row)

Time: 31164.641 ms
jura=# select count(*) from transactions;
count
----------
21113863
(1 row)

Time: 8652.074 ms
jura=# select count(*) from transactions;
count
----------
21113867
(1 row)

Time: 5454.249 ms
jura=# select count(*) from transactions;
count
----------
21113870
(1 row)

Time: 5066.897 ms
jura=#

The machine is a bit 'slower' than the one you have.

Mike
--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."

Mario Splivalo
majk (AT) fly (DOT) srk.fer.hr

#27

On Thu, 18 Feb 2010 16:49:12 +0000, Mario Splivalo wrote:

Quote:

On 2010-01-27, Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:
psql (8.4.2, server 8.3.9)
WARNING: psql version 8.4, server version 8.3.
Some psql features might not work.
Type "help" for help.

news_archive=# set search_path=news,public; SET
Time: 30.157 ms
news_archive=# select count(*) from news_segments;
count
----------
16192541
(1 row)

Time: 2173193.882 ms
news_archive=#

It seems like you have some other issues here:

jura=# select count(*) from transactions;
count
----------

Essentially, you are right. I do have other problems, the primary of
which is the size of the table. The table contains text column with a GIN
index on top of it. The other problem is that I have a "toy" platform
with a slow local disk, not a premium SAN which can deal with I/O
requests much faster than a 3 years old local drive which is,
coincidentally, the only drive at the machine. I did, however, solve the
problems, to a degree. Creating the primary keys and vacuuming helped a
lot. Also, I increased the file system read-ahead to 2048 (blockdev
command) and increased the shared buffers. I've cut the time down to a
third.

--
http://mgogala.freehostia.com

#28

On 2010-02-18, Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:

Quote:

It seems like you have some other issues here:

jura=# select count(*) from transactions;
count
----------

Essentially, you are right. I do have other problems, the primary of
which is the size of the table. The table contains text column with a GIN
index on top of it. The other problem is that I have a "toy" platform
with a slow local disk, not a premium SAN which can deal with I/O
requests much faster than a 3 years old local drive which is,
coincidentally, the only drive at the machine. I did, however, solve the
problems, to a degree. Creating the primary keys and vacuuming helped a
lot. Also, I increased the file system read-ahead to 2048 (blockdev
command) and increased the shared buffers. I've cut the time down to a
third.

Glad to hear it. I neglected to mention that I'm using pg8.4 on xfs. My
table has several uuid columns, one is PK, two other have unique
constraints. The size of the table on the disk is around 4GB (including
indices). Table is not partitioned in any way. The machine has 4 GB of RAM
and my shared_buffers are set to 512 MB. Free tells me that i'm using almost
3 GB for disk cache.
I never issue DELETE on that table, UPDATES are seldom, and I rely on
autovaccum - it's working quite well in 8.4.

Mike

--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."

Mario Splivalo
majk (AT) fly (DOT) srk.fer.hr

#29

On Thu, 18 Feb 2010 18:48:03 +0000, Mario Splivalo wrote:

Quote:

I never issue DELETE on that table, UPDATES are seldom, and I rely on
autovaccum - it's working quite well in 8.4.

Unfortunately, I cannot upgrade to 8.4. There is no pljava for that
version and my developers say that they cannot live without Java. Such
situation doesn't make me happy but that's the business decision, CIO has
sanctioned it and there is no further debate.

--
http://mgogala.freehostia.com

#30

On 2010-02-18, Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:

Quote:

On Thu, 18 Feb 2010 18:48:03 +0000, Mario Splivalo wrote:

I never issue DELETE on that table, UPDATES are seldom, and I rely on
autovaccum - it's working quite well in 8.4.

Unfortunately, I cannot upgrade to 8.4. There is no pljava for that
version and my developers say that they cannot live without Java. Such
situation doesn't make me happy but that's the business decision, CIO has
sanctioned it and there is no further debate.

--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."

Mario Splivalo
majk (AT) fly (DOT) srk.fer.hr