Обсуждение: Fwd: Performance Tuning
Hi the list,
I have a performance problem and would like to any input on how to make it perform as desired. In the DB, there are a few tables over 3 million records in postgis. When I do some operation on the tables, e.g. CREATE INDEX, it takes hours without results. I believe there must be something wrong within the DB. However, I have not figured it out. Could anyone point me to the right direction to tune and configured the database efficiently?
Thanks in advance
John
On Mon, Nov 17, 2008 at 11:21 AM, John Zhang <johnzhang06@gmail.com> wrote: > > > Hi the list, > > I have a performance problem and would like to any input on how to make it > perform as desired. In the DB, there are a few tables over 3 million records > in postgis. When I do some operation on the tables, e.g. CREATE INDEX, it > takes hours without results. I believe there must be something wrong within > the DB. However, I have not figured it out. Could anyone point me to the > right direction to tune and configured the database efficiently? Assuming it's creating GIN indexes, and possibly even if not, index creation is very dependent on having a large enough work_mem for it to fit a large portion of the data it's working on in memory. Try cranking up work_mem before index creation. Note that you don't have to do this in postgresql.conf and reload, you can just do it in the session you're in: set work_mem=512000; create index blah blah blah;
On Mon, 17 Nov 2008, Scott Marlowe wrote: > On Mon, Nov 17, 2008 at 11:21 AM, John Zhang <johnzhang06@gmail.com> wrote: >> >> >> Hi the list, >> >> I have a performance problem and would like to any input on how to make it >> perform as desired. In the DB, there are a few tables over 3 million records >> in postgis. When I do some operation on the tables, e.g. CREATE INDEX, it >> takes hours without results. I believe there must be something wrong within >> the DB. However, I have not figured it out. Could anyone point me to the >> right direction to tune and configured the database efficiently? > > Assuming it's creating GIN indexes, and possibly even if not, index > creation is very dependent on having a large enough work_mem for it to > fit a large portion of the data it's working on in memory. Try > cranking up work_mem before index creation. Note that you don't have > to do this in postgresql.conf and reload, you can just do it in the > session you're in: > > set work_mem=512000; > create index blah blah blah; Doesn't he want to change maintenance_work_mem instead of work_mem for index creation? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
On Mon, Nov 17, 2008 at 11:36 AM, Jeff Frost <jeff@frostconsultingllc.com> wrote: > On Mon, 17 Nov 2008, Scott Marlowe wrote: > >> On Mon, Nov 17, 2008 at 11:21 AM, John Zhang <johnzhang06@gmail.com> >> wrote: >>> >>> >>> Hi the list, >>> >>> I have a performance problem and would like to any input on how to make >>> it >>> perform as desired. In the DB, there are a few tables over 3 million >>> records >>> in postgis. When I do some operation on the tables, e.g. CREATE INDEX, it >>> takes hours without results. I believe there must be something wrong >>> within >>> the DB. However, I have not figured it out. Could anyone point me to the >>> right direction to tune and configured the database efficiently? >> >> Assuming it's creating GIN indexes, and possibly even if not, index >> creation is very dependent on having a large enough work_mem for it to >> fit a large portion of the data it's working on in memory. Try >> cranking up work_mem before index creation. Note that you don't have >> to do this in postgresql.conf and reload, you can just do it in the >> session you're in: >> >> set work_mem=512000; >> create index blah blah blah; > > Doesn't he want to change maintenance_work_mem instead of work_mem for index > creation? I hate it when my brain is thinking maintenance_work_mem and my fingers are typing work_mem. You're right. It was maintenance_work_mem.