Re: [GENERAL] Querry and SMP mechine
On Sun, Aug 07, 2005 at 01:04:51PM +0300, Michael Ben-Nes wrote: Hello Im using postgres 8.0.1 on Debian Sarge with 2 XEON 2.4 HT. doing cat /etc/cpuinfo shows that linux sees 4 cpu ( the HT effect ). I noticed that when im runing a big query only one of the CPUs become loaded. Is it normal behavior ? am i missing something ? Yep, PostgreSQL uses a single backend for each client, so if you want to use all four processors, you need to be running four queries simultaneously... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp4A0b1FmVC4.pgp Description: PGP signature
[GENERAL] Querry and SMP mechine
Hello Im using postgres 8.0.1 on Debian Sarge with 2 XEON 2.4 HT. doing cat /etc/cpuinfo shows that linux sees 4 cpu ( the HT effect ). I noticed that when im runing a big query only one of the CPUs become loaded. Is it normal behavior ? am i missing something ? Thanks -- -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Cel: 972-52-8555757 Fax: 972-4-6990098 http://www.canaan.net.il -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Querry and SMP mechine
Im using postgres 8.0.1 on Debian Sarge with 2 XEON 2.4 HT. doing cat /etc/cpuinfo shows that linux sees 4 cpu ( the HT effect ). I noticed that when im runing a big query only one of the CPUs become loaded. Is it normal behavior ? am i missing something ? Yes, that is normal. Only one CPU per concurrent query is used. If your load is mostly single or dual sessions runinng queries, you will probably get a better performance if you disable hyperthreading. If you normally have a lot of concurrent sessions, HT will probably help your total performance. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Querry and SMP mechine
Martijn van Oosterhout wrote: On Sun, Aug 07, 2005 at 01:04:51PM +0300, Michael Ben-Nes wrote: Hello Im using postgres 8.0.1 on Debian Sarge with 2 XEON 2.4 HT. doing cat /etc/cpuinfo shows that linux sees 4 cpu ( the HT effect ). I noticed that when im runing a big query only one of the CPUs become loaded. Is it normal behavior ? am i missing something ? Yep, PostgreSQL uses a single backend for each client, so if you want to use all four processors, you need to be running four queries simultaneously... You mean select 1, select 2 sleect 3 will run on the same cpu becouse they submited from the same PSQL session ? Or each select will be run in turn on the most idle cpu ? Is this true for all the other OS or just for Linux ? (Bsd, solaris... ) Thanks Have a nice day, -- -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Cel: 972-52-8555757 Fax: 972-4-6990098 http://www.canaan.net.il -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Querry and SMP mechine
On Sun, Aug 07, 2005 at 02:15:50PM +0300, Michael Ben-Nes wrote: Yep, PostgreSQL uses a single backend for each client, so if you want to use all four processors, you need to be running four queries simultaneously... You mean select 1, select 2 sleect 3 will run on the same cpu becouse they submited from the same PSQL session ? Or each select will be run in turn on the most idle cpu ? No, they are run in the same single process and run on one cpu. The OS can ofcourse shift it to whichever CPU it thinks is most appropriate. It can shift it in the middle of a query too, but that's not up to PostgreSQL. Is this true for all the other OS or just for Linux ? (Bsd, solaris... ) A single client uses a single CPU at a time, but it's not tied to that particular CPU. That applies to all supported platforms... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpr7ayws6S5G.pgp Description: PGP signature
[GENERAL] postgresql Secure Mode
Hi, i have a probem,. I am trying to configure postgresql in sure way, I have made the following thing: 1. - I have created the certificate and put this in the directory it data 2. - given him privileges to the user postgresql for the certificate 3. - in the file postgresql.conf, modify the parameters ssl = true to use md5 = true 4. - in the file pg_hba.conf adds one it lines this way hostssl all all 192.168.0.0/255.255.255.0 md5 5. - stsrt postresql But when trying to connect me says that the user cannot authenticate some idea thank you very much -- cordialmente, Ing. Mario Soto Cordones -- cordialmente, Ing. Mario Soto Cordones ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Remopte connection to POstgreSQL via ODBC
Hello. I just read all about TCP/IP, pg_hba.conf, listen_adresses and similar topics regarding remote TCP/IP connections, but where can I find explanations about how to remotely connect using ODBC andDSN-less connection string. For example, how to connect to PostgreSQL server from remote computer using MS Access and DSN-less connection string ? Since now, I successfully conected from MS Access using DSN-less connection string but from the same machine. What if server is on a remote computer ? Is there any guide? Thanks in advance, Zlatko
Re: [GENERAL] timestamp default values
On Sun, Aug 07, 2005 at 12:47:19AM -0400, Tom Lane wrote: BTW: at least with our current interpretation of these datatypes, the only type that is sensible for a now()-like function to return is timestamptz. Not plain timestamp; that cannot be considered to represent a well-defined instant at all. BTW, do we have a type which stores the TZ it originally had? -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) No hay hombre que no aspire a la plenitud, es decir, la suma de experiencias de que un hombre es capaz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Remopte connection to POstgreSQL via ODBC
Google is your friend, follow this http://archives.postgresql.org/pgsql-odbc/2004-02/msg00029.php Poul Zlatko Matić wrote: Hello. I just read all about TCP/IP, pg_hba.conf, listen_adresses and similar topics regarding remote TCP/IP connections, but where can I find explanations about how to remotely connect using ODBC and DSN-less connection string. For example, how to connect to PostgreSQL server from remote computer using MS Access and DSN-less connection string ? Since now, I successfully conected from MS Access using DSN-less connection string but from the same machine. What if server is on a remote computer ? Is there any guide? Thanks in advance, Zlatko
Re: [GENERAL] postgresql Secure Mode
Hi, i have a probem,. I am trying to configure postgresql in sure way, I have made the following thing: 1. - I have created the certificate and put this in the directory it data 2. - given him privileges to the user postgresql for the certificate 3. - in the file postgresql.conf, modify the parameters ssl = true to use md5 = true 4. - in the file pg_hba.conf adds one it lines this way hostssl all all 192.168.0.0/255.255.255.0 md5 5. - stsrt postresql But when trying to connect me says that the user cannot authenticate some idea Hmm. Are you certain you had the password for the user stored somewhere? e.g... 1. Stored in the file $HOME/.pgpass? 2. Stored in some relevant environment variable ($PGPASS)? 3. Included into the DSN (e.g. - 'dbname=this password=something') Having the cert doesn't automatically authenticate anything... -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://cbbrowne.com/info/rdbms.html Rules of the Evil Overlord #153. My Legions of Terror will be an equal-opportunity employer. Conversely, when it is prophesied that no man can defeat me, I will keep in mind the increasing number of non-traditional gender roles. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] tsearch2: very slow queries
'k, I'm obviously doing something wrong, since my experiences with sites
like fts.postgresql.org indicate things should be *alot* faster then I'm
getting ...
I have a *very* simple table:
=# \d article_tsearch
Table public.article_tsearch
Column | Type | Modifiers
+--+---
article_id | integer |
idxft1 | tsvector |
Indexes:
at_idxft1_idx gist (idxft1)
rblog=# select count(1) from article_tsearch;
count
643072
(1 row)
rblog=# select count(1) from article_tsearch where idxFT1 @@
to_tsquery('1dvd');;
count
---
1681
(1 row)
But, it just seems to take so long to do the query itself:
# explain analyze select * from article_tsearch where idxFT1 @@ to_tsquery('1dvd')
order by rank(idxFT1, to_tsquery('1dvd')) desc limit 26;
QUERY PLAN
-
Limit (cost=2625.53..2625.60 rows=26 width=36) (actual
time=20164.262..20164.597 rows=26 loops=1)
- Sort (cost=2625.53..2627.14 rows=644 width=36) (actual
time=20164.257..20164.298 rows=26 loops=1)
Sort Key: rank(idxft1, '\'1\' \'dvd\''::tsquery)
- Index Scan using at_idxft1_idx on article_tsearch
(cost=0.00..2595.48 rows=644 width=36) (actual time=29.476..20153.530 rows=1681
loops=1)
Index Cond: (idxft1 @@ '\'1\' \'dvd\''::tsquery)
Filter: (idxft1 @@ '\'1\' \'dvd\''::tsquery)
Total runtime: 20166.326 ms
(7 rows)
If it is, then I'm obviously overlooking something key here ... now, I've
read through the docs in contrib/tsearch2/docs, and don't *think* I've
missed anything obvious ... it seems fairly straightforward ...
Is there something else I should be doing to speed the query up any? Or
is this fairly normal?
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tsearch2: very slow queries
Marc G. Fournier wrote: 'k, I'm obviously doing something wrong, since my experiences with sites like fts.postgresql.org indicate things should be *alot* faster then I'm getting ... Well the first thing I would ask is are you running 8.0? My testing shows that Tsearch is pretty abysmal if you are not running 8.0. At least with very large tables. I have a *very* simple table: =# \d article_tsearch Table public.article_tsearch Column | Type | Modifiers +--+--- article_id | integer | idxft1 | tsvector | Indexes: at_idxft1_idx gist (idxft1) rblog=# select count(1) from article_tsearch; count 643072 (1 row) Is there something else I should be doing to speed the query up any? Or is this fairly normal? Considering the number of rows I am not that surprised but I would be curious to know what type of HD you have? Also correct me if I am wrong but gist indexes are typically very large. Do you have enough work_mem/sort_mem to keep them from going to disk? Sincerely, Joshua D. Drake Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] tsearch2: very slow queries
On Sun, 7 Aug 2005, Joshua D. Drake wrote: Marc G. Fournier wrote: 'k, I'm obviously doing something wrong, since my experiences with sites like fts.postgresql.org indicate things should be *alot* faster then I'm getting ... Well the first thing I would ask is are you running 8.0? My testing shows that Tsearch is pretty abysmal if you are not running 8.0. At least with very large tables. This is one thing I was fearing, especially with the work that Teodor and gang have been putting into it for 8.1 :( Unfortunately, we're currently stuck with 7.4.6 for this, so that is one thing I'm going to have to take into consideration ... Considering the number of rows I am not that surprised but I would be curious to know what type of HD you have? Also correct me if I am wrong but gist indexes are typically very large. Do you have enough work_mem/sort_mem to keep them from going to disk? I'm currently playing in a non-production environment (ie. my desktop machine) just to get a feel for things ... our main server for this is a proper 4G of RAM, sort_mem bump'd up quite nicely, and file system spread over multiple spindles ... Right now, I'm just playing with / learning the tsearch stuff, so am more looking at a 'this is the worst case scenario on my box', and this improves things ... not perfect, but anything I can improve here, I know will be easier to improve on the production server :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] linking access using ODBC driver with geometric data
Jeff D. Hamann [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sorry for the seemingly novice posting, but I could find a solution for this on the web so far... I've been developing a database using postgresql (and loving it) and have started running into problems with attempting to update records using the ODBC connection. The tables that contain geometric fields are causing the problems. Whenever I try to update a record that contains a geometric field (any field) I get the following error message: ODBC -- update on a linked table 'public_locations' failed. ERROR: invalid input syntax for type point: (100),(100) (#7) When I attempt to update the record without the quotes, as if typing 100,100 in the point field, I get the error: record has been changed... blah, blah, blah... You mean the old record has been changed by another user? If so, try setting the row versioning driver option to true. Is there some trick to being able to use the geometric data types if you're going to interface with Access using the ODBC driver? Jeff. --- Jeff D. Hamann Forest Informatics, Inc. PO Box 1421 Corvallis, Oregon USA 97339-1421 541-754-1428 [EMAIL PROTECTED] www.forestinformatics.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
