Re: [GENERAL] Partitioning Advice
On 2012-06-06, Ben Carbery wrote: > In the current environment I am given a single VHDD which I have not > partitioned at all. The SAN performance is pretty good, but we have noticed > slowdowns at various times.. The database does a lot of logging - constant > small writes, with some probably insignificant reads of smaller tables. > Delays in logging can effect the service which is doing the logging and > cause problems upstream. Typically this does not happen, but there are > hourly jobs which generate stats from the logs. Due to their complexity the > query planner always chooses to do sequential scans on the main log > table. This table is truncated monthly when the data is archived to another > table, but peaks in size at around 10GB at the end of the month. Generally > any time the stats jobs are running there are delays which I would like to > reduce/eliminate. There is also a fair bit of iowait on the cpu. do many of the statistical queries use the whole month's data? have you considered partitioning the log table by day? if you can reduce the number of rows involved in the table-scans by partitioning it'll be help performance. -- ⚂⚃ 100% natural -- 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] problem after upgrade db missing
On 2012-06-05, Aleksander Rozman wrote: > Now I am not sure two of this directories are old databases, but I think > they are... Is there a way to register one of this databases into new > installation (I am sure that directory "1" is old postgres database, and > "11563" is my database I want to save, and "11564" is new postgresql > database (this is only directory with new date). bad news: template0 template1 postgres > > Any help is appreciated? I would really need data from that database... > > Thanks for all help in advance. > Andy > -- ⚂⚃ 100% natural -- 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] Procedural Languages
On 2012-06-08, Zenaan Harkness wrote: > I'd like find out how often the JVM starts up eg based on queries or > sessions or connections or what... didn't know it was potentially > woeful. In particular re "Because Pg doesn't re-use backends, there's > a huge amount of JVM startup and shutdown cost." A "backend" is what is at the other end of the socket which mediates the "connection" - there's a 1:1 mapping. -- ⚂⚃ 100% natural -- 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] problem after upgrade db missing
On 6/6/2012 2:35 AM, Daniel Verite wrote: Aleksander Rozman wrote: Now I am not sure two of this directories are old databases, but I think they are... Is there a way to register one of this databases into new installation (I am sure that directory "1" is old postgres database, and "11563" is my database I want to save, and "11564" is new postgresql database (this is only directory with new date). Actually 1, 11563 and 11564 are what you get with a fresh new installation. On a 8.4 install on Ubuntu, the corresponding databases are: SELECT oid, datname from pg_database where oid in (1,11563,11564); oid | datname ---+--- 1 | template1 11563 | template0 11564 | postgres Also normally initdb wouldn't work on a non-empty data directory, anyway. I'd say that either the old data directory has been moved aside at some point of the upgrade procedure, or it has been wiped out :( Best regards, Problem is that upgrade of Ubuntu failed at some point, and after that I did clean install... So database probably got deleted at some point. Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Run external SQL file via Perl dbh
Hi There, I'd like to be able to run the contents of an external SQL file from Perl. Something akin to: $dbh->do( '\i /home/david/run_me.sql' ); However this fails, and I assume that is because the \i is a client command. Is there a way to run the contents of an external SQL from a Per database handle? Best regards David -- 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] Run external SQL file via Perl dbh
On Sat, Jun 9, 2012 at 3:29 PM, David Williams
wrote:
>
> Hi There,
>
> I'd like to be able to run the contents of an external SQL file from Perl.
> Something akin to:
>
> $dbh->do( '\i /home/david/run_me.sql' );
>
> However this fails, and I assume that is because the \i is a client command.
> Is there a way to run the contents of an external SQL from a Per database
> handle?
What we do in LedgerSMB is something like:
system("psql -f /home/david/run_me.sql > /tmp/dblog 2>&1") after
setting appropriate environment variables.
But if you want to do this server-side there is always pl/perlU
Best Wishes,
Chris Travers
--
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] Partitioning Advice
> do many of the statistical queries use the whole month's data? > have you considered partitioning the log table by day? if you can > reduce the number of rows involved in the table-scans by partitioning > it'll be help performance. > I am summarising by hour, day, week and month. So I guess partitioning by day would help for the daily and hourly summaries. Thanks for the suggestions everyone, I'll look into all these ideas. cheers
[GENERAL] Segmentation Fault
Hi All, We are having a problem with our streaming replication read only node. It has crashed a few times with a couple of different reasons, mostly "segmentation fault". The latest log are listed below: 2012-05-30 23:56:37.385 UTC::: LOG: server process (PID 19476) was terminated by signal 11: Segmentation fault 2012-05-30 23:56:37.385 UTC::: LOG: terminating any other active server processes 2012-05-30 23:56:37.385 UTC:10.43.6.61:webmaster:panorama WARNING: terminating connection because of crash of another server process 2012-05-30 23:56:37.385 UTC:10.43.6.61:webmaster:panorama DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2012-05-30 23:56:37.385 UTC:10.43.6.61:webmaster:panorama HINT: In a moment you should be able to reconnect to the database and repeat your command. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama WARNING: terminating connection because of crash of another server process 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama HINT: In a moment you should be able to reconnect to the database and repeat your command. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama WARNING: terminating connection because of crash of another server process 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama HINT: In a moment you should be able to reconnect to the database and repeat your command. 2012-05-30 23:56:37.575 UTC:10.43.6.81:webmaster:panorama FATAL: the database system is in recovery mode Our setup: 2x physical server - Dell PE R815, 64GB ECC RAM, 2 CPUs (12 cores each), storing pgsql data on SAN backed volumes. CentOS 5.6 PostgreSQL 9.0.8, compiled *without* int64 datetime. Both servers are identically configured (or at least as much as we could ensure) One is master, another is streaming read-only node. The master runs two instances of postgreSQL, where the slave runs 5 instances of postgreSQL. 2 out of 5 are streaming replication from the master, rest 3 are streaming replication from other DB nodes. Those 2 instances serves clients as Read Only. The master node never had any crash so far. However, the 2 instances on slave have crashed 3 times by now, 1 time on one readonly instance, twice on another readonly instance. Above log was generated from one of the instances. All three crashes happened when the database was doing vacuuming. we automatically purge some data every night, and run vacuum analyze right after that... Our the CPU load is generally on 40%-60% mark. I have run a complete set of hardware diagnostics on the slave , with no faulty hardware detected. Can someone kindly shed some lights on me? I am not sure where to look into at this point Cheers, Bo Jin Operating/IT Manager Troo Corporation [ www.troo.com ] 43 Auriga Drive, Suite 102, Ottawa, ON K2E 7Y8 Ph: +1 877.702.8766 x156 Fax: +1 855.726.8766
Re: [GENERAL] Question about load balance
On 2012-06-08 08:39, Craig Ringer wrote: On 06/07/2012 10:26 PM, Condor wrote: Hello ppl, I read in internet and in this mailing list, when some one asking about load balance, most of the answers is: pgpool. I want to asking how stable is pgpool ? How much query can handle ? What load average ? Im looking for something multi master solution. PgPool-II doesn't offer mutli-master operation. Not much does. Multi-master is very difficult to get right, and even harder to make fast. Are you really sure it's what you want? Failover is often a much, MUCH simpler and more efficient approach. -- Craig Ringer No, Im not sure, just looking how to make load balance. I have a small database around 20 gb, but I expect to join another database on different scheme and Im looking for solution about load balance or some cache mechanism. Bad part is one row from db is read once in a month, in worst scenario 3-4 times in month and I think cache is not good option, but I don't have idea how will work. H. -- 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] Question about load balance
On 06/10/12 11:26 PM, Condor wrote: Im not sure, just looking how to make load balance. I have a small database around 20 gb, but I expect to join another database on different scheme and Im looking for solution about load balance or some cache mechanism. Bad part is one row from db is read once in a month, in worst scenario 3-4 times in month and I think cache is not good option, but I don't have idea how will work. whats the problem you're trying to solve? so far, doesn't sound like you have anything that a decent database server couldn't handle easily. -- john r pierceN 37, W 122 santa cruz ca mid-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
