Re: [PERFORM] JDBC 5 million function insert returning Single Transaction Lock Access Exclusive Problem

[Eileen]

Thank you for your help.  At a high-level, I am just updating about 900k 
records in the database with new information, and during that update timetable, 
I didn't want users to get inconsistent data.

I read about the MVCC and discovered that I didn't necessarily need the LOCK 
statement.  However, based on what I read, I thought that versions of the 
database would include changes to the schema.  I found that not to be the case. 
 I.e. when I queried the database while a transaction was in the process of 
DROPing tables, it gave me an error instead of an older snapshot.  Is there any 
database which actually isolates schema changes?  I was just curious.

I have verified that while I'm DELETING rows from one session, that other 
sessions can retrieve the old data in a consistent state.  Although, in order 
to actually successfully DELETE the items, I had to add an index for all my 
Foreign Key fields.

Tina

From: Dave Cramer 
To: Eileen  
Cc: "pgsql-performance@postgresql.org"  
Sent: Friday, August 31, 2012 6:50 AM
Subject: Re: [PERFORM] JDBC 5 million function insert returning Single 
Transaction Lock Access Exclusive Problem

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Thu, Aug 30, 2012 at 2:34 AM, Eileen  wrote:
> Hi,
>
> I have written some Java code which builds a postgresql function.  That
> function calls approximately 6 INSERT statements with a RETURNING clause.  I
> recreate and re-run the function about 900,000 times.  I use JDBC to execute
> these functions on postgresql 8.3 on Windows.  When I tried running this on
> a single Connection of Postgresql, it failed (some kind of memory error).
> So I split the JDBC connections up into chunks of 5000.  I reran and
> everything was fine.  It took about 1 hour to execute all the updates.


>
> Since it took so long to perform the update, I wanted to prevent other users
> from querying the data during that time.  So I read about the LOCK command.
> It seemed like I should LOCK all the tables in the database with an ACCESS
> EXCLUSIVE mode.  That would prevent anyone from getting data while the
> database was making its updates.

Do you understand how MVCC works? Do you really need to lock out users ?
>
> Since a LOCK is only valid for 1 transaction, I set autocommit to FALSE.  I
> also removed the code which chunked up the inserts.  I had read that a
> single transaction ought to have better performance than committing after
> each insert, but that was clearly not what ended up happening in my case.

We would need more information as to what you are doing.
>
> In my case, a few problems occurred.  Number 1, the process ran at least 8
> hours and never finished.  It did not finish because the hard drive was
> filled up.  After running a manual vacuum (VACUUM FULL), no space was freed
> up.  I think this has cost me 20 GB of space.  Is there any way to free this
> space up?  I even dropped the database to no avail.
>
> Secondly, why did this process take over 8 hours to run?  While reading the
> performance mailing list, it seems like recommendations are to run lots of
> INSERTS in a single commit.  Is 5 million too many?  Is redefining a
> function over and over inside a transaction a problem?  Does the RETURNING
> clause present a problem during a single transaction?

VACUUM FULL on 8.3 is not a good idea
>
> If anyone has any suggestions for me, I would really appreciate it.
>

Can you explain at a high level what you are trying to do ?

> Tina


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance






 

Re: [PERFORM] HELP!!!-----Need to Sql commands to monitoring Postgresql

[Daniel Farina]

On Tue, Sep 4, 2012 at 12:12 AM, charles_xie  wrote:
> Hi all,
>  i have 5 servers that have been installing postgresql .In order to
> know the postgresql working  status and monitor them ,moreover i don't want
> to  use the monitor tools .I  want to use the SQL commands to monitoring
> postgresql system . please suggest any SQL COMMANDS to work successfully.if
> you have some good suggestion ,you can email to me
> (charles@sanmina-sci.com) or sky :xqwbx163

Hello,

You might want to try pgsql-general or the wiki.  The right stuff also
depends on what you are monitoring for.

Basic uptime and information: "SELECT 1" ("can I log in?"), but also
counting the number of connections (select count(*) from
pg_stat_activity), the number of contending connections (select
count(*) from pg_stat_activity where waiting = 't'), the number of
tables (select count(*) from pg_tables), database size (select
pg_database_size()), and database version (select
version()) we find useful.  It's so useful we put it into a very
condensed and cryptic status line (which can optionally have more
information in more exceptional conditions) like:

