I run a Perl/CGI driven website that makes extensive use of PostgreSQL
(7.4.3) for everything from user information to formatting and display
of specific sections of the site. The server itself, is a dual
processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2 x 120GB hard drives
mirrored for redundancy running under FreeBSD 5.2.1 (AMD64).

Recently loads on the site have increased during peak hours to the point
of showing considerable loss in performance. This can be observed
when connections move from the 120 concurrent connections to PostgreSQL
to roughly 175 or more. Essentially, the machine seems to struggle
to keep up with continual requests and slows down respectively as
resources are tied down.

Code changes have been made to the scripts to essentially back off in
high load working environments which have worked to an extent.
However, as loads continue to increase the database itself is not taking
well to the increased traffic taking place.

Having taken a look at 'Tuning PostgreSQL for Performance'
(http://www.varlena.com/GeneralBits/Tidbits/perf.html) using it as best
I could in order to set my settings. However, even with statistics
disabled and ever setting tweaked things still consider to deteriorate.

Is there anything anyone can recommend in order to give the system a
necessary speed boost? It would seem to me that a modest dataset of
roughly a Gig combined with that type of hardware should be able to
handle substantially more load then what it is. Can anyone provide me
with clues as where to pursue? Would disabling 'fsync' provide more
performance if I choose that information may be lost in case of a crash?

If anyone needs access to logs, settings et cetera. Please ask, I
simply wish to test the waters first on what is needed. Thanks!

Martin Foster
martin (AT) ethereal-realms (DOT) org

On Tue, 2004-08-03 at 12:05, Martin Foster wrote:
Are you properly vacuuming the database and have you set your fsm
settings high enough? You may need to do a vacuum full first to reclaim
lost space.

Post your postgresql.conf settings if you can.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Scott Marlowe wrote:

Actually, I have it do a VACUUM FULL ANALYZE nightly now. On a weekly
basis, it would take about five minutes to run the maintenance process,
now it takes less then a minute.

I was also using the pg_autovacuum daemon with little to no performance
improvements overall when loads were light, but heavy penalties during
high load averages. This has since been disabled in favour of the
existing script maintenance that I put into place already.

Martin Foster
Creator/Designer Ethereal Realms
martin (AT) ethereal-realms (DOT) org



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Tue, Aug 03, 2004 at 06:05:23PM +0000, Martin Foster wrote:
Martin,

Maybe a connection pool middleware like pgpool could help. This would
allow a lower number of backends to serve the same frontends more
effectively and reduce the connection startup costs.

--Ken

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Martin Foster wrote:
There are several aspects which are performance-related.

First, try to find out exactly where the bottleneck is.
Are you sure that it's the database server backends?
(I.e. not the web server, not the perl CGIs or anything
else? I assume you use mod_perl, do you?)

Are you running out of processor time, or out of RAM so
the machine starts swapping/paging, or is there an I/O
bandwidth bottleneck, or maybe it's even the network?
Commands like "top", "vmstat", "iostat", "netstat" etc.
will help finding out. If "top" displays nearly 100%
"user CPU state", then the processor time is your problem,
otherwise it's more likely that I/O is the bottleneck.

As long as you have enough RAM available, yu can try
increasing FSM values etc. in your postgresql.conf (you
might need to increase the kernel limits on SysV shared
memory, too).

Disabling fsync, as you suggested, will improve performance
somewhat (especially if you have a lot of insert and update
commands), at the cost of increased risk of inconsistencies
and data loss in the case of a crash. If you're willing to
go that far, you might as well mount your data filesystem
async (or with soft-updates), and with the "noatime" flag.

Second, there are several ways to tune FreeBSD's settings
for your particular application. I suggest that you study
the tuning(7) manual page. It covers a lot of topics,
raning from optimized newfs filesystem parameters to things
like network buffers, VFS settings and VM settings. It
also contains pointers to several other related manual
pages.

Third, there might be room for optimizing your database
queries. Use the explain analyze command to find out which
query plans are choosen for your select statements. Maybe
there are ways you can improve on them, e.g. by creating
an appropriate index, by re-formulating the queries in a
different way, or by adapting your database design in a way
that suits your queries better and allows for improved
performance. (If in doubt, you can post your queries in
this list, and I'm sure someone will have good advice for
you.)

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"To this day, many C programmers believe that 'strong typing'
just means pounding extra hard on the keyboard."
-- Peter van der Linden

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

I have tried to COPY the contents of the .csv file into my database
called TEST_DB, but I'm still doing something incorrectly. Currently,
database TEST_DB has one table called "matters".

In my attempts listed below, I specified the name of each column/field
in my database and specified the delimiter used in the .csv file, but
still no go:

TEST_DB-# copy matters (client_1_lastname, client_1_firstname,
client_1_address, client_1_phone, client_2_lastname,
client_2_firstname, client_2_address, client_2_phone, carrier_name,
claim_rep, claim_num, responsible, supervisory, second, client_num,
matter_num, file_name, adverse_1_lastname, adverse_1_firstname,
adverse_2_lastname, adverse_2_firstname, adverse_3_lastname,
adverse_3_firstname, other_party_lastname, other_party_firstname,
case_type, billing_type, date_of_loss, judicial_district, date_open,
date_close) from '/home/floog/TEST_DB.csv' DELIMITER ',';

ERROR: syntax error at or near "client_1_lastname" at character 2

