[SQL] Postgres performance
Hi, I understand this is an super-older thread!! note: i like postgres and not mysql! I'm a Postgres server user: I've postgres 7.2 and 8.0 in many servers and I've tested performance Postgres vs MySQL and I see Postgres is very slow. But my question is: why? Cannot Postgres team make faster because cannot change internal architecture? Ok, postgres supports SUB-query and many super-feature, but mySQL now supports it and is faster! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Postgres 8 - problem: invalid input syntax for integer
Hi, In previous version di Postgres (7.2) I used this table: CREATE TABLE tablename (id serial, field int1, field2 text); Now this query work: UPDATE tablename SET field1=''; (NOTE: implicit conversion to 0) UPDATE tablename SET field2=''; (this cause of simple code-generation query - I don't know what's field type) Now in postgres 8 this don't work. Why ?(ok, it's the ufficial documentation but I don't understand... why? it's so comfortable!) Can someone help me to create a CAST to re-use this feature? Thank you! Mauro ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Postgres 8 - problem: invalid input syntax for integer
> What number does '' represent?
'No response' value...
> Does that mean a string of '/2' should equal your number divided by two?
right, but it is never required.
> If not, why not?
because I use it to GROUP BY values.
> Who is providing an empty string where you've asked for a number, and
> why not trap this error (or store a NULL)?
You are certainly right. My problem concerns the compatibility of code
among postgres 8 and 7.2 that I wanted to maintain. The existing code
(data analysis) exploits the particularity that the null ('') becomes
0 (ok, no comment :) ) logically wrong but practically perfect!
[...CAST CODE...]
Thank you for the explicit-cast code, but I want reproduce it in
'database level' so I don't use explicit cast but IMPLICIT; everytime
updating integer fields with '' values it cast to (0 or NULL).
Best regards, Mauro
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Postgres performance
> Not always, AFAICT. The four most common reasons why PG tests slower > than Mysql are: > 1. You haven't configured or have misconfigured PostgreSQL. > 2. You are testing a MySQL-tuned application (lots of small, simple > queries, no views, no subselects etc) > 3. You are only testing one connection (try 10,20,50 simultaneous users > and see who wins then). > 4. You are not testing the transaction-safe storage systems in MySQL > > See if you can answer some of the questions above and I'm sure we'll be > able to get your database server running smoothly. Hi, I've used the benchmark http://genecensus.org/yeast/cluster/database/mysql/sql-bench/ (ok, it's without bench on views, sub-select, transaction,..) The database files are in stripe (RAID 0) on two SATA hd (transfer rate 50Mb), the filesystem is reiserfs (3.6 format - with checks off), no optimation on I/O scheduler, DBMS are in default configuration (so I don't benefit nobody). Total time: Pgsql: 7h 20' MySQL: 14' (!!) This is the configuration where is running Postgres 8.0 and MySql: Linux version 2.6.9-1.667 ([EMAIL PROTECTED]) (gcc version 3.4.2 20041017 (Red Hat 3.4.2-6.fc3)) #1 Tue Nov 2 14:50:10 EST 2004 Memory: 1024128k/1047744k available (2389k kernel code, 22900k reserved, 1276k d ata, 164k init) CPU: L1 I Cache: 64K (64 bytes/line), D cache 64K (64 bytes/line) CPU: L2 Cache: 512K (64 bytes/line) CPU: AMD Athlon(tm) 64 Processor 3000+ stepping 00 powernow-k8: Found 1 AMD Athlon 64 / Opteron processors (version 1.00.09b) powernow-k8: BIOS error - no PSB hda: SAMSUNG SP0411N, ATA DISK drive hda: max request size: 1024KiB hda: 78242976 sectors (40060 MB) w/2048KiB Cache, CHS=16383/255/63, UDMA(133) ata1: SATA max UDMA/133 cmd 0xE800 ctl 0xE402 bmdma 0xD400 irq 193 ata2: SATA max UDMA/133 cmd 0xE000 ctl 0xD802 bmdma 0xD408 irq 193 cpuinfo processor : 0 vendor_id : AuthenticAMD cpu family : 15 model : 12 model name : AMD Athlon(tm) 64 Processor 3000+ stepping: 0 cpu MHz : 2002.652 cache size : 512 KB fpu : yes fpu_exception : yes cpuid level : 1 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm 3dnowext 3dnow bogomips: 3932.16 TLB size: 1088 4K pages clflush size: 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual power management: ts fid vid ttp The analisys: TestPostgres (sec) Mysql (sec) alter_table_add 1 34 alter_table_drop27 connect 36 4 connect+select_1_row45 5 connect+select_simple 38 4 count 21 5 count_distinct 9 7 count_distinct_217 11 count_distinct_big 87 26 count_distinct_group57 16 count_distinct_group_on_key 19 7 count_distinct_group_on_key_parts 56 16 count_distinct_key_prefix 38 6 count_group_on_key_parts7 7 count_on_key226 53 create+drop 145 3 create_MANY_tables 50 3 create_index1 1 create_key+drop 98 5 create_table0 0 delete_all 13 2 delete_all_many_keys11 10 delete_big 0 0 delete_big_many_keys11 10 delete_key 6 1 drop_index 0 0 drop_table 0 drop_table_when_MANY_tables 23 2 insert 177 24 insert_duplicates 59 6 insert_key 695 21 insert_many_fields 84 2 insert_select_1_key 6 1 insert_select_2_keys8 1 min_max 9 3 min_max_on_key 114527 multiple_value_insert 1 order_by_big25 19 order_by_big_key19 14 order_by_big_key2 17 14 order_by_big_key_desc 19 15 order_by_big_key_diff 17 18 order_by_big_key_prefix 17 15 order_by_key2_diff 2 2 order_by_key_prefix 0 1 order_by_range 1 1 outer_join 40 8 outer_join_found38 8 outer_join_not_found37
Re: [SQL] Postgres performance
Hi, thanks a lot! you are rigth, but I did read your message ;) Yes, 1- I misconfigured PostgreSQL (I thought that was already configured in base to the released version - Fedora Core 3 64bit). 2- The bench is, clearly after your precisations, an MySQL tuned application tests. 3- I think the bench test only one connection, I didn't see (in a fast reading) no threading request in the bench code to simulate users requests. 4- I didn't test transaction-safe (that isn't used explicitly in my application) I understand it isn't simple.. I use the dbms in data analysis environment and the more time is spent in query (php is 0.1%) with more sub-selects and maybe there's, in the same time, from 1 to 1000 users insert/update data. I tests the dbms with my data analysis framework simulating an super-extensive request. Do you know where I can find an tutorial to configure hardware dependent Postgres internal values? Thx, best regards,Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgres performance
Hi Richard, thank you for your apreciated answers!!! - start quote - Well, do you care whether your data is consistent or not? If not, you don't need transactions. - end quote - I don't require transaction because the query aren't complex and update a single tuple (in SELECT transactions are useless) - start quote - You'll find inserts/updates with lots of users is where PostgreSQL works well compared to other systems. - end quote - Uhhmm.. this is interesting... - tutorial links - Thx, now I read it and test an hardware tuned configuration... I read that is not very simple... :O Another question: - why postgres release aren't already configured (hardware tuning)? isn't possible configure it during installation? - why postgres use a new process for every query ? (mySQL, if I'm not wrong, use threads... I think its faster) - why connection time is slower? (compared to mySQL)? - why postgres require analyze? (mySQL, if I'm not wrong, don't require it) Yours answers will be very apreciated! Thx ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Postgres performance
--- PFC <[EMAIL PROTECTED]> wrote: > You mean, you have no foreign keys in your database ? > In SELECT they are definitely useful (think select > for update, isolation > level serializable...) No, I haven't foreign keys in the older version, in that new I've it... however I manage relations from app code (PHP)... doesn't MYSQL allow to use 'foreign keys' in sure and fast way then? > > - why postgres use a new process for every query ? > > (mySQL, if I'm not wrong, use threads... I think > its > > faster) > > Not for every query, for every CONNECTION. > You are using persistant connections are you. Are you ? I'm using PHP and every user (can be from 1 user to 100 users) must connect to the database... do you know how I can use persistant connection? I think it's impossible... I'm wrong? > > > - why connection time is slower? (compared to > mySQL)? > > This is of no importance as everyone uses > persistent connections anyway. See last answer... > > - why postgres require analyze? (mySQL, if I'm not > > wrong, don't require it) > > Yours answers will be very apreciated! Thx > > So it has a planner which knows what it's doing ;) > instead of just > guessing in the dark. > > And MySQL requires analyze too (read the docs), > optimize table which > looks like vacuum to me, and sometimes repair > table... Ok... they are conceptually implemented in the same mode... Thx a lot! Now my ideas about pg & mySQL are cleared (and corrects ;) ) Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Postgres performance
> > No, I haven't foreign keys in the older version, > in > > that new I've it... however I manage relations > from > > app code (PHP)... > > Really ? > In my experience this is a sure way to get > inconsistencies slowly > creeping into your database, and you also get a load > of funky concurrency > issues. Yes, you are rigth... my insert/update are very simple and without problems and so I think to use 'foreign key' coded to make faster/simpler the management and don't overloading the db (and use exception code management )... but I had a problem with pgSQL because server was very busy and the same query was replicate (because users refresh the page... :( ) so now I've foreign keys... > Now, in PHP, you can use mysql_pconnect instead of > mysql_connect to get a > persistent connection. mod_php keeps a pool of > connections. The same thing > probably applies for postgres, but as I don't use it > with PHP (only with > Python) I can't tell. Look in the docs for > "persistent connections". mmm...This is interesting... I see it's usefull but there are some 'warnings' about use it (see http://it.php.net/manual/en/features.persistent-connections.php) : "Note, however, that this can have some drawbacks if you are using a database with connection limits that are exceeded by persistent child connections. If your database has a limit of 16 simultaneous connections, and in the course of a busy server session, 17 child threads attempt to connect, one will not be able to. If there are bugs in your scripts which do not allow the connections to shut down (such as infinite loops), the database with only 16 connections may be rapidly swamped. Check your database documentation for information on handling abandoned or idle connections." What about it? (i think it's the same with mySQL...) I don't know how users can connect to... 1, 10, 1000... I must create a pool with 1000 connections? is this fine? if connections aren't released I must reset manually, it is dangerous... do you think? Thx! Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres performance
> > Yes, you are rigth... my insert/update are very > simple > > and without problems and so I think to use > 'foreign > > key' coded to make faster/simpler the management > and > > don't overloading the db (and use exception code > > management )... but I had a problem with pgSQL > because > > server was very busy and the same query was > replicate > > (because users refresh the page... :( ) so now > I've > > foreign keys... > > It's a typical case, program being stopped between > insertion of parent > and child row. Although in this case FK's is not the > solution, > transactions are. I insert a tuple (in the table answers) if the key 'iduser->idquestion' don't exists but when the key exists I update the value; this is coded in php because I have thought that otherwise I must use functions... there's no parent/child rows... how can I use transactions here? Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Query history file
Hi, I've installed a Postgres 8.0. There's a history file with all executed queries? Thanks! ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Query history file
> From the > server side, if you enable 'log_statement' all > queries will go into the > server logs. Thank you, I enabled log_statement = all log_duration = true It's beautiful! :) ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(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
[SQL] php wrapper
Hi, I need a PHP wrapper for PostgreSQL... I found 1000 small "+/- identicals" wrappers but incompleted There's an "ufficial" or an suggested PHP wrapper? ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 3: 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
[SQL] Postgres 8.0.0 - unknown log string
Hi, I found in my postges 8.0 logs (/var/lib/pgsql/data/pg_log/postgresql-Thu.log) LOG: incomplete startup packet LOG: incomplete startup packet very very times. What's it? I did't found answers in the postgres documentation. Can someone explain me about it? Thanks, Mauro B. ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] PHP postgres connections
Hi, I need to connect to 2 differents Postgres 8.0.0 databases located in the same machine using the same PHP script with an "db wrapper object" instance (pg_Connect)... simply a PHP page with contemporarily 2 database connections... What's the best practice ? Can I use however persistent connections ? Thanks, Mauro B. ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PHP postgres connections
Hi, Thank for your answers.
I asked here because I had thought the problem was in
how Postgres manage connections.
Sorry
--- Yasir Malik <[EMAIL PROTECTED]> ha scritto:
> > Hi,
> > I need to connect to 2 differents Postgres 8.0.0
> > databases located in the same machine using the
> same
> > PHP script with an "db wrapper object" instance
> > (pg_Connect)... simply a PHP page with
> contemporarily
> > 2 database connections...
> >
> I don't think this is the right place to ask this,
> but there's an example
> on php.net for using the pg_connect():
> $dbconn = pg_connect("dbname=mary");
> //connect to a database named "mary"
>
> $dbconn2 = pg_connect("host=localhost port=5432
> dbname=mary");
> // connect to a database named "mary" on "localhost"
> at port "5432"
>
> $dbconn3 = pg_connect("host=sheep port=5432
> dbname=mary user=lamb
> password=foo");
> //connect to a database named "mary" on the host
> "sheep" with a username
> and password
>
> $conn_string = "host=sheep port=5432 dbname=test
> user=lamb password=bar";
> $dbconn4 = pg_connect($conn_string);
> //connect to a database named "test" on the host
> "sheep" with a username
> and password
> ?>
>
> I don't know if that answers your question.
>
> > Can I use however persistent connections ?
> >
> pg_pconnect() works the same way.
>
> Regards,
> Yasir
>
___
Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam,
Giochi, Rubrica
Scaricalo ora!
http://it.messenger.yahoo.it
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Get max value from an comma separated string
Hi, I've a field that contain values-comma-separated like A) 1;2;3;;5 -- ;2;;4;5 but also B) 12;34;18 how I can get the max value? For A I tried: SELECT max(array_upper(string_to_array(answer,';'),1)) FROM values; and work fine, but for B case I don't find a solution like SELECT max(string_to_array(answer,';')) FROM values; Any ideas? Thanks for any hint Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.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