[100.5GB:140T:7C], (v9.0.6, --other statuses if they occur--)

The space of queries used for tuning and capacity are much larger, but
I find these basic chunks of information a useful fingerprint of most
databases and activity levels in a relatively small amount of space.

-- 
fdr


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] HELP!!!-----Need to Sql commands to monitoring Postgresql

[Josh Berkus]

On 9/4/12 12:12 AM, charles_xie wrote:
> Hi all,
>  i have 5 servers that have been installing postgresql .In order to
> know the postgresql working  status and monitor them ,moreover i don't want
> to  use the monitor tools .I  want to use the SQL commands to monitoring
> postgresql system . please suggest any SQL COMMANDS to work successfully.if
> you have some good suggestion ,you can email to me
> (charles@sanmina-sci.com) or sky :xqwbx163

Actually, the Nagios extension for PostgreSQL, check_postgres.pl, has a
really good, very complete set of queries in its code.  You could mine
them from there.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] HELP!!!-----Need to Sql commands to monitoring Postgresql

[Steven Crandell]

Also probably some good info to be mined out of postbix.
http://www.zabbix.com/wiki/howto/monitor/db/postbix/monitor_postgres_with_zabbix

On Thu, Sep 6, 2012 at 12:44 PM, Josh Berkus  wrote:

> On 9/4/12 12:12 AM, charles_xie wrote:
> > Hi all,
> >  i have 5 servers that have been installing postgresql .In order
> to
> > know the postgresql working  status and monitor them ,moreover i don't
> want
> > to  use the monitor tools .I  want to use the SQL commands to monitoring
> > postgresql system . please suggest any SQL COMMANDS to work
> successfully.if
> > you have some good suggestion ,you can email to me
> > (charles@sanmina-sci.com) or sky :xqwbx163
>
> Actually, the Nagios extension for PostgreSQL, check_postgres.pl, has a
> really good, very complete set of queries in its code.  You could mine
> them from there.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

[PERFORM] libpq or postgresql performance

[Aryan Ariel Rodriguez Chalas]

Hello,

I'm working with an application that connects to a remote server database using 
"libpq" library over internet, but making a simple query is really slow even 
though I've done PostgreSQL Tunning and table being indexed, so I want to know:

-Why is postgresql or libpq that slow when working over internet?
-What else should I do to solve this issue in addition of postgresql tunning?
-Why if I connect to the remote server desktop (using RDP or any Remote Desktop 
Application) and run the application using the same internet connection, it 
runs really fast when making requests to postgresql; but if I run the 
application locally by connecting to the remote postgresql server through 
"libpq", it's really slow?.

Thanks in advance,

Ariel Rodriguez



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] HELP!!!-----Need to Sql commands to monitoring Postgresql

[charles_xie]

Hi，
   Thanks for your advice.i know the basic monitoring skill,because the
postgresql database is used for the factory production , so I hope they can
run  normal and exert more perfect performance. so i need to be considered
from the point of view ,eg : threading ,locks and so on.


Daniel Farina-4 wrote
> 
> On Tue, Sep 4, 2012 at 12:12 AM, charles_xie  wrote:
>> Hi all,
>>  i have 5 servers that have been installing postgresql .In order
>> to
>> know the postgresql working  status and monitor them ,moreover i don't
>> want
>> to  use the monitor tools .I  want to use the SQL commands to monitoring
>> postgresql system . please suggest any SQL COMMANDS to work
>> successfully.if
>> you have some good suggestion ,you can email to me
>> (charles.xie@) or sky :xqwbx163
> 
> Hello,
> 
> You might want to try pgsql-general or the wiki.  The right stuff also
> depends on what you are monitoring for.
> 
> Basic uptime and information: "SELECT 1" ("can I log in?"), but also
> counting the number of connections (select count(*) from
> pg_stat_activity), the number of contending connections (select
> count(*) from pg_stat_activity where waiting = 't'), the number of
> tables (select count(*) from pg_tables), database size (select
> pg_database_size()), and database version (select
> version()) we find useful.  It's so useful we put it into a very
> condensed and cryptic status line (which can optionally have more
> information in more exceptional conditions) like:
> 
> [100.5GB:140T:7C], (v9.0.6, --other statuses if they occur--)
> 
> The space of queries used for tuning and capacity are much larger, but
> I find these basic chunks of information a useful fingerprint of most
> databases and activity levels in a relatively small amount of space.
> 
> -- 
> fdr
> 
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/HELP-Need-to-Sql-commands-to-monitoring-Postgresql-tp5722548p5723150.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] libpq or postgresql performance