TEST_DB=# copy matters client_1_lastname, client_1_firstname,
client_1_address, client_1_phone, client_2_lastname,
client_2_firstname, client_2_address, client_2_phone, carrier_name,
claim_rep, claim_num, responsible, supervisory, second, client_num,
matter_num, file_name, adverse_1_lastname, adverse_1_firstname,
adverse_2_lastname, adverse_2_firstname, adverse_3_lastname,
adverse_3_firstname, other_party_lastname, other_party_firstname,
case_type, billing_type, date_of_loss, judicial_district, date_open,
date_close from '/home/floog/TEST_DB.csv' DELIMITER ',';

ERROR: syntax error at or near "client_1_lastname" at character 14

What did I do incorrectly?
Thank you for your time and assistance.

Mike



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

I have tried again to copy using a suggestion sent to me from another
new user on the list:

Attempt 1 --

TEST_DB=# copy matters from '/home/floog/TEST_DB.csv' delimiter ',';
ERROR: could not open file "/home/floog/TEST_DB.csv" for reading:
Permission denied

Okay, so I chmod the file to be read/write by anyone -- (chmod 0777
/home/floog/TEST_DB.csv)

TEST_DB=# copy matters from '/home/floog/TEST_DB.csv' delimiter ',';
ERROR: could not open file "/home/floog/TEST_DB.csv" for reading:
Permission denied

Still no go so I copied the file into my PGDATA directory -
/usr/local/postgres

TEST_DB=# copy matters from '/usr/local/postgres/TEST_DB.csv' delimiter
',';
ERROR: invalid input syntax for integer: ""CFW""
CONTEXT: COPY matters, line 1, column matter_num: ""CFW""
TEST_DB=# select * from matters;
client_1_lastname | client_1_firstname | client_1_address |
client_1_phone | client_2_lastname | client_2_firstname |
client_2_address | client_2_phone | carrier_name | claim_rep |
claim_num | responsible | supervisory | second | client_num |
matter_num | file_name | adverse_1_lastname | adverse_1_firstname |
adverse_2_lastname | adverse_2_firstname | adverse_3_lastname |
adverse_3_firstname | other_party_lastname | other_party_firstname |
case_type | billing_type | date_of_loss | judicial_district | date_open
+-------------------+--------------------+------------------+--------------
--+--------------+-----------+-----------+-------------+-------------+-
-------+------------+------------+-----------+--------------------+----
-----------------+--------------------+---------------------+---------
-----------+---------------------+----------------------+------------
-----------+-----------+--------------+--------------+---------------
----+-----------+------------
(0 rows)

TEST_DB=#

I understand the first error about the "CFW" being in a field designated
in an integer format. That's okay. CFW are the initials of a person
at my office and that's just a data entry error -- wrong data in the
wrong column.

But I don't understand why none of the data from the .csv file was
copied to the database. SELECT * FROM matters - results in nothing but
a line of hyphens.

Yikes, I can't get off the ground. I can't imagine manually inserting 2
megs. of stuff one entry at a time. Please help if you can.

I appreciate your time and patience.

Regards,

Mike



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Wed, 2004-08-04 at 16:29, michael (AT) floog (DOT) net wrote:
You also have to make the whole path accessible; presumably the
directory /home/floog is not accessible to the postgres user. (The
backend runs with the permission of the user who starts the postmaster,
which is commonly postgres.)

COPY happens in a transaction; either all of the data goes in or none of
it does.

--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"And not only so, but we glory in tribulations also;
knowing that tribulation worketh patience; And
patience, experience; and experience, hope."
Romans 5:3,4


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Wed, 2004-08-04 at 15:40, michael (AT) floog (DOT) net wrote:
You had a previous line without a terminating semi-colon. Notice that
the prompt (TEST_DB-# rather than TEST_DB=#) indicates that this is a
continuation line.

--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"And not only so, but we glory in tribulations also;
knowing that tribulation worketh patience; And
patience, experience; and experience, hope."
Romans 5:3,4


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hi Oliver,

This time the copy command appeared to work, except for the "CFW" entry
in the integer-formatted column, but then trying to view the data shows
the long hyphenated line again and no readable data.

What do you think is causing this?
Thank you very much hanging in there with me on this issue.

Mike

TEST_DB=# copy matters (client_1_lastname, client_1_firstname,

<<<snip>>

TEST_DB(# case_type, billing_type, date_of_loss, judicial_district,
date_open,
TEST_DB(# date_close) from '/usr/local/postgres/TEST_DB.csv' DELIMITER
',';

ERROR: invalid input syntax for integer: ""CFW""
CONTEXT: COPY matters, line 1, column matter_num: ""CFW""

TEST_DB=# select * from matters;
client_1_lastname | client_1_firstname | client_1_address |
client_1_phone | client_2_lastname | client_2_firstname |

<<<snip>>>

-------------------+--------------------+------------------+
----------------+-------------------+--------------------+--
----------------+----------------+--------------+-----------+
-----------+-------------+-------------+--------+------------
+------------+-----------+--------------------+--------------
-------+--------------------+---------------------+----------
----------+---------------------+----------------------+-----
------------------+-----------+--------------+--------------+
-------------------+-----------+------------
(0 rows)

TEST_DB=#


Quoting Oliver Elphick <olly (AT) lfix (DOT) co.uk>:




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match