effective_cache_size = 100 # typically 8KB each
I have this setting on postgresql 7.4.8 on FreeBSD with 4 GB RAM:
effective_cache_size = 27462
So eventhough your machine runs Debian and you have four times as much
RAM as mine your effective_cache_size is 36 times larger. You could
try
I have a SUSE 9 box that is running Postgres 8.0.1 compiled from source.
Over time, I see the memory usage of the box go way way up (it's got
8GBs in it and by the end of the day, it'll be all used up) with what
looks like cached inodes relating to the extreme IO generated by
I was wondering
I have a postgresql 7.4.8-server with 4 GB ram.
#effective_cache_size = 1000# typically 8KB each
This is computed by sysctl -n vfs.hibufspace / 8192 (on FreeBSD). So I
changed it to:
effective_cache_size = 27462# typically 8KB each
Apparently this formula is no longer
Apparently this formula is no longer relevant on the FreeBSD systems as
it can cache up to almost all the available RAM. With 4GB of RAM, one
could specify most of the RAM as being available for caching, assuming
that nothing but PostgreSQL runs on the server -- certainly 1/2 the RAM
We are running some performance tests in which we are attempting to
insert about 100,000,000 rows in a database at a sustained rate. About
50M rows in, our performance drops dramatically.
This test is with data that we believe to be close to what we will
encounter in production. However in
Does anyone have recommendations for hardware and/or OS to work with around
5TB datasets?
Hardware-wise I'd say dual core opterons. One dual-core-opteron
performs better than two single-core at the same speed. Tyan makes
some boards that have four sockets, thereby giving you 8 cpu's (if you
at 5TB data, i'd vote that the application is disk I/O bound, and the
difference in CPU speed at the level of dual opteron vs. dual-core
opteron is not gonna be noticed.
to maximize disk, try getting a dedicated high-end disk system like
nstor or netapp file servers hooked up to fiber
I forgot to give our non default postgresql.conf parameters:
shared_buffers = 28800
sort_mem = 32768
vacuum_mem = 32768
max_fsm_pages = 35
max_fsm_relations = 2000
checkpoint_segments = 16
effective_cache_size = 27
random_page_cost = 2
Isn't sort_mem quite high? Remember that
Typical query
SELECT n.name
FROM node n
WHERE n.name
LIKE '56x%'
AND n.type='H'
AND n.usage='TEST'
AND n.node_id
NOT IN
(select n.node_id
FROM job_log j
INNER JOIN node n
ON j.node_id = n.node_id
WHERE n.name
LIKE '56x%'
AND n.type='H'
AND n.usage='TEST'
AND
Hi.
Has anybody tried the new Sun cool-thread servers t1000/t2000 from
Sun? I'd love to see benchmarks with Solaris 10 and pg 8.1.
regards
Claus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
Here is my current configuration:
Dual Xeon 3.06Ghz 4GB RAM
Adaptec 2200S 48MB cache 4 disks configured in RAID5
FreeBSD 4.11 w/kernel options:
options SHMMAXPGS=65536
options SEMMNI=256
options SEMMNS=512
options SEMUME=256
options SEMMNU=256
4. Are there any other settings in the conf file I could try to tweak?
One more thing :-)
I stumbled over this setting, this made the db (PG 7.4.9) make use of
the index rather than doing a sequential scan and it reduced a query
from several minutes to some 20 seconds.
random_page_cost = 2
We're planning new server or two for PostgreSQL and I'm wondering Intel
Core 2 (Woodcrest for servers?) or Opteron is faster for PostgreSQL now?
When I look through hardware sites Core 2 wins. But I believe those tests
mostly are being done in 32 bits. Does the picture change in 64 bits?
I have a simple query which uses 32ms on 7.4.14 and 1015ms on 8.2.0.
I guess 7.4.14 creates a better execution plan than 8.2.0 for this query but
i don't know how to get it to select a better one.
Explain analyse output will be found near the end of the e-mail.
Explain analyze is run several
I am about to pull the trigger on a new machine after analyzing some
discussions I posted here last year. I've been trying to spec out a reliable
and powerfull enough machine where I won't have to replace it for some time.
Currently I've been using a dual Xeon 3.06ghz with 4GB of ram and
Approx. 200 reqest a sec. should be a problem unless the queries are heavy.
Thanks Claus thats good news!
I'm having a reputable vendor build the box and test it for me before
delivering. The bottom line of your message, did you mean 'should be not a
problem'? I wonder what the main reason
I recall a reference on the list indicating that newer Xeon processors
don't suffer from the context switching problem reported last year.
In searching the archives, I can't find any specific info indentifying
which Xeon processors don't have this problem.
Anyone point me to a reference?
We
I'm curious, what problem does the disclaimer cause?
I wrote the following TOS for my personal system:
https://www.potentialtech.com/cms/node/9
Excerpt of the relevant part:
I have no idea if that's legally binding or not, but I've talked to a few
associates who have some experience in
Hi all,
take a look at those plans:
Try changing random_page_cost from the default 4 to 2 in postgresql.conf:
random_page_cost = 2
The default in postgresql is somewhat conservative. This setting
indicates for postgresql how fast your disks are, the lower the
faster.
Could this setting be
I am about to order a new server for my Postgres cluster. I will
probably get a Dual Xeon Quad Core instead of my current Dual Xeon.
Which OS would you recommend to optimize Postgres behaviour (i/o
access, multithreading, etc) ?
I am hesitating between Fedora Core 6, CentOS and Debian. Can
In #postgresql on freenode, somebody ever mentioned that ZFS from Solaris
helps a lot to the performance of pgsql, so dose anyone have information
about that?
the filesystem you use will affect the performance of postgres
significantly. I've heard a lot of claims for ZFS, unfortunantly many
At our institute we are currently establishing a small GIS working group.
The data storage for vector data should be the central PostGIS system.
Raster data will be held in file system.
Mostly the users are accessing the data base in read only mode. From the
client side there is not much write
The iostat -c says about 8% of time waiting for IO. I'm afraid this
is due to locks between concurrent queries, is there anyway to have more
info about?
I do believe that if you told what OS you're running, what pg-version
you're running, what type of sql-statements you perform the list
I've recently run into problems with my kernel complaining that I ran
out of memory, thus killing off postgres and bringing my app to a
grinding halt.
I'm on a 32-bit architecture with 16GB of RAM, under Gentoo Linux.
Naturally, I have to set my shmmax to 2GB because the kernel can't
Get yourself the ability to benchmark your application. This is
invaluable^W a requirement for any kind of performance tuning.
I'm pretty happy with the performance of the database at this stage.
Correct me if I'm wrong, but AFAIK a load of 3.5 on a quad is not
overloading it. It also
Hi.
We are using a HP DL 380 G5 with 4 sas-disks at 10K rpm. The
controller is a built in ciss-controller with 256 MB battery-backed
cache. It is partitioned as raid 1+0.
Our queries are mainly selects.
I will get four 72 GB sas-disks at 15K rpm. Reading the archives
suggest raid 1+0 for
All of our existing servers are from Dell, but I want to look at some
other options as well. We are currently looking at rack boxes with 8
internal SAS discs. Two mirrored for OS, Two mirrored for WAL and 4 in
raid 10 for the base.
Here are our current alternatives:
1) Dell 2900 (5U)
8 *
Apart from the disks, you might also investigate using Opterons instead
of Xeons. there appears to be some significant dent in performance
between Opteron and Xeon. Xeons appear to spend more time in passing
around ownership of memory cache lines in case of a spinlock.
It's not yet clear
Does anyone have any ideas what my bottle neck might be and what I can do
about it?
Your bottleneck is that you are using a very old version of PostgreSQL. Try
8.2 or (if you can) the 8.3 beta series -- it scales a _lot_ better in this
kind of situation.
You won't know until you've seen
I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
postgres 7.4.3. This has been recompiled on the server for 64 stored
procedure parameters, (I assume this makes postgres 64 bit but are not
sure). When the server gets under load from database connections
executing
Thanks for the information Claus, Why would reducing the effective cache
size help the processor usage? It seems that there is plenty of resources
on the box although I can see that 10MB of sort space could mount up if we
had 500 connections but at the moment we do not have anything like
Which scheduler is recommended for a box that is dedicated to running
postgres?
I've asked google and found no answers.
Is it the OS itself?
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.
Shakespeare
I missed the initial post in this thread, but I haven't seen any 15K rpm
2.5 drives, so if you compare 10K rpm 2.5 drives with 15K rpm 3.5
drives you will see differences (depending on your workload and controller
cache)
I have some 15K rpm 2.5 sas-drives from HP. Other vendors have them as
There are some results here that show PostgreSQL is slower in some cases
than Monet and MySQL. Of course these results were published immediately
prior to 8.2 being released, plus run out-of-the-box, so without even
basic performance tuning.
Would anybody like to repeat these tests with the
The following query takes about 4s to run in a 16GB ram server. Any ideas
why it doesn´t use index for the primary keys in the join conditions?
Maby random_page_cost is set too high? What version are you using?
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentlest
Without knowing what a lakhs record is,
I had the same question... and Wikipedia gave me the answer : it is an
Indian word meaning 10^5, often used in indian english.
Thank you (both OP and this post) for enlightening us with this word.
--
regards
Claus
When lenity and cruelty play for
I need to install a new server for postgresql 8.3. It will run two
databases, web server and some background programs. We already have a
server but it is becoming slow and we would like to have something that
is faster. It is a cost sensitive application, and I would like to get
your
1) hardware
2) rewriting my queries and table structures
3) using more predefined queries
4) tweek parameters in the db conf files
Of these points:
1) is nothing I can do about right now, but in the future perhaps.
2) will be quite hard right now since there is more code than time.
Can anyone who have started using 8.3.1 list out the pros and cons.
I upgraded to 8.3.1 yesterday from 8.3.0. I've used 8.3.0 since it was
released and it's working fine. I upgraded from 7.4 (dump/restore) and
it was working out of the box. We have somewhat simple sql-queries so
there was no
We want to migrate from postgres 8.1.3 to postgres 8.3.1.
Can anybody list out the installation steps to be followed for migration.
Do we require to take care of something specially.
Perform a pg_dump, do a restore and validate your sql-queries on a test-server.
--
regards
Claus
When lenity
I have a table with 9,961,914 rows in it (see the describe of
bigtab_stats_fact_tmp14 below)
I also have a table with 7,785 rows in it (see the describe of xsegment_dim
below)
I'm running the join shown below and it takes 10 hours and eventually runs
out of disk space on a 1.4TB file
We have a database with lots of small simultaneous writes and reads
(millions every day) and are looking at buying a good hardware for this.
What are your suggestions. What we are currently looking at is.
Dual Quad Core Intel
8 - 12 GB RAM
10 disks total.
4 x 146 GB SAS disk in RAID 1+0
If you have a good RAID controller with BBU cache, then there's no point
splitting the discs into two sets. You're only creating an opportunity to
under-utilise the system. I'd get ten identical discs and put them in a
single array, probably RAID 10.
OK, thats good to know. Really want to
After setting log_statement='all' at postgres.conf,
then i'm rebooting OS [freeBSD or CentOS],
i can't find where log file created from log_statement='all' located...
FYI, location of postgres.conf at /var/lib/pgsql/data/postgres.conf
many thanks..
I added the following to FreeBSD:
I have taken over the maintenance of a server farm , recently. 2 webserver
on db server. They are quite powerful 2 processor xeon w/ 6Gig of ram .
Couple of days ago we had a serious performance hit and the db server (pg.
v7.4) was overloaded w/ something in a way that operating system was
Considering a quad core server processor, 4 GBs of RAM memory, disk Sata
2.
What is the recommended setting for the parameters:
max_connections:70
Depends on how many clients that access the database.
shared_buffers?
I have mine at 512 MB but I will lower it and see how it affects
Should I use gjournal on FreeBSD 7? Or just soft updates?
Here is my opinion: I suspect that gjournal would be much slower than
soft updates. Also gjournal is relatively new code, not very well
tested. But gjournal is better when the system crashes. Although I have
heard that sometimes
I am doing a performance comparison between running Jena with MySQL and
Postgres. I used the 8.3-community version of Postgres and MySQL 5.0.67. I
have run several queries to both MySQL and Postgres and all of them took
similar amount of time to execute except one. For the following query to a
Query:
select A0.Subj, A2.Obj From jena_g1t1_stmt A0, jena_g1t1_stmt A1,
jena_g1t1_stmt A2 Where
A0.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/isPartOf' AND
A0.Obj='Uv::http://www.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1'
AND A0.GraphID=1 AND A0.Subj=A1.Subj
The execution time has not improved. I am going to increase the
shared_buffers now keeping the work_mem same.
Have you performed a vacuum analyze?
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentler gamester is the soonest winner.
Shakespeare
--
Sent via
max_fsm_pages = 280
max_fsm_relations = 16
What does the last couple of lines from a 'vacuum analyze verbose'
say? I have max_fsm_pages = 400 and max_fsm_relations = 1500.
You can also try to lower random_page_cost to a lower value like 1.2
but I doubt this will help in your
I have a transaction running at the database for around 20 hours .. still
isn't done. But during the last hours it has come to the point where it
really hurts performance of other queries.
Given pg_stat_activity output there seems to be no locks interfering but
the overall cpu-usage of all
I have a big performance problem in my SQL select query:
select * from event where user_id in
However, I have certainly seen some inefficiencies with Linux and large use
of shared memory -- and I wouldn't be surprised if these problems don't
exist on FreeBSD or OpenSolaris.
This came on the freebsd-performance-list a few days ago.
Claus Guttesen kome...@gmail.com wrote:
http://docs.freebsd.org/cgi/getmsg.cgi?fetch=13001+0+current/freebsd-performance
Not being particularly passionate about any OS, I've been intrigued by
the FreeBSD benchmarks. However, management is reluctant to use boxes
which don't have heavily
55 matches
Mail list logo
