[GENERAL] C++, Postgres , libpqxx huge query
Hello experts. I have posted this question on stack overflow, but I did not
get any detailed answer, so I thought I should cross post here. My
apologies.
I have to execute an SQL query to Postgres by the following code. The query
returns a huge number of rows (40M or more) and has 4 integer fields: When I
use a workstation with 32Gb everything works but on a 16Gb workstation the
query is very slow (due to swapping I guess). Is there any way to tell the
C++ to load rows at batches, without waiting the entire dataset? With Java I
never had these issues before, due to the probably better JDBC driver.
try {
work W(*Conn);
result r = W.exec(sql[sqlLoad]);
W.commit();
for (int rownum = 0; rownum r.size(); ++rownum) {
const result::tuple row = r[rownum];
vid1 = row[0].asint();
vid2 = row[1].asint();
vid3 = row[2].asint();
.
} catch (const std::exception e) {
std::cerr e.what() std::endl;
}
I am using PostgreSQL 9.3 and there I see this
http://www.postgresql.org/docs/9.3/static/libpq-single-row-mode.html, but I
do not how to use it on my C++ code. Your help will be appreciated.
EDIT: This query runs only once, for creating the necessary main memory data
structures. As such, tt cannot be optimized. Also, pgAdminIII could easily
fetch those rows, in under one minute on the same (or with smaller RAM) PCs.
Also, Java could easily handle twice the number of rows (with
Statent.setFetchSize()
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize%28int%29)
So, it is really an issue for the libpqxx library and not an application
issue. Is there a way to enforce this functionality in C++, without
explicitly setting limits / offsets manually? Also, is there another driver
I should use with C++ that offers this kind of functionality?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/C-Postgres-libpqxx-huge-query-tp5802330.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
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] C++, Postgres , libpqxx huge query
On 04 May 2014, at 10:57, alexandros_e alexandros...@gmail.com wrote:
Hello experts. I have posted this question on stack overflow, but I did not
get any detailed answer, so I thought I should cross post here. My
apologies.
I have to execute an SQL query to Postgres by the following code. The query
returns a huge number of rows (40M or more) and has 4 integer fields: When I
use a workstation with 32Gb everything works but on a 16Gb workstation the
query is very slow (due to swapping I guess). Is there any way to tell the
C++ to load rows at batches, without waiting the entire dataset? With Java I
never had these issues before, due to the probably better JDBC driver.
try {
work W(*Conn);
result r = W.exec(sql[sqlLoad]);
W.commit();
for (int rownum = 0; rownum r.size(); ++rownum) {
const result::tuple row = r[rownum];
vid1 = row[0].asint();
vid2 = row[1].asint();
vid3 = row[2].asint();
.
} catch (const std::exception e) {
std::cerr e.what() std::endl;
}
I am using PostgreSQL 9.3 and there I see this
http://www.postgresql.org/docs/9.3/static/libpq-single-row-mode.html, but I
do not how to use it on my C++ code. Your help will be appreciated.
I think the section of relevance is:
http://www.postgresql.org/docs/9.3/static/libpq-async.html
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
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] C++, Postgres , libpqxx huge query
Hi, On 2014-05-04 01:57:43 -0700, alexandros_e wrote: I have to execute an SQL query to Postgres by the following code. The query returns a huge number of rows (40M or more) and has 4 integer fields: When I use a workstation with 32Gb everything works but on a 16Gb workstation the query is very slow (due to swapping I guess). Is there any way to tell the C++ to load rows at batches, without waiting the entire dataset? With Java I never had these issues before, due to the probably better JDBC driver. Try looking into either using a serverside cursor or COPY. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Server continuously enters to recovery mode.
On 05/03/2014 04:29 PM, DrakoRod wrote: Hi everybody! I have a problem (really huge problem), I have one server of production, but yesterday in the night I saw this error: *ERROR: could not access status of transaction 2410303155 DETAIL: Could not open file pg_clog/08FA: No such file or directory* Solution: * dd if=/dev/zero/ of=data/pg_clog/08FA bs=256K count=1* So I ran this solutions, no problem so far, but then (after 1 or 2 hours approximately), the server crash, I think somebody did something but did not tell me, no one did nothing! cool!! ¬¬. I started the server and this began in recovery mode, he started. But after some time (without apparent pattern), the server came into the recovery mode again. After that, the server continuously entering recovery mode, like I said without apparent pattern, between 3, 5 or 10 minutes run normally but then enter in the recovery mode again. I restart the server (began in recovery mode again), started, but after sometime he enter in recovery mode again. Try to recover the server with the PITR and nothing. The server version is 9.0.x in a Linux SUSE. The database size is the 336 GB. Please give me any help to recover the server! In addition to what David said about the log information, do you have any program running against the pg_clog/ directory? Something that might be moving the files out of it? Thanks! -- Adrian Klaver adrian.kla...@aklaver.com -- 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] C++, Postgres , libpqxx huge query
Andres Freund and...@2ndquadrant.com writes: On 2014-05-04 01:57:43 -0700, alexandros_e wrote: I have to execute an SQL query to Postgres by the following code. The query returns a huge number of rows (40M or more) and has 4 integer fields: When I use a workstation with 32Gb everything works but on a 16Gb workstation the query is very slow (due to swapping I guess). Is there any way to tell the C++ to load rows at batches, without waiting the entire dataset? With Java I never had these issues before, due to the probably better JDBC driver. Try looking into either using a serverside cursor or COPY. Another possibility is libpq's recently-introduced row-at-a-time mode: http://www.postgresql.org/docs/9.3/static/libpq-single-row-mode.html though I'm not sure how effectively that's supported by libpqxx. 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
[GENERAL] Monitoring Pg servers with Microsoft SCOM
I am in the process of deploying Microsoft System Centre Operations Manager and was hoping that somebody had either developed or knew of where I could get hold of a management pack for PostgreSQL. I am not sure whether there is an instrumentation interface into the DB so haven't yet looked at rolling our own. Any comments appreciated. Glen -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 4446 Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015 Specialising in providing low-cost professional Internet Services since 1997
Re: [GENERAL] Monitoring Pg servers with Microsoft SCOM
On 5/4/2014 4:17 PM, Glen Eustace wrote: I am in the process of deploying Microsoft System Centre Operations Manager and was hoping that somebody had either developed or knew of where I could get hold of a management pack for PostgreSQL. I am not sure whether there is an instrumentation interface into the DB so haven't yet looked at rolling our own. if you have perl (strawberryperl ?) on the postgres server, you could use the checkpostgresql.pl script meant for Nagios from the Bucardo folks, it will allow you to poll for 100s of status and performance metrics, even things like table bloat. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Monitoring Pg servers with Microsoft SCOM
On 05/04/2014 04:17 PM, Glen Eustace wrote: I am in the process of deploying Microsoft System Centre Operations Manager and was hoping that somebody had either developed or knew of where I could get hold of a management pack for PostgreSQL. I am not sure whether there is an instrumentation interface into the DB so haven't yet looked at rolling our own. Any comments appreciated. Searches on this all seem to point back to this post: http://blogs.technet.com/b/kevinholman/archive/2012/03/19/opsmgr-how-to-monitor-non-microsoft-sql-databases-in-scom-an-example-using-postgre-sql.aspx Glen -- -- Adrian Klaver adrian.kla...@aklaver.com -- 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] Monitoring Pg servers with Microsoft SCOM
On 5/05/2014, at 12:26 pm, Adrian Klaver adrian.kla...@aklaver.com wrote: On 05/04/2014 04:17 PM, Glen Eustace wrote: I am in the process of deploying Microsoft System Centre Operations Manager and was hoping that somebody had either developed or knew of where I could get hold of a management pack for PostgreSQL. I am not sure whether there is an instrumentation interface into the DB so haven't yet looked at rolling our own. Any comments appreciated. Searches on this all seem to point back to this post: http://blogs.technet.com/b/kevinholman/archive/2012/03/19/opsmgr-how-to-monitor-non-microsoft-sql-databases-in-scom-an-example-using-postgre-sql.aspx Yes, I had seen that. I was more interested in being able to instrument the Pg internals, connection counts, transaction rates, RAM usage etc. In a similar sort of way to MS-SQL. Running transactions against an individual DB is still useful but I was hoping to do better. Glen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] monitoring postgresql with Munin
I'm using munin to monitor a postgresql server, using the standard munin postgres methods, it gives me quite a comprehensive set of graphs. but, I can't figure out how to use this to monitor more than one postgres instance on the same server... I have 4 instances (on 4 different ports) on a development server. anyone who's worked with these postgresql munin scripts have any ideas? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] C++, Postgres , libpqxx huge query
To answer my own question, I adapted How to use pqxx::stateless_cursor class
from libpqxx?
try {
work W(*Conn);
pqxx::stateless_cursorpqxx::cursor_base::read_only,
pqxx::cursor_base::owned
cursor(W, sql[sqlLoad], mycursor, false);
/* Assume you know total number of records returned */
for (size_t idx = 0; idx countRecords; idx += 10) {
/* Fetch 100,000 records at a time */
result r = cursor.retrieve(idx, idx + 10);
for (int rownum = 0; rownum r.size(); ++rownum) {
const result::tuple row = r[rownum];
vid1 = row[0].asint();
vid2 = row[1].asint();
vid3 = row[2].asint();
.
}
}
} catch (const std::exception e) {
std::cerr e.what() std::endl;
}
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/C-Postgres-libpqxx-huge-query-tp5802330p5802392.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
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] Monitoring Pg servers with Microsoft SCOM
I am in the process of deploying Microsoft System Centre Operations Manager and was hoping that somebody had either developed or knew of where I could get hold of a management pack for PostgreSQL. I am not sure whether there is an instrumentation interface into the DB so haven't yet looked at rolling our own. Any comments appreciated. Glen Have you looked into this? http://pg-monz.github.io/pg_monz/index-en.html Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Monitoring Pg servers with Microsoft SCOM
On 5/05/2014, at 3:18 pm, Tatsuo Ishii is...@postgresql.org wrote: I am in the process of deploying Microsoft System Centre Operations Manager and was hoping that somebody had either developed or knew of where I could get hold of a management pack for PostgreSQL. I am not sure whether there is an instrumentation interface into the DB so haven't yet looked at rolling our own. Any comments appreciated. Glen Have you looked into this? http://pg-monz.github.io/pg_monz/index-en.html Unfortunately the goal is integration of services with SCOM as opposed to monitoring PostgreSQL so I don't get a choice of platform. Glen. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
