[PERFORM] Bad performance using ODBC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I have a performance problem using postgresql when the connection is made via ODBC with a windows machine using the latests ODBC drivers (Windows) and PostgreSQL 7.3.3 (Linux). The queries made by my Visual Basic program are very very simple. It queries with as Select if a record exists and if so, it reduces stock with an Update. For the benchmarks I do it 200 times. If I test it against an Access database (located in a SMB server) it spends 3 seconds but against PostgreSQL 17 !! Exactly the same test programmed in C (with pgsql libraries) and run within the same machine or another Linux spends less than a second!! So the problem seems to be whether with the ODBC drivers or with Windows ODBC itself. Are there any parameters in the ODBC drivers that might help reducing that big overhead added or do you have any suggestions to speed it up? Thanks in advance! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQE/FV3pTK7ZP4pDOHcRAmVWAJ9KF/YyKmuBZcidV3FK2gESaX25NwCgjABx 6WhA0HgC7oxF7VFJeczIrgE= =3H+u -END PGP SIGNATURE- ---(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
Re: [ODBC] [PERFORM] Bad performance using ODBC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A Dimecres 16 Juliol 2003 16:38, Tom Lane va escriure: Albert Cervera Areny [EMAIL PROTECTED] writes: I have a performance problem using postgresql when the connection is made via ODBC with a windows machine using the latests ODBC drivers (Windows) and PostgreSQL 7.3.3 (Linux). Do you have logging turned on in the ODBC driver? I recall hearing that that adds a heck of a lot of overhead... After trying too many things I've finally been able to make it run in just 1 or 2 seconds. I simply had to change the recordset type and set it to dbOpenSnapshot (This one doesn't show changes made to the database once it's been open) instead of the default dbDynaset (much more powerful but unnecessary in this application). Take note that though it might seem obvious the performance loss against Access isn't that much and thus VB users aren't probably used to change the recordset type. I think It would be nice a note with this performance benchmarks (2 seconds against 15) in the Mini-Howto on Accessing PostgreSQL from Visual Basic. I'll contact Dave Page directly in case he finds it interesting. I haven't seen any speed improvements desabling logging but thanks for your suggestion anyway! regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQE/FstfTK7ZP4pDOHcRArepAJ9rIhOKtztuPORbGkrVTOfC4UmUOQCeJ00u UxJegkvrs4TL3QVXNun3iFs= =itG7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Performance problems with 8.1.1 compared to 7.4.7
Hello, we have a PostgreSQL for datawarehousing. As we heard of so many enhancements for 8.0 and 8.1 versions we dicided to upgrade from 7.4 to 8.1. I must say that the COPY FROM processes are much faster now from 27 to 17 minutes. Some queries where slower, but the performance problems were solved by increasing work_mem to 8192. However, now we have a query that is much slower with 8.1 compared to 7.4. The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4 seconds the following tries. We're not experts at all but we can't see anything strange with the differences of EXPLAIN in the queries. Below I paste the query and the EXPLAIN output. Does somebody have a clue of what could be the cause of this big difference in performance? Many thanks in advance. SELECT lpad(c.codigo,6,'0'), MIN(c.nombre), SUM( CASE WHEN ( res.hora_inicio = time '00:00' AND res.hora_inicio time '16:00' ) THEN (CASE WHEN res.importe_neto IS NOT NULL THEN res.importe_neto ELSE 0 END) ELSE 0 END ) AS p1, SUM( CASE WHEN ( res.hora_inicio = time '00:00' AND res.hora_inicio time '16:00' ) THEN (CASE WHEN res.cantidad_servida IS NOT NULL THEN res.cantidad_servida ELSE 0 END) ELSE 0 END ) AS p2, SUM( CASE WHEN ( res.hora_inicio = time '16:00' AND res.hora_inicio time '23:59' ) THEN (CASE WHEN res.importe_neto IS NOT NULL THEN res.importe_neto ELSE 0 END) ELSE 0 END ) AS p3 SUM( CASE WHEN ( res.hora_inicio = time '16:00' AND res.hora_inicio time '23:59' ) THEN (CASE WHEN res.cantidad_servida IS NOT NULL THEN res.cantidad_servida ELSE 0 END) ELSE 0 END ) AS p4 SUM(CASE WHEN res.importe_neto IS NOT NULL THEN res.importe_neto ELSE 0 END) AS total, SUM(CASE WHEN res.cantidad_servida IS NOT NULL THEN res.cantidad_servida ELSE 0 END) AS total_lineas FROM clientes c LEFT JOIN ( SELECT la.cliente as cliente, es.hora_inicio as hora_inicio, la.albaran as albaran, la.cantidad_servida as cantidad_servida, la.importe_neto as importe_neto FROM lineas_albaranes la LEFT JOIN escaner es ON la.albaran = es.albaran WHERE la.fecha_albaran = '20-12-2005' AND la.empresa = 1 AND la.indicador_factura = 'F' ) AS res ON c.codigo = res.cliente, provincias p WHERE p.codigo = c.provincia AND p.nombre='NAME' AND EXISTS(SELECT 1 FROM lineas_albaranes la WHERE la.cliente=c.codigo AND la.fecha_albaran (date '20-12-2005' - interval '2 month') AND la.fecha_albaran = '20-12-2005' AND la.empresa=1 AND la.indicador_factura='F') GROUP BY c.codigo ORDER BY nom; PostgreSQL 8.1.1: QUERY PLAN -- Sort (cost=333769.99..333769.99 rows=2 width=61) Sort Key: min((c.nombre)::text) - GroupAggregate (cost=37317.41..333769.98 rows=2 width=61) - Nested Loop (cost=37317.41..333769.83 rows=2 width=61) Join Filter: (inner.codigo = outer.provincia) - Merge Left Join (cost=37315.27..333758.58 rows=405 width=65) Merge Cond: (outer.codigo = inner.cliente) - Index Scan using clientes_pkey on clientes c (cost=0.00..296442.28 rows=405 width=40) Filter: (subplan) SubPlan - Bitmap Heap Scan on lineas_albaranes la (cost=138.99..365.53 rows=1 width=0) Recheck Cond: ((cliente = $0) AND ((indicador_factura)::text = 'F'::text)) Filter: ((fecha_albaran '2005-10-20 00:00:00'::timestamp without time zone) AND (fecha_albaran = '2005-12-20'::date)AND (empresa = 1)) - BitmapAnd (cost=138.99..138.99 rows=57 width=0) - Bitmap Index Scan on lineas_albaranes_cliente_idx (cost=0.00..65.87 rows=11392 width=0) Index Cond: (cliente = $0) - Bitmap Index Scan on lineas_albaranes_indicador_factura_idx (cost=0.00..72.87 rows=11392 width=0) Index Cond: ((indicador_factura)::text = 'F'::text) -
Re: [PERFORM] Performance problems with 8.1.1 compared to 7.4.7
A Dimarts 27 Desembre 2005 18:13, Michael Fuhr va escriure: On Tue, Dec 27, 2005 at 05:09:28PM +0100, Albert Cervera Areny wrote: However, now we have a query that is much slower with 8.1 compared to 7.4. The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4 seconds the following tries. We're not experts at all but we can't see anything strange with the differences of EXPLAIN in the queries. Below I paste the query and the EXPLAIN output. Could you post the EXPLAIN ANALYZE output of the query on both systems? That'll show how accurate the planner's estimates are. Have you run ANALYZE (or VACUUM ANALYZE) on the tables in both versions? The row count estimates in the 8.1.1 query differ from those in the 7.4.7 query. Are the two versions using the same data set? Are your configuration settings the same in both versions? You mentioned increasing work_mem, but what about others like effective_cache_size, random_page_cost, and shared_buffers? Hey, thank you for your fast response, I found what the problem was. I thought the settings were the same but work_mem was still higher in 7.4, 30Mb, so I increased 8.1 to 30Mb and it worked faster, down to 17 seconds the first time, 2.5 seconds for the others. Are there any rules of thumb to let a begginer give reasonable values to these parameters? Not only work_mem, but also random_page_cost, and so on. Are there any tests one can run to determine good values? Thanks a lot! -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(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: [PERFORM] Storing Digital Video
A Dimecres 01 Febrer 2006 01:32, Rodrigo Madera va escriure: I am concerned with performance issues involving the storage of DV on a database. I though of some options, which would be the most advised for speed? 1) Pack N frames inside a container and store the container to the db. 2) Store each frame in a separate record in the table frames. 3) (type something here) Thanks for the help, What if you store meta data in the database and use some PL/Python/Java/Perl functions to store and retrieve video files from the server. The function would store files to the files system, not a table. This avoids the need for a file server for your application while making your relational queries fast. Any experiences/thoughts on this solution? Rodrigo ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] copy and postgresql.conf
Hi William, which PostgreSQL version are you using? Newer (8.0+) versions have some important performance improvements for the COPY command. Also, you'll notice significant improvements by creating primary foreign keys after the copy command. I think config tweaking can improve key and index creation but I don't think you can improve the COPY command itself. There are also many threads in this list commenting on this issue, you'll find it easely in the archives. A Dimarts 14 Febrer 2006 10:44, FERREIRA, William (VALTECH) va escriure: hi, i load data from files using copy method. Files contain between 2 and 7 millions of rows, spread on 5 tables. For loading all the data, it takes 40mn, and the same processing takes 17mn with Oracle. I think that this time can be improved by changing postgresql configuration file. But which parameters i need to manipulate and with which values ? Here are the specifications of my system : V250 architecture sun4u 2xCPU UltraSparc IIIi 1.28 GHz. 8 Go RAM. Regards. Will This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Security Notice: all e-mail, sent to or from this address, may be accessed by someone other than the recipient, for system management and security reasons. This access is controlled under Regulation of Investigatory Powers Act 2000, Lawful Business Practises. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] copy and postgresql.conf
Sorry, COPY improvements came with 8.1 (http://www.postgresql.org/docs/whatsnew) A Dimarts 14 Febrer 2006 14:26, FERREIRA, William (VALTECH) va escriure: thanks, i'm using postgresql 8.0.3 there is no primary key and no index on my tables regards -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] la part de Albert Cervera Areny Envoyé : mardi 14 février 2006 12:38 À : pgsql-performance@postgresql.org Objet : Re: [PERFORM] copy and postgresql.conf Hi William, which PostgreSQL version are you using? Newer (8.0+) versions have some important performance improvements for the COPY command. Also, you'll notice significant improvements by creating primary foreign keys after the copy command. I think config tweaking can improve key and index creation but I don't think you can improve the COPY command itself. There are also many threads in this list commenting on this issue, you'll find it easely in the archives. A Dimarts 14 Febrer 2006 10:44, FERREIRA, William (VALTECH) va escriure: hi, i load data from files using copy method. Files contain between 2 and 7 millions of rows, spread on 5 tables. For loading all the data, it takes 40mn, and the same processing takes 17mn with Oracle. I think that this time can be improved by changing postgresql configuration file. But which parameters i need to manipulate and with which values ? Here are the specifications of my system : V250 architecture sun4u 2xCPU UltraSparc IIIi 1.28 GHz. 8 Go RAM. Regards. Will This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Security Notice: all e-mail, sent to or from this address, may be accessed by someone other than the recipient, for system management and security reasons. This access is controlled under Regulation of Investigatory Powers Act 2000, Lawful Business Practises. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Security Notice: all e-mail, sent to or from this address, may be accessed by someone other than the recipient, for system
Re: [PERFORM] Table Inheritence and Partioning
Currently foreign keys don't work that way. You will need your data to be in table persons if you want the foreign key to work correctly. Otherwise you could create your own trigger to ensure the foreign key restriction you need. I'm no expert at all and it seems there are non trivial issues that have to be solved in order to make foreign keys behave in the way you'd expect. A Dimecres 17 Gener 2007 11:42, [EMAIL PROTECTED] va escriure: Hi, We are having 3 tables; 1. persons -- Base table and no data will be inserted in this table. 2. Person1 -- Inherited table from persons all data will be inserted in this table. 3. PersonalInfo -- which is storing all personal information of the persons and is having the foreign key relationship with the persons table. When we try to insert the data in the personalInfo table it is throwing the error stating the primary key does not contain the given value. But, if I try to select from the persons table it is showing the records from its inherited tables as well. Can anybody tell me what might be the problem here? Or else any help regarding the same will be of very much help. Following is the table structure; - Create Table persons ( name varchar, age int, dob varchar, constraint pKey primary key(name) ); create table person1 ( ) inherits(persons); Create table personalInfo ( name varchar, contact_id int, contact_addr varchar, constraint cKey primary key(contact_id), constraint fKey foreign key(name) references persons(name) ); --- --- Thanks In Advance, Ramachandra B.S. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance of count(*)
As you can see, PostgreSQL needs to do a sequencial scan to count because its MVCC nature and indices don't have transaction information. It's a known drawback inherent to the way PostgreSQL works and which gives very good results in other areas. It's been talked about adding some kind of approximated count which wouldn't need a full table scan but I don't think there's anything there right now. A Dijous 22 Març 2007 11:53, Andreas Tille va escriure: Hi, I just try to find out why a simple count(*) might last that long. At first I tried explain, which rather quickly knows how many rows to check, but the final count is two orders of magnitude slower. My MS_SQL server using colleague can't believe that. $ psql InfluenzaWeb -c 'explain SELECT count(*) from agiraw ;' QUERY PLAN --- Aggregate (cost=196969.77..196969.77 rows=1 width=0) - Seq Scan on agiraw (cost=0.00..185197.41 rows=4708941 width=0) (2 rows) real0m0.066s user0m0.024s sys 0m0.008s $ psql InfluenzaWeb -c 'SELECT count(*) from agiraw ;' count - 4708941 (1 row) real0m4.474s user0m0.036s sys 0m0.004s Any explanation? Kind regards Andreas. -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] Bad RAID1 read performance
Hi, after doing the dd tests for a server we have at work I obtained: Read: 47.20 Mb/s Write: 39.82 Mb/s Some days ago read performance was around 20Mb/s due to no readahead in md0 so I modified it using hdparm. However, it seems to me that being it a RAID1 read speed could be much better. These are SATA disks with 3Gb of RAM so I did 'time bash -c dd if=/dev/zero of=bigfile bs=8k count=786432 sync'. File system is ext3 (if read many times in the list that XFS is faster), but I don't want to change the file system right now. Modifing the readahead from the current 1024k to 2048k doesn't make any difference. Are there any other tweaks I can make? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Bad RAID1 read performance
Hardware isn't very good I believe, and it's about 2-3 years old, but the RAID is Linux software, and though not very good the difference between reading and writing should probably be greater... (?) Would you set 512Kb readahead on both drives and RAID? I tried various configurations and none seemed to make a big difference. It seemed correct to me to set 512kb per drive and 1024kb for md0. A Dimecres 30 Maig 2007 16:09, Luke Lonergan va escriure: This sounds like a bad RAID controller - are you using a built-in hardware RAID? If so, you will likely want to use Linux software RAID instead. Also - you might want to try a 512KB readahead - I've found that is optimal for RAID1 on some RAID controllers. - Luke On 5/30/07 2:35 AM, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, after doing the dd tests for a server we have at work I obtained: Read: 47.20 Mb/s Write: 39.82 Mb/s Some days ago read performance was around 20Mb/s due to no readahead in md0 so I modified it using hdparm. However, it seems to me that being it a RAID1 read speed could be much better. These are SATA disks with 3Gb of RAM so I did 'time bash -c dd if=/dev/zero of=bigfile bs=8k count=786432 sync'. File system is ext3 (if read many times in the list that XFS is faster), but I don't want to change the file system right now. Modifing the readahead from the current 1024k to 2048k doesn't make any difference. Are there any other tweaks I can make? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(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: [PERFORM] Bad RAID1 read performance
As you suggested with two threads I get 42.39 Mb/s in one and 40.70 Mb/s in the other one, so that's more than 80Mb/s. That's what I expected with a single thread, so thanks for the information. It seems I will have to buy better hard drives if I want increased performance... A Dimecres 30 Maig 2007 22:13, Luke Lonergan va escriure: Not for one thread/process of I/O. Mirror sets can nearly double the read performance on most RAID adapters or SW RAID when using two or more thread/processes, but a single thread will get one drive worth of performance. You should try running two simultaneous processes during reading and see what you get. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Creating and updating table using function parameter reference
A Dimecres 13 Febrer 2008 15:25, Linux Guru va escriure: I want to create and update two tables in a function such as below, but using parameters as tablename is not allowed and gives an error. Is there any way I could achieve this? You're looking for EXECUTE: http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN CREATE OR REPLACE FUNCTION test ( t1 text,t2 text ) RETURNS numeric AS $$ declare temp1 text; declare temp2 text; begin temp1=t1; temp2=t2; select product, (case when sum(pd) 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp into temp2 from temp1 as dummy group by dummy.product,dummy.totalclaimsgroup,dummy.avgmems,dummy.months; update temp1 as t set GPPP=(select gppp from temp2 as dummy where dummy.product=t.product), end $$ LANGUAGE plpgsql -- ERROR: syntax error at or near $1 LINE 1: ...en sum(gd)/sum(pd)*100 else 0 end ) as gppp from $1 as dum... ^ QUERY: select product, (case when sum(pd) 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp from $1 as dummy group by dummy.product, dummy.totalclaimsgroup,dummy.avgmems,dummy.months CONTEXT: SQL statement in PL/PgSQL function test near line 10 ** Error ** ERROR: syntax error at or near $1 SQL state: 42601 Context: SQL statement in PL/PgSQL function test near line 10 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Creating and updating table using function parameter reference
You need the string concatenation operator ||. Take a look at http://www.postgresql.org/docs/8.3/static/functions-string.html By the way, this is off-topic in this list please, post general non-performance questions to pgsql-general. A Dijous 14 Febrer 2008 13:35, Linux Guru va escriure: I still cannot pass tablename, what is wrong? Is this the right way? CREATE OR REPLACE FUNCTION test ( t1 text,t2 text ) RETURNS numeric AS $$ declare temp1 text; declare temp2 text; declare cmd text; declare t2row RECORD; begin temp1=t1; temp2=t2; cmd='select product, (case when sum(pd) 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp from ' temp1 ' as dummy group by dummy.product,dummy.totalclaimsgroup, dummy.avgmems,dummy.months'; execute cmd into t2row --After executing above, I need here to update table t1 end; $$ LANGUAGE plpgsql ERROR: syntax error at or near $1 LINE 2: from ' $1 ' as dummy group by dummy.product,dummy.totalcla... ^ QUERY: SELECT 'select product, (case when sum(pd) 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp from ' $1 ' as dummy group by dummy.product,dummy.totalclaimsgroup, dummy.avgmems,dummy.months' CONTEXT: SQL statement in PL/PgSQL function test near line 9 ** Error ** ERROR: syntax error at or near $1 SQL state: 42601 Context: SQL statement in PL/PgSQL function test near line 9 On Wed, Feb 13, 2008 at 8:23 PM, Albert Cervera Areny [EMAIL PROTECTED] wrote: A Dimecres 13 Febrer 2008 15:25, Linux Guru va escriure: I want to create and update two tables in a function such as below, but using parameters as tablename is not allowed and gives an error. Is there any way I could achieve this? You're looking for EXECUTE: http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL -STATEMENTS-EXECUTING-DYN CREATE OR REPLACE FUNCTION test ( t1 text,t2 text ) RETURNS numeric AS $$ declare temp1 text; declare temp2 text; begin temp1=t1; temp2=t2; select product, (case when sum(pd) 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp into temp2 from temp1 as dummy group by dummy.product,dummy.totalclaimsgroup,dummy.avgmems,dummy.months ; update temp1 as t set GPPP=(select gppp from temp2 as dummy where dummy.product=t.product), end $$ LANGUAGE plpgsql -- ERROR: syntax error at or near $1 LINE 1: ...en sum(gd)/sum(pd)*100 else 0 end ) as gppp from $1 as dum... ^ QUERY: select product, (case when sum(pd) 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp from $1 as dummy group by dummy.product, dummy.totalclaimsgroup,dummy.avgmems,dummy.months CONTEXT: SQL statement in PL/PgSQL function test near line 10 ** Error ** ERROR: syntax error at or near $1 SQL state: 42601 Context: SQL statement in PL/PgSQL function test near line 10 -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately
Re: [PERFORM] Controling where temporary files are created
Since 8.3 there's temp_tablespaces configuration parameter. A Dilluns 18 Febrer 2008 16:27, Nikolas Everett va escriure: Is there a way I can change where postgres writes it temporary files? My data directory is on a slow array, but we also have a fast array. I'm looking to get all the temp file creation onto the fast array. -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] count * performance issue
A Dimarts 11 Març 2008 04:11, Scott Marlowe va escriure: On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan [EMAIL PROTECTED] wrote: Hi, I have been reading this conversation for a few days now and I just wanted to ask this. From the release notes, one of the new additions in 8.3 is (Allow col IS NULL to use an index (Teodor)). Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ? SELECT COUNT(*) from table WHERE indexed_field IS NULL + SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL It really depends on the distribution of the null / not nulls in the table. If it's 50/50 there's no advantage to using the index, as you still have to check visibility info in the table itself. OTOH, if NULL (or converserly not null) are rare, then yes, the index can help. I.e. if 1% of the tuples are null, the select count(*) from table where field is null can use the index efficiently. But you'll get a sequential scan with the NOT NULL case which will end up taking more time. (Seq Scan + Index Scan Seq Scan) -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. -- 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] performance using table partitions in Postgres 8.2.6
A Dilluns 07 Abril 2008, Janet Jacobsen va escriure: no. of partitions constraint_exclusion off constraint_exclusion on 2 0.597 ms0.427 ms 4 0.653 ms0.414 ms 8 0.673 ms 0.654 ms 161.068 ms 1.014 ms 322.301 ms 1.537 ms I expected that the query time would decrease as the number of partitions increases, but that's not what I am seeing. I get better results (0.29 ms) if I simply index DMID and don't use the partitions. I see really small times here so probably the overhead that partitioning imposes isn't worth yet. Maybe with 50M rows it'll help, you could try feeding those 50M tuples and test again. -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Posible planner improvement?
I've got a query similar to this: select * from t1, t2 where t1.id 158507 and t1.id = t2.id; That took 84 minutes (the query was a bit longer but this is the part that made the difference) after a little change the query took ~1 second: select * from t1, t2 where t1.id 158507 and t2.id 158507 and t1.id = t2.id; The change is pretty simple and it seems (note I don't have a clue on how the planner works) it'd be possible for the planner to make this assumption itself. Do you think it is really feasible/appropiate? -- 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] Posible planner improvement?
A Dimecres 21 Maig 2008, Richard Huxton va escriure: Albert Cervera Areny wrote: I've got a query similar to this: select * from t1, t2 where t1.id 158507 and t1.id = t2.id; That took 84 minutes (the query was a bit longer but this is the part that made the difference) after a little change the query took ~1 second: select * from t1, t2 where t1.id 158507 and t2.id 158507 and t1.id = t2.id; Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and we'll see why it's better at the second one. Right, attached an example of such a difference. # explain analyze select * from datos_venta dv, key_conta kc where dv.codigo 158507 and kc.codigo 158507 and dv.codigo=kc.codigo; QUERY PLAN -- Hash Join (cost=7527.14..18203.25 rows=51415 width=253) (actual time=244.107..326.375 rows=11650 loops=1) Hash Cond: (dv.codigo = kc.codigo) - Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=4.801..25.601 rows=11650 loops=1) Recheck Cond: (codigo 158507) - Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=4.694..4.694 rows=11650 loops=1) Index Cond: (codigo 158507) - Hash (cost=4539.78..4539.78 rows=53788 width=136) (actual time=238.852..238.852 rows=2985 loops=1) - Bitmap Heap Scan on key_conta kc (cost=999.43..4539.78 rows=53788 width=136) (actual time=102.208..174.339 rows=2985 loops=1) Recheck Cond: (codigo 158507) - Bitmap Index Scan on key_conta_pkey (cost=0.00..999.43 rows=53788 width=0) (actual time=102.137..102.137 rows=2985 loops=1) Index Cond: (codigo 158507) Total runtime: 347.949 ms (12 rows) # explain analyze select * from datos_venta dv, key_conta kc where dv.codigo 158507 and dv.codigo=kc.codigo; QUERY PLAN -- Hash Join (cost=7737.95..21585.51 rows=154245 width=253) (actual time=2227.439..2991.082 rows=11650 loops=1) Hash Cond: (dv.codigo = kc.codigo) - Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=22.179..149.532 rows=11650 loops=1) Recheck Cond: (codigo 158507) - Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=22.086..22.086 rows=11650 loops=1 Index Cond: (codigo 158507) - Hash (cost=4481.65..4481.65 rows=161365 width=136) (actual time=2205.184..2205.184 rows=161365 loops=1) - Seq Scan on key_conta kc (cost=0.00..4481.65 rows=161365 width=136) (actual time=25.736..1351.018 rows=161365 loops=1) Total runtime: 3307.621 ms (9 rows) -- 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] Posible planner improvement?
A Dimecres 21 Maig 2008, Mark Mielke va escriure: A Dimecres 21 Maig 2008, Richard Huxton va escriure: Albert Cervera Areny wrote: I've got a query similar to this: select * from t1, t2 where t1.id 158507 and t1.id = t2.id; That took 84 minutes (the query was a bit longer but this is the part that made the difference) after a little change the query took ~1 second: select * from t1, t2 where t1.id 158507 and t2.id 158507 and t1.id = t2.id; Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and we'll see why it's better at the second one. Even if the estimates were off (they look a bit off for the first table), the above two queries are logically identical, and I would expect the planner to make the same decision for both. I am curious - what is the result of: select * from t1, t2 where t2.id 158507 and t1.id = t2.id; Is it the same speed as the first or second, or is a third speed entirely? Attached the same file with the third result at the end. The result is worst than the other two cases. Note that I've analyzed both tables but results are the same. One order of magnitude between the two first queries. If t1.id = t2.id, I would expect the planner to substitute them freely in terms of identities? Cheers, mark # explain analyze select * from datos_venta dv, key_conta kc where dv.codigo 158507 and kc.codigo 158507 and dv.codigo=kc.codigo; QUERY PLAN -- Hash Join (cost=7527.14..18203.25 rows=51415 width=253) (actual time=244.107..326.375 rows=11650 loops=1) Hash Cond: (dv.codigo = kc.codigo) - Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=4.801..25.601 rows=11650 loops=1) Recheck Cond: (codigo 158507) - Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=4.694..4.694 rows=11650 loops=1) Index Cond: (codigo 158507) - Hash (cost=4539.78..4539.78 rows=53788 width=136) (actual time=238.852..238.852 rows=2985 loops=1) - Bitmap Heap Scan on key_conta kc (cost=999.43..4539.78 rows=53788 width=136) (actual time=102.208..174.339 rows=2985 loops=1) Recheck Cond: (codigo 158507) - Bitmap Index Scan on key_conta_pkey (cost=0.00..999.43 rows=53788 width=0) (actual time=102.137..102.137 rows=2985 loops=1) Index Cond: (codigo 158507) Total runtime: 347.949 ms (12 rows) # explain analyze select * from datos_venta dv, key_conta kc where dv.codigo 158507 and dv.codigo=kc.codigo; QUERY PLAN -- Hash Join (cost=7737.95..21585.51 rows=154245 width=253) (actual time=2227.439..2991.082 rows=11650 loops=1) Hash Cond: (dv.codigo = kc.codigo) - Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=22.179..149.532 rows=11650 loops=1) Recheck Cond: (codigo 158507) - Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=22.086..22.086 rows=11650 loops=1 Index Cond: (codigo 158507) - Hash (cost=4481.65..4481.65 rows=161365 width=136) (actual time=2205.184..2205.184 rows=161365 loops=1) - Seq Scan on key_conta kc (cost=0.00..4481.65 rows=161365 width=136) (actual time=25.736..1351.018 rows=161365 loops=1) Total runtime: 3307.621 ms (9 rows) # explain analyze select * from datos_venta dv, key_conta kc where kc.codigo 158507 and dv.codigo=kc.codigo; QUERY PLAN -- Hash Join (cost=146.18..14631.18 rows=8130 width=168) (actual time=1419.986..1555.975 rows=11650 loops=1) Hash Cond: (dv.codigo = kc.codigo) - Seq Scan on datos_venta dv (cost=0.00..9776.35 rows=462735 width=59) (actual time=0.044..934.853 rows=462735 loops=1) - Hash (cost=139.09..139.09 rows=2835 width=109) (actual time=14.487..14.487 rows=2985 loops=1) - Index Scan using key_conta_pkey on key_conta kc (cost=0.00..139.09 rows=2835 width=109) (actual time=0.100..7.847 rows=2985 Index Cond: (codigo 158507) Total runtime: 1565.255 ms (7 rows) -- 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] Posible planner improvement?
A Dimecres 21 Maig 2008, Richard Huxton va escriure: Luke Lonergan wrote: The problem is that the implied join predicate is not being propagated. This is definitely a planner deficiency. IIRC only equality conditions are propagated and gt, lt, between aren't. I seem to remember that the argument given was that the cost of checking for the ability to propagate was too high for the frequency when it ocurred. Of course, what was true for code and machines of 5 years ago might not be so today. Hope this can be revisited given the huge difference in this case: 80 minutes to 1 second. -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. -- 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] [BACKUPS]Little backups
A Dilluns 21 Juliol 2008, Leví Teodoro da Silva va escriure: Hi Guys, I am developing a project with PostgreSQL and one guy from project is familiar with Oracle and did a question for me, but i could not answer, if someone could help it will be good. =) The question is : * - In oracle he makes a full backup two times in a day. In this range of time, Oracle make a lot of mini-backups, but this backups is about just the data whose have changed in this time. If the system fails, he could reconstruct the database adding the last big backup with mini-backups. Can Postgres do this ? * Yes, it can. If you need detailed information, you can take a look at http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html Regards, Leví - Brazil -- 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] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence
A Dimecres 23 Juliol 2008, Miernik va escriure: I have a PostgreSQL database on a very low-resource Xen virtual machine, 48 MB RAM. When two queries run at the same time, it takes longer to complete then if run in sequence. Is there perhaps a way to install something like a query sequencer, which would process queries in a FIFO manner, one at a time, even if a new query comes before the last one running is finished, it would not give the new query to the server before the one running now finishes? That would greatly improve performance. You didn't mention your PostgreSQL version. Since 8.3 there's synchronized scans which greatly improves performance if concurrent queries have to do a sequential scan on the same table. Of course, if queries don't hit the same table there'll be no improvements in performance... Any tips in general for running PostgreSQL on such low-resource machine? I have: shared_buffers = 5MB work_mem = 1024kB are these good values, or could perhaps changing something improve it a bit? Any other parameters to look at? -- Miernik http://miernik.name/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
