[GENERAL] zLinux Load Testing Experience
I'm currently working on a project porting an application from RedHat
Linux on Intel onto IBM zLinux. Our application requires PostgreSQL at
version 9.n, so the PostgreSQL binaries have been built using the
standard build tools from source. Everything appears run correctly.
However as part of performance testing, our IBM and Linux SysProgs have
been "poking around" using strace and have reported the following (which
they think is an error condition) when hooking up to the postmaster
processes:-
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 200) = 0 (Timeout)
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 1) = 0 (Timeout)
... repeated many times
From researching the archives, I "believe" the above to be "as
designed" and simply indicates the Postmaster is attempting to read data
from an IP socket which is timing out. Could I ask :-
1. Is this "normal" ?
2. if abnormal, any pointers as to where to start investigating
The reason they latched onto the postmaster process was due to a
perceived high CPU utilisation. For info, we are load testing with 100
connections being accessed from an IBm WebSphere hosted EJB based
application.
Many thanks,
Andrew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] zLinux Load Testing Experience
On 30/04/13 20:46, Merlin Moncure wrote:
On Tue, Apr 30, 2013 at 12:26 PM, Jeff Janes wrote:
On Tue, Apr 30, 2013 at 8:28 AM, Andrew Hastie wrote:
I'm currently working on a project porting an application from RedHat
Linux on Intel onto IBM zLinux. Our application requires PostgreSQL at
version 9.n, so the PostgreSQL binaries have been built using the standard
build tools from source. Everything appears run correctly. However as part
of performance testing, our IBM and Linux SysProgs have been "poking around"
using strace and have reported the following (which they think is an error
condition) when hooking up to the postmaster processes:-
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 200) = 0 (Timeout)
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 1) = 0
(Timeout)
... repeated many times
That does not look like the postmaster process. It looks like probably the
background writer process.
It is normal, and doesn't explain high CPU utilization.
yeah: we're probably a couple of steps in front of deep system
profiling. Helpful things to provide to help diagnose would be:
*) 'explain analyze' of the queries that are eating cpu
*) more details about the hardware -- how many cpu, etc.
*) better definition of 'perceived high CPU utilisation'
*) some correlating performance tests, expecially cpu bound pgbench
tests (pgbench -S)
merlin
I'm not sure how much experience the community has on tuning PostgreSQL
running on RedHat which in turn is hosted on an IBM mainframe under VM
(using zLinux). So I'm happy to start posting further details and
benchmark results and see where we go. Should I be moving this thread
over into the pg-performance list, or is pg-general the right place?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] zLinux Load Testing Experience
On 01/05/13 15:34, Merlin Moncure wrote:
On Wed, May 1, 2013 at 8:01 AM, Andrew Hastie wrote:
On 30/04/13 20:46, Merlin Moncure wrote:
On Tue, Apr 30, 2013 at 12:26 PM, Jeff Janes wrote:
On Tue, Apr 30, 2013 at 8:28 AM, Andrew Hastie
wrote:
I'm currently working on a project porting an application from RedHat
Linux on Intel onto IBM zLinux. Our application requires PostgreSQL at
version 9.n, so the PostgreSQL binaries have been built using the
standard
build tools from source. Everything appears run correctly. However as
part
of performance testing, our IBM and Linux SysProgs have been "poking
around"
using strace and have reported the following (which they think is an
error
condition) when hooking up to the postmaster processes:-
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily
unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 200) = 0
(Timeout)
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily
unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 1) = 0
(Timeout)
... repeated many times
That does not look like the postmaster process. It looks like probably
the
background writer process.
It is normal, and doesn't explain high CPU utilization.
yeah: we're probably a couple of steps in front of deep system
profiling. Helpful things to provide to help diagnose would be:
*) 'explain analyze' of the queries that are eating cpu
*) more details about the hardware -- how many cpu, etc.
*) better definition of 'perceived high CPU utilisation'
*) some correlating performance tests, expecially cpu bound pgbench
tests (pgbench -S)
merlin
I'm not sure how much experience the community has on tuning PostgreSQL
running on RedHat which in turn is hosted on an IBM mainframe under VM
(using zLinux). So I'm happy to start posting further details and benchmark
results and see where we go. Should I be moving this thread over into the
pg-performance list, or is pg-general the right place?
certainly performance. and yes, zLinux is less well traveled. Did
you compile postgres from source? Did you confirm that there is a
native spinlocks implementation and it is being used?
merlin
Did you compile postgres from source? - Yes (I need PG v9.n as v8.n
shipped with RedHat Ent6 does not have several v9 specific features we
need).
Did you confirm that there is a native spinlocks implementation and it is being
used? - I believe so as no errors or warnings logged during the build. Is
there a simple way to check whether spin-locks are running native?
I've started looking at several articles covering pgbench and running some
initial tests, so I plan to start a new thread on pg-performance in the next
day or so.
Thanks for the advice so far - Appreciated :-)
Andrew
Re: [GENERAL] zLinux Load Testing Experience
On 01/05/13 19:21, Merlin Moncure wrote:
On Wed, May 1, 2013 at 11:34 AM, Andrew Hastie wrote:
On 01/05/13 15:34, Merlin Moncure wrote:
On Wed, May 1, 2013 at 8:01 AM, Andrew Hastie wrote:
On 30/04/13 20:46, Merlin Moncure wrote:
On Tue, Apr 30, 2013 at 12:26 PM, Jeff Janes wrote:
On Tue, Apr 30, 2013 at 8:28 AM, Andrew Hastie
wrote:
I'm currently working on a project porting an application from RedHat
Linux on Intel onto IBM zLinux. Our application requires PostgreSQL at
version 9.n, so the PostgreSQL binaries have been built using the
standard
build tools from source. Everything appears run correctly. However as
part
of performance testing, our IBM and Linux SysProgs have been "poking
around"
using strace and have reported the following (which they think is an
error
condition) when hooking up to the postmaster processes:-
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily
unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 200) = 0
(Timeout)
read(3, 0x3875ee0, 16) = -1 EAGAIN (Resource temporarily
unavailable)
poll([{fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 2, 1) = 0
(Timeout)
... repeated many times
That does not look like the postmaster process. It looks like probably
the
background writer process.
It is normal, and doesn't explain high CPU utilization.
yeah: we're probably a couple of steps in front of deep system
profiling. Helpful things to provide to help diagnose would be:
*) 'explain analyze' of the queries that are eating cpu
*) more details about the hardware -- how many cpu, etc.
*) better definition of 'perceived high CPU utilisation'
*) some correlating performance tests, expecially cpu bound pgbench
tests (pgbench -S)
merlin
I'm not sure how much experience the community has on tuning PostgreSQL
running on RedHat which in turn is hosted on an IBM mainframe under VM
(using zLinux). So I'm happy to start posting further details and benchmark
results and see where we go. Should I be moving this thread over into the
pg-performance list, or is pg-general the right place?
certainly performance. and yes, zLinux is less well traveled. Did
you compile postgres from source? Did you confirm that there is a
native spinlocks implementation and it is being used?
merlin
Did you compile postgres from source? - Yes (I need PG v9.n as v8.n shipped
with RedHat Ent6 does not have several v9 specific features we need).
Did you confirm that there is a native spinlocks implementation and it is
being used? - I believe so as no errors or warnings logged during the build.
Is there a simple way to check whether spin-locks are running native?
I've started looking at several articles covering pgbench and running some
initial tests, so I plan to start a new thread on pg-performance in the next
day or so.
Thanks for the advice so far - Appreciated :-)
I can't remember off the top of my head if configure forces you to
specifically unset spinlocks to get through a build on a non-hardware
spinlock platform. Point being: the interesting stuff happens during
configure, not build.
Check the contents of src/include/pg_config.h and look for this line:
#define HAVE_SPINLOCKS 1
to see if you have hardware spinlocks.
merlin
Confirm that #define HAVE_SPINLOCKS 1 is present and correct.
Will move any performance related issues I find onto the pg-performance
list.
Many thanks for all the help and advice so far :-)
Andrew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database connection Pooling using JNDI
Hi Sumit, I think you need to refer to the documentation for the JDBC driver and not the actual PostgresSQL database server documentation. See here: http://jdbc.postgresql.org/documentation/92/jndi.html#ds-jndi Hope this helps. Andrew On 17/02/14 09:27, Sumit Sureka wrote: Hi, I am planning to create my Application to use the database connection via connection pool which i registered to the JNDI service. I found one link that speaks about it: http://www.postgresql.org/docs/7.4/static/jdbc-datasource.html But the classes mentioned in the above link is not available in the latest Postgresql jdbc driver. Can you please point to a place where i can get all the information related the subject mentioned above. Thanks, Sumit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/pgSQL - Help or advice please on using unbound cursors
Hi all, Apologies if the answer to my question is "obvious", but I'm fairly new to writing functions in PG. I think my problem is has a simple solution, but I'm damned if I can find it :-/ (Postgres v9.1.1 on Linux 64-bit) I'm trying to write a function which will :- 1. Take 3 input params; a catalog, schema and name for a table (to uniquely identify the target table) 2. Take further input params indicating the ORDER by clauses when reading the table (see step 4) 3. Identify and drop the primary key from the table 4. Create a cursor to scan the table in the required sequence 5. UPDATE the record currently referenced by the cursor to set a new primary key. 6. Close the cursor 7. Restore the primary key I'm stuck on step 5 when looping around the records returned from the cursor. Reading the doco (from both PG and Oracle), I believe I can only use an unbound cursor when the SELECT statement is built dynamically via the function, so using the FOR/NEXT construct is not an option as that only works with bound cursors. The problem I have is that I cannot for the life of me work out how I check for dropping off the end of the table when I cursor down it. Here's an example code fragment where I'm cursoring down the cursor results and attempting to detect I've dropped of the end :- EXECUTE ''DECLARE cursor1 CURSOR FOR SELECT "ident" FROM '' || tableHN || '' ORDER BY "Name" FOR UPDATE''; LOOP EXECUTE ''FETCH NEXT FROM cursor1 INTO rec''; recCount = recCount + 1; RAISE NOTICE ''Fetched ok %'', recCount; IF FOUND THEN EXECUTE ''UPDATE '' || tableHN || '' SET "%1" = '' || recCount || '' WHERE CURRENT OF cursor1''; ELSE RAISE NOTICE ''Not Found''; EXIT; END IF; END LOOP; I never see the "Not Found" notice, so the "IF FOUND" test never appears to be triggered. Although I can catch this with a BEGIN + EXCEPTION triggered when the UPDATE call occurs after processing the last record, this results in the transaction being rolled back, so I loose the changes. I've also tried using "IF cursor1%notfound" but I get an error which I guess is because the cursor is not a bound cursor. Any advice on the "correct" way to detect end-of-resultset when using a cursor in this way or any other thoughts please. Many thanks Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL - Help or advice please on using unbound cursors
Yep, that fixed it. Many thanks for the pointer. From a performance point of view, I did look at using MOVE rather than FETCH before I call UPDATE as I don't actually need to read the data before applying the update. However in this situation, the ROW_COUNT is always zero and can't be used which I suspect is reasonable. Anyway, thanks for the help. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing on IBM AIX 7.1
Does anyone out there have any experience or feedback on building+installing PG on an AIX7.1 system? I need to do some testing on this platform in a few weeks time, so any hints or tips greatly appreciated. Thanks Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to don't update sequence on rollback of a transaction
Hi Frank, I believe this is by design. See the bottom of the documentation on sequences where it states ;- "*Important:* To avoid blocking concurrent transactions that obtain numbers from the same sequence, a |nextval| operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the |nextval| later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values. |setval| operations are never rolled back, either." http://www.postgresql.org/docs/9.1/static/functions-sequence.html If you really want to reset the sequence, I think you would have to call SELECT SETVAL(.) at the point you request the roll-back. Regards Andrew On 02/08/12 16:08, Frank Lanitz wrote: Hi folks, I did a test with transactions and wondered about an behavior I didn't expected. At http://pastebin.geany.org/bYQNo/raw/ I posted a complete backlog for. To make it short: I created a table with a serial and started a transactions. After this I was inserting values into the table but did a rollback. However. The sequence of the serial filed has been incremented by 1 on each insert (which is fine), but wasn't reset after rollback of transaction. Documentation stats: "If, partway through the transaction, we decide we do not wantto commit (perhaps we just noticed that Alice's balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be canceled." My understanding of all was that it includes sequences. Obviously, I'm wrong... but how to do it right? Cheers, Frank
[GENERAL] PG Installer - Licensing Issues
As I understand it, I am allowed to redistribute Postgres so long as I include the copyright notice plus paragraphs as detailed on http://www.postgresql.org/about/licence/. What I want to confirm is that the one-click installer (which I understand was supplied by EnterpriseDB) can also be redistributed such that I can bundle Postgres with my "product", and also use the non-interactive installer (http://www.enterprisedb.com/resources-community/pginst-guide#interactive) such that I can make the installation as easy as possible for my users. I've scanned the EnterpriseDB site, but I see nothing that confirms or denies my thoughts. Anyone care to comment? Thanks Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG Installer - Licensing Issues
Thanks for your thoughts Craig, the issue with users having an existing PG installation is a definite concern. It sounds like you're recommending using the "ZIP Binaries", at least for MS Win installs, and configuring things manually rather than using the one-click installer. If so, are there any guidelines or samples you know of that I could make use of to help out here? As you say, there doesn't seem to be any formal documentation on how to do this from what I can see. Many thanks, Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade: out of memory
Hi Tom/Matthew, Just to chime in on this thread - I'm currently validating Postgres on AIXv7.1 and confirm that I also see the same error. I can reproduce the error with 9.2.1 and 9.2.0 but unlike Matthew I'm using a built from source build using the IBM xcl compiler rather than gcc. I don't believe this is data limit related as I see the error when dumping database "template1" under user "postgres". Here's the output:- [eg17ph01:ahastie] /ahastie $ pg_dump -v -U postgres template1 > test.psql Password: pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading extensions pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: reading procedural languages pg_dump: reading user-defined aggregate functions pg_dump: out of memory [eg17ph01:ahastie] /ahastie $ xlc -qversion IBM XL C/C++ for AIX, V12.1 (5765-J02, 5725-C72) Version: 12.01..0001 I've tried the requested SQL query which returns zero rows. Is this as expected ? I will try the same with release 9.1.6 to see if we can pinpoint this as a potential AIX only issue or a 9.2.n issue. Also to confirm what Matthew has observed. Regards, Andrew On 28/09/12 16:12, Tom Lane wrote: "Carrington, Matthew (Produban)" writes: Reading symbols from /ukmetmon/data/dataCollection/postgres_9.2.1/bin/pg_dump...(no debugging symbols found)...done. ... hm, not sure why that didn't work, but anyway: (gdb) bt #0 0x00010002e354 in exit_horribly () #1 0x00010003243c in pg_malloc () #2 0x00018f14 in getAggregates () #3 0x00010002fcac in getSchemaData () #4 0x00011330 in main () getAggregates() doesn't do that much. Can we see the results of the query it would have been executing, namely SELECT tableoid, oid, proname AS aggname, pronamespace AS aggnamespace, pronargs, proargtypes, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = proowner) AS rolname, proacl AS aggacl FROM pg_proc p WHERE proisagg AND ( pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog') OR EXISTS(SELECT 1 FROM pg_depend WHERE classid = 'pg_proc'::regclass AND objid = p.oid AND refclassid = 'pg_extension'::regclass AND deptype = 'e')); regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade: out of memory
Tom/Matthew,
I also tried the same macro with the xlc compiler with similar results
in that pg_dump now works as expected :-)
For info here's my build setup:-
./configure CC=xlc LIBS="-lssl -lcrypto -lz -lreadline -lcurses -lld
-lmass -lm" CFLAGS="-qlanglvl=extc89 -D_LINUX_SOURCE_COMPAT"
--with-template=aix --prefix=/home/ahastie/pgbuild
--with-includes=/opt/freeware/include
--with-libraries=/opt/freeware/lib
gmake
gmake check
gmake install
Tom: Is this something we should get added into the AIX Platform
specific notes?
Regards,
Andrew
On 01/10/12 15:50, Carrington, Matthew (Produban) wrote:
Tom,
Following on from that man page extract I tried a build using the suggested
compiler macro (_LINUX_SOURCE_COMPAT) ...
export "CC=/opt/freeware/bin/gcc -maix64"
export OBJECT_MODE=64
export CFLAGS="-D_LINUX_SOURCE_COMPAT -maix64 -g"
export LDFLAGS="-maix64 -Wl,-bbigtoc"
export AR="ar -X64"
export "CC=/opt/freeware/bin/gcc -maix64"
./configure --enable-debug --prefix=/opt/serviceMonitoring/postgres_9.2.1
--disable-thread-safety --enable-cassert
make
cd contrib
make
cd ..
make install
cd contrib
make install
... and tried it out ...
"/opt/serviceMonitoring/postgres_9.2.1/bin/pg_dump" --port 65432 --username
"postgres" --verbose --schema-only --binary-upgrade -f dump.out template1
.. and the full dump as per pg_upgrade ...
"/opt/serviceMonitoring/postgres_9.2.1/bin/pg_dumpall" --port 65432 --username
"postgres" --schema-only --binary-upgrade -f pg_upgrade_dump_all.sql
.. both of which worked without any problems.
Hope that helps.
Matthew
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: 01 October 2012 14:39
To: Carrington, Matthew (Produban)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade: out of memory
"Carrington, Matthew (Produban)" writes:
pg_dump: reading user-defined aggregate functions
Breakpoint 1, exit_horribly (modulename=0x0, fmt=0x10006a590 "out of memory\n")
at dumputils.c:1314
1314dumputils.c: A file or directory in the path name does not exist..
(gdb) bt
#0 exit_horribly (modulename=0x0, fmt=0x10006a590 "out of memory\n") at
dumputils.c:1314
#1 0x00010003247c in pg_malloc (size=0) at dumpmem.c:47
#2 0x00018f54 in getAggregates (fout=0x11000bad0,
numAggs=0x73c)
at pg_dump.c:3614
Oh! Given your previous comment about there not being any user-defined
aggregates, I see what the problem is. AIX must be one of the platforms
where malloc(0) is defined to return NULL rather than a pointer to a
zero-size block. pg_malloc is not coping with that.
A quick fix would be
pg_malloc(size_t size)
{
void *tmp;
tmp = malloc(size);
- if (!tmp)
+ if (!tmp && size)
{
psql_error("out of memory\n");
exit(EXIT_FAILURE);
}
but I'm not sure if that's the best answer overall. Will take it up in
-hackers.
regards, tom lane
Emails aren't always secure, and they may be intercepted or changed
after they've been sent. Produban doesn't accept liability if this
happens. If you think someone may have interfered with this email,
please get in touch with the sender another way. This message and any
documents attached to it do not create or change any contract unless
otherwise specifically stated. Any views or opinions contained in this
message are solely those of the author, and do not necessarily represent
those of Produban, unless otherwise specifically stated and the sender
is authorised to do so. Produban doesn't accept responsibility for
damage caused by any viruses contained in this email or its attachments.
Emails may be monitored. If you've received this email by mistake,
please let the sender know at once that it's gone to the wrong person
and then destroy it without copying, using, or telling anyone about its
contents. Produban Servicios Informaticos Generales, S.L. (UK Branch).
Registered office: Shenley Wood House, Chalkdell Drive, Shenley Wood,
Milton Keynes MK5 6LA. Branch registration number BR 008486.
Ref:[PDB#014]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dedicated PostgreSQL System
On 02/01/13 14:30, Carlos Mennens wrote: Hello All, I'm trying to understand what exact parameters or configurations are adjusted when a PostgreSQL database system is going to be used as a 'stand-alone' or 'dedicated' server versus a shared or embedded database system? I have a server that's only going to be dedicated to running Linux and PostgreSQL software. Can someone please help me understand a few things I need to view or test with in order to get the most utilization from PostgreSQL & the dedicated hardware it will sit on top? -- Carlos Mennens I've found the following publication very useful, especially information on the various file systems you can use to support the actual database files:- PostgreSQL 9.0 High Performance* Paperback:* 442 pages* Publisher:* PACKT PUBLISHING (30 Oct 2010)* Language:* English* ISBN-10:* 184951030X* ISBN-13:* 978-1849510301 You can get a copy from Amazon (other online webstores are available...)
