Обсуждение: [GENERAL][HACKERS]data fragmentation
Hi, i have a theorical question. i was thought that data fragmentation can cause a loss of performance when retrieving data from a database. Some DBMS solved this with dbspaces, but postgresql doesn't support this concept. so, pgsql databases tend to suffer from data fragmentation? if yes, what is the solution you recommend? also i was thought that even when DBMS support dbspaces DELETEing records may cause data fragmentation anyway. so, can i think of DELETE statement as a double-edged sword? it is indifferent in pgsql - it doesn't support dbspaces anyway? thanks in advance, Jaime Casanova (el_vigia) _________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail
Hi, I too had the same problem; There was one query which used to take a very long time. What I did was, I took a backup ofthe whole database. Reinstalled postgres on a different mount point and restored the data back into the new database. Nowmy queries are running faster. Try it. All the very best. Somasekhar -----Original Message----- From: Jaime Casanova [mailto:el_vigia_ec@hotmail.com] Sent: Friday, December 12, 2003 3:07 AM To: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org; pgsql-admin@postgresql.org Subject: [GENERAL][ADMIN][HACKERS]data fragmentation Hi, i have a theorical question. i was thought that data fragmentation can cause a loss of performance when retrieving data from a database. Some DBMS solved this with dbspaces, but postgresql doesn't support this concept. so, pgsql databases tend to suffer from data fragmentation? if yes, what is the solution you recommend? also i was thought that even when DBMS support dbspaces DELETEing records may cause data fragmentation anyway. so, can i think of DELETE statement as a double-edged sword? it is indifferent in pgsql - it doesn't support dbspaces anyway? thanks in advance, Jaime Casanova (el_vigia) _________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Fri, Dec 12, 2003 at 09:59:23AM +0530, Somasekhar Bangalore wrote: > Hi, > > I too had the same problem; There was one query which used to take a > very long time. What I did was, I took a backup of the whole database. > Reinstalled postgres on a different mount point and restored the data > back into the new database. Now my queries are running faster. Try it. > All the very best. You could instead apply CLUSTER to the affected tables. (I'm assuming you already apply VACUUM periodically and REINDEX as appropiate) -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Everybody understands Mickey Mouse. Few understand Hermann Hesse. Hardly anybody understands Einstein. And nobody understands Emperor Norton."
Hello, An quicker option would be to use rsync (on a stopped database of course). You can rsync to a new directory (off the filesystem) and then reformat the data filesystem and move it back. J Somasekhar Bangalore wrote: >Hi, > >I too had the same problem; There was one query which used to take a very long time. What I did was, I took a backup ofthe whole database. Reinstalled postgres on a different mount point and restored the data back into the new database. Nowmy queries are running faster. Try it. All the very best. > >Somasekhar > > >-----Original Message----- >From: Jaime Casanova [mailto:el_vigia_ec@hotmail.com] >Sent: Friday, December 12, 2003 3:07 AM >To: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org; pgsql-admin@postgresql.org >Subject: [GENERAL][ADMIN][HACKERS]data fragmentation > >Hi, > >i have a theorical question. i was thought that data fragmentation can cause >a >loss of performance when retrieving data from a database. Some DBMS solved >this >with dbspaces, but postgresql doesn't support this concept. >so, pgsql databases tend to suffer from data fragmentation? >if yes, what is the solution you recommend? > >also i was thought that even when DBMS support dbspaces DELETEing records >may >cause data fragmentation anyway. >so, can i think of DELETE statement as a double-edged sword? >it is indifferent in pgsql - it doesn't support dbspaces anyway? > >thanks in advance, >Jaime Casanova (el_vigia) > >_________________________________________________________________ >The new MSN 8: smart spam protection and 2 months FREE* >http://join.msn.com/?page=features/junkmail > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com