[PERFORM] HELP!!!-----Need to Sql commands to monitoring Postgresql
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 best regards charles_xie -- View this message in context: http://postgresql.1045698.n5.nabble.com/HELP-Need-to-Sql-commands-to-monitoring-Postgresql-tp5722548.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] JDBC 5 million function insert returning Single Transaction Lock Access Exclusive Problem
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 p...@fastcrypt.com To: Eileen hey_h...@yahoo.com Cc: pgsql-performance@postgresql.org 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 hey_h...@yahoo.com 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
On Tue, Sep 4, 2012 at 12:12 AM, charles_xie xqwyy...@163.com 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(dbnamehere)), 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
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
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 j...@agliodbs.com 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
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
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 lt;xqwyy163@gt; 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(dbnamehere)), 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
Aryan Ariel Rodriguez Chalas wimo...@yahoo.com 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
On Thu, Sep 6, 2012 at 6:50 PM, charles_xie xqwyy...@163.com 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
