Re: [PERFORM] log_statement at postgres.conf
I added the following to FreeBSD: /etc/newsyslog.conf: /var/log/postgresql 600 7 *@T00 JC make new file? /etc/syslog.conf: local0.*/var/log/postgresql /usr/local/pgsql/data/postgresql.conf: log_destination = 'syslog' syslog_facility = 'LOCAL0' syslog_ident = 'postgres' log_min_duration_statement = 100# -1 is disabled, 0 logs all statements, in ms. I already do this, but i can't find my log file FYI, i just wanna to log every SQL statement. Remember to touch /var/log/postgresql before restarting syslogd (kill -HUP syslog-pid). Chmod 0700 so only root can read the log-file. Adjust log_min_duration_statement to your needs. i don't understand to touch /var/log/postgresql Hello, another possibility is to have logs stored in a file by just changing 'redirect_stderr' to 'on' and 'log_destination' to 'stderr'. This way, with the default config, all logs sent to stderr will be written to 'log_directory' under the name 'log_filename', without having to change syslog.conf (you just need to change postgresql.conf). Additionaly, I added 'log_rotation_size = 0' to have on log file per day. Note that in that case, the log files won't be rotated, you'll need to check you don't store too many log file after a few months (as the number of files will increase every day). setting 'log_destination' to 'stderr' could also log every sql statement happen on my server? My mission is to activate 'log_statement' to 'all', so that i can log all sql activity on my database. Regards, Joko [SYSTEM] PT. Indra Jaya Swastika Phone: +62 31 7481388 Ext 201 http://www.ijs.co.id -- If you have any problem with our services , please contact us at 70468146 or e-mail: [EMAIL PROTECTED] PT Indra Jaya Swastika | Jl. Kalianak Barat 57A | +62-31-7481388
Re: [PERFORM] log_statement at postgres.conf
On Fri, 18 Jul 2008, System/IJS - Joko wrote: I added the following to FreeBSD: /etc/newsyslog.conf: /var/log/postgresql 600 7 *@T00 JC make new file? /etc/syslog.conf: local0.*/var/log/postgresql /usr/local/pgsql/data/postgresql.conf: log_destination = 'syslog' syslog_facility = 'LOCAL0' syslog_ident = 'postgres' log_min_duration_statement = 100# -1 is disabled, 0 logs all statements, in ms. I already do this, but i can't find my log file FYI, i just wanna to log every SQL statement. Remember to touch /var/log/postgresql before restarting syslogd (kill -HUP syslog-pid). Chmod 0700 so only root can read the log-file. Adjust log_min_duration_statement to your needs. i don't understand to touch /var/log/postgresql Hello, another possibility is to have logs stored in a file by just changing 'redirect_stderr' to 'on' and 'log_destination' to 'stderr'. This way, with the default config, all logs sent to stderr will be written to 'log_directory' under the name 'log_filename', without having to change syslog.conf (you just need to change postgresql.conf). Additionaly, I added 'log_rotation_size = 0' to have on log file per day. Note that in that case, the log files won't be rotated, you'll need to check you don't store too many log file after a few months (as the number of files will increase every day). setting 'log_destination' to 'stderr' could also log every sql statement happen on my server? My mission is to activate 'log_statement' to 'all', so that i can log all sql activity on my database. There're 2 points in your question : - what to log - where to log To choose 'what' to log in your case, you can change 'log_statement' to 'all'. Then, to choose 'where' to log, you can either use the proposal in the first answer, or change 'log_destination' to 'stderr' and 'redirect_stderr' to 'on'. Nicolas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] query plan, index scan cost
hi list,
i have a problem with time consuming query. first of all my table structure:
CREATE TABLE nw_tla_2008_4_deu
(
ID bigint NOT NULL,
NET2CLASS smallint,
FOW smallint,
CONSTRAINT nw_tla_2008_4_deu_pkey PRIMARY KEY (ID),
)
WITHOUT OIDS;
CREATE INDEX nw_tla_2008_4_deu_fow_idx
ON nw_tla_2008_4_deu
USING btree
(FOW);
CREATE INDEX nw_tla_2008_4_deu_net2class_idx
ON nw_tla_2008_4_deu
USING btree
(NET2CLASS);
CREATE INDEX nw_tla_2008_4_deu_the_geom_gist
ON nw_tla_2008_4_deu
USING gist
(the_geom gist_geometry_ops);
ALTER TABLE nw_tla_2008_4_deu CLUSTER ON nw_tla_2008_4_deu_the_geom_gist;
when i run the following query with explain analyze i get the following result:
EXPLAIN
ANALYZE
SELECT
nw.ID AS id
FROM
nw_tla_2008_4_deu AS nw
WHERE
expand(st_pointfromtext('POINT(13.7328934 51.049476)',4326),
0.2476961598054) nw.the_geom
AND nw.FOW IN (1,2,3,4,10,17)
AND nw.NET2CLASS IN (0,1,2,3)
Bitmap Heap Scan on nw_tla_2008_4_deu nw (cost=35375.52..77994.15 rows=11196
width=8) (actual time=13307.830..13368.969 rows=15425 loops=1)
Recheck Cond: (NET2CLASS = ANY ('{0,1,2,3}'::integer[]))
Filter:
(('010320E610010005C06BF82A4000A0A066494000C06BF82A4000C009A6494000E00FF62B4000C009A6494000E00FF62B4000A0A066494000C06BF82A4000A0A0664940'::geometry
the_geom) AND (FOW = ANY ('{1,2,3,4,10,17}'::integer[])))
- BitmapAnd (cost=35375.52..35375.52 rows=12614 width=0) (actual
time=13307.710..13307.710 rows=0 loops=1)
- Bitmap Index Scan on nw_tla_2008_4_deu_the_geom_gist (cost=0.00..1759.12
rows=55052 width=0) (actual time=22.452..22.452 rows=52840 loops=1)
Index Cond:
('010320E610010005C06BF82A4000A0A066494000C06BF82A4000C009A6494000E00FF62B4000C009A6494000E00FF62B4000A0A066494000C06BF82A4000A0A0664940'::geometry
the_geom)
- Bitmap Index Scan on nw_tla_2008_4_deu_net2class_idx (cost=0.00..33610.55
rows=1864620 width=0) (actual time=13284.121..13284.121 rows=2021814 loops=1)
Index Cond: (NET2CLASS = ANY ('{0,1,2,3}'::integer[]))
Total runtime: *13.332* ms
running the next query which is only slightly different and has one instead of
two and conditions leads to the following result
EXPLAIN
ANALYZE
SELECT
nw.ID AS id
FROM
nw_tla_2008_4_deu AS nw
WHERE
expand(st_pointfromtext('POINT(13.7328934 51.049476)',4326),
0.2476961598054) nw.the_geom
AND nw.FOW IN (1,2,3,4,10,17)
Bitmap Heap Scan on nw_tla_2008_4_deu nw (cost=1771.34..146161.54 rows=48864
width=8) (actual time=23.285..99.493 rows=47723 loops=1)
Filter:
(('010320E610010005C06BF82A4000A0A066494000C06BF82A4000C009A6494000E00FF62B4000C009A6494000E00FF62B4000A0A066494000C06BF82A4000A0A0664940'::geometry
the_geom) AND (FOW = ANY ('{1,2,3,4,10,17}'::integer[])))
- Bitmap Index Scan on nw_tla_2008_4_deu_the_geom_gist (cost=0.00..1759.12
rows=55052 width=0) (actual time=22.491..22.491 rows=52840 loops=1)
Index Cond:
('010320E610010005C06BF82A4000A0A066494000C06BF82A4000C009A6494000E00FF62B4000C009A6494000E00FF62B4000A0A066494000C06BF82A4000A0A0664940'::geometry
the_geom)
Total runtime: *109*ms
so in both querys there are and conditions. there are two and conditions in the
first query and one and condition in the second query. unfortunately i am not
an expert in reading the postgre query plan. basically i am wondering why in
the first query a second index scan is done whereas in the second query the
second index scan is not done. the second query runs hundred times faster then
first one which surprising to me.
any ideas?
regards, stefan
_
In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten!
Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Mailing list hacked by spammer?
I've never gotten a single spam from the Postgres mailing list ... until today. A Chinese company selling consumer products is using this list. I have my filters set to automatically trust this list because it has been so reliable until now. It would be really, really unfortunate if this list fell to the spammers. Craig -- 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] Mailing list hacked by spammer?
On Jul 18, 2008, at 4:02 PM, Craig James wrote: I've never gotten a single spam from the Postgres mailing list ... until today. A Chinese company selling consumer products is using this list. I have my filters set to automatically trust this list because it has been so reliable until now. It would be really, really unfortunate if this list fell to the spammers. It's not been hacked by spammers. It's a valid From address, probably coincidentally. Nothing worth discussing. *Definitely* not something worth discussing on the list. Cheers, Steve -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] long transaction
Hi there, I have a script which includes 3 called functions within a single transaction. At the beginning, the functions runs fast enough (about 60 ms each). In time, it begins to run slower and slower (at final about one per 2 seconds). I check the functions that runs slowly outside the script and they run normally (60 ms each). What is the problem ? TIA, Sabin -- 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] Mailing list hacked by spammer?
Steve Atkins wrote: On Jul 18, 2008, at 4:02 PM, Craig James wrote: I've never gotten a single spam from the Postgres mailing list ... until today. A Chinese company selling consumer products is using this list. I have my filters set to automatically trust this list because it has been so reliable until now. It would be really, really unfortunate if this list fell to the spammers. It's not been hacked by spammers. It's a valid From address, probably coincidentally. Nothing worth discussing. *Definitely* not something worth discussing on the list. Keep in mind that messages from unsubscribed addresses are held up for moderation. A human moderator must then reject it or approve it, and humans make mistakes sometimes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Mailing list hacked by spammer?
Most likely just a forged header or something, hardly hacked though is it. I think you need to do some training: http://www2.b3ta.com/bigquiz/hacker-or-spacker/ - Original Message From: Craig James [EMAIL PROTECTED] To: pgsql-performance@postgresql.org Sent: Friday, 18 July, 2008 4:02:37 PM Subject: [PERFORM] Mailing list hacked by spammer? I've never gotten a single spam from the Postgres mailing list ... until today. A Chinese company selling consumer products is using this list. I have my filters set to automatically trust this list because it has been so reliable until now. It would be really, really unfortunate if this list fell to the spammers. Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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] long transaction
have you use VACUMM? --- On Fri, 7/18/08, Sabin Coanda [EMAIL PROTECTED] wrote: From: Sabin Coanda [EMAIL PROTECTED] Subject: [PERFORM] long transaction To: pgsql-performance@postgresql.org Date: Friday, July 18, 2008, 3:34 PM Hi there, I have a script which includes 3 called functions within a single transaction. At the beginning, the functions runs fast enough (about 60 ms each). In time, it begins to run slower and slower (at final about one per 2 seconds). I check the functions that runs slowly outside the script and they run normally (60 ms each). What is the problem ? TIA, Sabin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Mailing list hacked by spammer?
Glyn Astill wrote: Most likely just a forged header or something, hardly hacked though is it. Yes, hack is the correct term. The bad guys have hacked into the major email systems, including gmail, which was the origin of this spam: http://www.theregister.co.uk/2008/02/25/gmail_captcha_crack/ I think you need to do some training: http://www2.b3ta.com/bigquiz/hacker-or-spacker/ Sending a link to a web site that plays loud rap music is not a friendly way to make your point. Craig - Original Message From: Craig James [EMAIL PROTECTED] To: pgsql-performance@postgresql.org Sent: Friday, 18 July, 2008 4:02:37 PM Subject: [PERFORM] Mailing list hacked by spammer? I've never gotten a single spam from the Postgres mailing list ... until today. A Chinese company selling consumer products is using this list. I have my filters set to automatically trust this list because it has been so reliable until now. It would be really, really unfortunate if this list fell to the spammers. Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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] Mailing list hacked by spammer?
On Fri, Jul 18, 2008 at 10:40:33AM -0700, Craig James wrote: Yes, hack is the correct term. The bad guys have hacked into the major email systems, including gmail, which was the origin of this spam: http://www.theregister.co.uk/2008/02/25/gmail_captcha_crack/ The simple fact is that, as long as we don't reject completely all mail from any unsubscribed user, some spam will occasionally get through. It's humans who have to do the moderation, and sometimes we hit the wrong button. Sorry. (Moreover, the trick of foiling captchas and using compromised machines all over the Internet to send spam is hardly hacking the list.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] Mailing list hacked by spammer?
Glyn Astill wrote: Most likely just a forged header or something, hardly hacked though is it. Yes, hack is the correct term. The bad guys have hacked into the major email systems, including gmail, which was the origin of this spam: http://www.theregister.co.uk/2008/02/25/gmail_captcha_crack/ I think you need to do some training: http://www2.b3ta.com/bigquiz/hacker-or-spacker/ Sending a link to a web site that plays loud rap music is not a friendly way to make your point. Craig Whatever. I see you clicked on the link then, even though it came from a 'hacked' mailing list :-) weren't you the chap that couldn't figure out how to use the slony tools and threw a wobbler at the developers ... __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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] 3ware vs Areca
Jeffrey Baker writes: Their firmware is, frankly, garbage. In more than one instance we have had the card panic when a disk fails, which is obviously counter to the entire purpose of a RAID. I have had simmilar problems with 3ware 9550 and 9650 cards. Undre FreeBSD I have seen constant crashes under heavy loads. Used to think it was just FreeBSD, but saw a thread on StorageReview where the same was happening under Linux. controllers from our database server and replaced them with HP P800s. How is that working out? Which RAID level? SAS/SATA? -- 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] An obvious index not being used
Daniele Varrazzo writes: I suspect the foo.account_id statistical data are not used at all in query: the query planner can only estimate the number of accounts to look for, not You mentioned you bumped your default_statistics_target. What did you increase it to? My data sets are so strange that anything less than 350 gives many bad plans. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