[Andreas Kretschmer]

Aryan Ariel Rodriguez Chalas  wrote:

> Hello,
> 
> I'm working with an application that connects to a remote server database 
> using "libpq" library over internet, but making a simple query is really slow 
> even though I've done PostgreSQL Tunning and table being indexed, so I want 
> to know:

define slow.

> 
> -Why is postgresql or libpq that slow when working over internet?
> -What else should I do to solve this issue in addition of postgresql tunning?
> -Why if I connect to the remote server desktop (using RDP or any Remote 
> Desktop Application) and run the application using the same internet 
> connection, it runs really fast when making requests to postgresql; but if I 
> run the application locally by connecting to the remote postgresql server 
> through "libpq", it's really slow?.

Maybe DNS-Resolving - Problems...


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] HELP!!!-----Need to Sql commands to monitoring Postgresql

[Daniel Farina]

On Thu, Sep 6, 2012 at 6:50 PM, charles_xie  wrote:
> Hi，
>Thanks for your advice.i know the basic monitoring skill,because the
> postgresql database is used for the factory production , so I hope they can
> run  normal and exert more perfect performance. so i need to be considered
> from the point of view ,eg : threading ,locks and so on.

I think the key structures you are looking for, then, are queries on
pg_stat_activity, pg_locks, the pg_statio table, and also "bloat" of
tables and indexes (the wiki has several slightly different relatively
large queries that help track bloat).

As others have mentioned, there are existing tools with an impressive
number of detailed queries, but knowing about these can help you
informally categorize what you are looking at.  check_postgres.pl is
especially useful to copy queries from, if not using it in a Nagios
installation entirely.

-- 
fdr


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] libpq or postgresql performance

[Albe Laurenz]

Aryan Ariel Rodriguez Chalas wrote:
> I'm working with an application that connects to a remote server database 
> using "libpq" library over
> internet, but making a simple query is really slow even though I've done 
> PostgreSQL Tunning and table
> being indexed, so I want to know:
> 
> -Why is postgresql or libpq that slow when working over internet?
> -What else should I do to solve this issue in addition of postgresql tunning?
> -Why if I connect to the remote server desktop (using RDP or any Remote 
> Desktop Application) and run
> the application using the same internet connection, it runs really fast when 
> making requests to
> postgresql; but if I run the application locally by connecting to the remote 
> postgresql server through
> "libpq", it's really slow?.

There are a million possible reasons; it would be a good
idea to trace at different levels to see where the time
is lost.

One thing that comes to mind and that is often the cause of
what you observe would be that there is a lot of traffic
between the database and the application, but little traffic
between the application and the user.

Yours,
Laurenz Albe

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] libpq or postgresql performance

[Ireneusz Pluta]

W dniu 2012-09-06 22:04, Aryan Ariel Rodriguez Chalas pisze:

-Why if I connect to the remote server desktop (using RDP or any Remote Desktop 
Application) and run the application using the same internet connection, it runs really 
fast when making requests to postgresql; but if I run the application locally by 
connecting to the remote postgresql server through "libpq", it's really slow?.
It might look like the client side fetches too much data or sends too many queries over the 
connection to the database server and then further processes that data locally. Are you using some 
kind of ORM in your application?


If that is the case, you might need to refactor your application to do as much as possible 
computation at server side and deal only with computation results over the connection, not the raw data.


Try to see in server log what SQL statements are executed while you are running your application. 
You need to SET log_statement TO 'all' for that.


With psql, try to see how much data (how many rows) are returned from that query you call "simple 
query". Even simple query may return a lot of rows. Server backend might execute it quickly, but 
returning a huge result over the Internet might take a long time.



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance