Thread: ? in explain query

Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> In the explain below are the references
> "outer"."?column2?" = "inner"."?column2?"
> Ok?

Yeah, those are variables that don't have any name because they don't
correspond exactly to table columns.  It looks like the plan is
merge-joining (main.id)::text to (groups_1.instance)::text.  At the
level of the scans it's possible to see the expressions involved, but
at the level of the join those are just Var references to the outputs
of the lower plan steps.

We could possibly alter EXPLAIN to print the referred-to expression
instead of faking up a name for the Var node, but this would make the
printout look like the expression was being recomputed at the upper
level, which it isn't.

            regards, tom lane

I can't offer info about monitoring for break-ins or other malicious
activity.  But if you're looking for monitoring a site/database to
see that it's up and working properly, there are many, many options.

Here's a product I've used to monitor an extremely complex site at a
financial company (expensive, but works well):
http://www.mercuryinteractive.com/products/sitescope/


I've heard good things about this software, but haven't used it:
http://www.nagios.org/


We use this software at my current job (works OK, nothing special):
http://www.maxum.com/PageSentry/


There are also services that will monitor sites for you.

- Jeff



>Hi all:
>
>We maintain a number of web-based applications that use postgres as
>the back end. We recently had an unfortunate situation where the
>hosted server was hacked and the client had some significant
>downtime. We proposed a custom monitoring app, written in PHP, that
>would periodically monitor (for example) the web server  (Apache)
>and database (postgres) so that we'd know more quickly when
>something happened.
>
>The client responded that surely this problem of monitoring a
>database-backed web app was a known, solved problem, and wanted to
>know what other people did to solve the problem.
>
>So my question, hopefully not too off-topic: if you administer a
>mission-critical postgres install that needs high availability, what
>do you do for monitoring? Commercial, freeware or open source tool?
>Custom scripts? Anything I haven't thought of?
>
>-- sgl
>
>
>=======================================================
>Steve Lane
>
>Vice President
>The Moyer Group
>14 North Peoria St Suite 2H
>Chicago, IL 60607
>
>Voice: (312) 433-2421       Email: slane@moyergroup.com
>Fax:   (312) 850-3930       Web:   http://www.moyergroup.com
>=======================================================

--

Jeff Bohmer
VisionLink, Inc.
_________________________________
303.402.0170
www.visionlink.org
_________________________________
People. Tools. Change. Community.

On Monday 03 November 2003 5:19 pm, Steve Lane wrote:
> Hi all:
>
> We maintain a number of web-based applications that use postgres as
> the back end....
>
> The client responded that surely this problem of monitoring a
> database-backed web app was a known, solved problem, and wanted to
> know what other people did to solve the problem.

Do your best to anticipate and then plug holes as found. A sad story
(different OS/DB/server but same idea - this tale involves
NT/IIS/Cold Fusion/MSSQL).

Back in the day at a dot-com the colo would "monitor the servers". By
monitor they meant "ping". Of course IIS had a habit of playing dead
and pings worked fine.

So the colo added a port 80 monitor to the servers. This would alarm
if a connection to port 80 was refused. Turned out, however, that IIS
could die leaving a Dr. Watson message on the screen. It would
continue accepting connections to port 80 but do nothing with them -
at least until the Dr. Watson warning was clicked at which time the
alarm would go off. Useless.

So we switched to regex testing. The monitoring system would look for
a special page - something like /test.html and make sure the correct
text, "IIS running" was returned.

But it turned out that Cold Fusion could die on its own. So we changed
the test page to look at /test.cf (or whatever Cold Fusion used as a
extension - I don't care to remember). That page concatenated a
couple of strings and returned the result. Cool, we were much better
at trapping events.

But what about the database? We changed the ColdFusion page to run a
very simple query - something like "select 0" (see a thread from a
couple months back regarding "what's the fastest query" which had to
do with PG server monitoring) and if it got the correct result it
would return something like "db running".

We were happy with this arrangement till we discovered that _parts_ of
ColdFusion could die and the rest could run fine?!? The tests worked
most of the time but when CF "half-died" one page of the site that
pulled data from another web site would not work.

So we switched everything to a Java based app server and were able to
handle twice the load with 1/7th the machines and crashing became a
thing of the past - but I digress.

We used the same basic tests on the new server. We had a static page
served by the front-end, a simple page served by the app server, and
one that checked the database server. The colo monitors checked the
database testing page and the others allowed for some quick-n-dirty
remote diagnosis (hmm, front end and app servers are running but db
isn't responding to the app server could be determined in 30 seconds
from any browser).

In addition we automatically checked the pages from our office and I
checked from a server at home. The checks ran once per minute and 3
consecutive fails would trigger a page. I'm sure there are many
things that could have fooled us but they are rare enough that we
never saw them - the monitoring worked like a charm.

Don't forget that you need to make sure the monitoring is happening.
It's easy to lose track of a well-written monitoring app when there
are no failures and only find that someone turned the monitor program
off when a real failure happens. We figured that the combination of
our monitoring along with the colo monitoring offered enough
redundancy.

Obviously it's best if at least some of the monitoring comes from
off-site and never trust a machine to monitor itself.

BTW, some of these server test pages can be used by a load balancer to
fail a server in a cluster so they are very handy for more than just
testing.

Oh, to answer your other question - the problem has been "solved". You
can pay for very expensive monitoring from a variety of third
parties.

Cheers,
Steve

Hi,

I've got experice with monitoring all kinds of serviers/services with Nagios
(www.nagios.org). I has some plugins to check webservers, postgresql etc.
And is you need any special test, you can write your own plugin (open a
webpage and check for a certain string or so).

We use i to monitor our customers servers (via nrpe en passive checks even
servers behind a nat/firewall).

Regards

Jean Huveneers

Xillion ICT Solutions B.V.
Marktplein 8
6243 BR  Geulle
The Netherlands

tel: +31 (0)43 3659244
fax: +31 (0)43 3659249
gsm: +31 (0)6 456 44 357
www: www.xillion.nl
-----Original Message-----
From: Steve Lane [mailto:slane@moyergroup.com]
Sent: dinsdag 4 november 2003 2:20
To: PGSQL List (E-mail)
Subject: [ADMIN] Application monitoring


Hi all:

We maintain a number of web-based applications that use postgres as the back
end. We recently had an unfortunate situation where the hosted server was
hacked and the client had some significant downtime. We proposed a custom
monitoring app, written in PHP, that would periodically monitor (for
example) the web server  (Apache) and database (postgres) so that we’d know
more quickly when something happened.

The client responded that surely this problem of monitoring a
database-backed web app was a known, solved problem, and wanted to know what
other people did to solve the problem.

So my question, hopefully not too off-topic: if you administer a
mission-critical postgres install that needs high availability, what do you
do for monitoring? Commercial, freeware or open source tool? Custom scripts?
Anything I haven’t thought of?

-- sgl


=======================================================
Steve Lane

Vice President
The Moyer Group
14 North Peoria St Suite 2H
Chicago, IL 60607

Voice: (312) 433-2421       Email: slane@moyergroup.com
Fax:   (312) 850-3930       Web:   http://www.moyergroup.com
=======================================================

Hi Steve

In my old company we had some shell skripts, monitoring all the oracle
stuff. I plan to transfer this to pg later. It was easy but verry stable
concept: 2 Servers where watching each other with the same
functionality: the master was checking all databases for availablity,
free space in tablespace (does not (yet) exist in postgreSQL), free
diskspace for oracle mountpoints, listener, apache, etc...
If there was a problem, after 3 alarms (3x5 minutes) a pikett dba was
called via pager-call etc.

If it helps to you, please let me know.

Oli


Steve Lane wrote:

> Hi all:
>
> We maintain a number of web-based applications that use postgres as
> the back end. We recently had an unfortunate situation where the
> hosted server was hacked and the client had some significant downtime.
> We proposed a custom monitoring app, written in PHP, that would
> periodically monitor (for example) the web server (Apache) and
> database (postgres) so that we’d know more quickly when something
> happened.
>
> The client responded that surely this problem of monitoring a
> database-backed web app was a known, solved problem, and wanted to
> know what other people did to solve the problem.
>
> So my question, hopefully not too off-topic: if you administer a
> mission-critical postgres install that needs high availability, what
> do you do for monitoring? Commercial, freeware or open source tool?
> Custom scripts? Anything I haven’t thought of?
>
> -- sgl
>
>
> =======================================================
> Steve Lane
>
> Vice President
> The Moyer Group
> 14 North Peoria St Suite 2H
> Chicago, IL 60607
>
> Voice: (312) 433-2421 Email: slane@moyergroup.com
> Fax: (312) 850-3930 Web: http://www.moyergroup.com
> =======================================================



--
-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import

Thanks to all who gave me responses on this issue. A number of you
recommended Nagios (www.nagios.com), and a quick inspection does suggest
that this is a very useful tool. I plan to look into it further.

-- sgl


=======================================================
Steve Lane

Vice President
The Moyer Group
14 North Peoria St Suite 2H
Chicago, IL 60607

Voice: (312) 433-2421       Email: slane@moyergroup.com
Fax:   (312) 850-3930       Web:   http://www.moyergroup.com
=======================================================

Hello all:

Are there any known incompatibilities between postgres and RedHat 9? Is
there a minimum version of postgres I should be using on RedHat9?

-- sgl

RedHat 9 ships with Postgres so there should be no incompatibilities.
However, there has been an errata issued and an updated rpm is available
at https://rhn.redhat.com/errata/rh9-errata.html.  I always download the
source and compile my own though to get an optimized build.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Steve Lane
Sent: Saturday, November 15, 2003 5:45 PM
To: 'PGSQL List (E-mail)'
Subject: [ADMIN] Postrgesql on RedHat 9


Hello all:

Are there any known incompatibilities between postgres and RedHat 9? Is
there a minimum version of postgres I should be using on RedHat9?

-- sgl


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match