Re: Turning off transactions completely.
От | Francisco Reyes |
---|---|
Тема | Re: Turning off transactions completely. |
Дата | |
Msg-id | 20020116100849.X19885-100000@zoraida.natserv.net обсуждение исходный текст |
Ответ на | Re: Turning off transactions completely. ("Arsalan Zaidi" <azaidi@directi.com>) |
Список | pgsql-general |
On Tue, 8 Jan 2002, Arsalan Zaidi wrote: > Just to re-iterate. > 1. I've done quite a bit of tweaking with the WAL*, shared buffers and sort > mem over several weeks. They're about as optimum as I can get them. Could you expand on that. What makes you say they are "optimun". > 3. The queries are just about as good as I can get them and have been > throughly EXPLAIN'ed with live/large amounts of data in the tables. No IN's > used, only EXISTS (where required). I am far from an SQL expert, but it may not hurt to share with the list what are these queries. Perhaps even post them. > 4. Temp tables are used to simplify complex queries (and speed them up I > hope). Could you expand on that? > 5. RAID-0 (SCSI/HW) + Dual Proc + 1GB RAM. Linux 2.4.17(smp) (pure Linus. No > other patches). Swap is on a seperate IDE drive. How about more memory? This would certainly help substantially. What is the speed of the HDs? 10K RPM? 15K RPM? Is the Swap ever hit? Hitting Swap always hurts, but given that you put an IDE for swap it would slow you down even more. What is the speed of the CPUs? What type of memory PC100/PC133/Rambus/DDR??? How many disks on your Raid 0? Although there is much you could perhaps do through software configuration once you get to the levels you are discribing there is no substitute for fast hardware. Probably the cheapest upgrade you can do is adding more memory. This will likely help. You also need to pay close attention to your schema. You can to have your most heavily used data in a small table. To give you an example let me make up an scenario simmilar to something I did. Let's say I have a people's list(not particularly correct SQL just to give you an idea) id serial name varchar(20) last varchar(20) addres varchar(30) addres2 varchar(30) state char(2) zip int comment varchar(30) education char(1) Now let's say that I use this table extremely heavily when doing joins with other tables (i.e. OLAP type of analyses against other tables). What I did was that I broke it off into two tables and kept only the fields which I used %90+ of the time. something like id name zip Then did another table with the rest and linked by ID. I didn't do any particular time difference analyses, but I got a substantial improvement. On my actual case I had about 50 fields which totalled something on the neighborhood of 300 bytes. After breaking it up the abbreviated version of the table was less than 50 bytes. > 7. Driver app was multi-threaded. It made things worse. Expand on this. This is where the number of buffers vs the amount of memory comes into play. If by firing multiple copies you ended up hitting swap this would have made things much worse. Remember postgresql fires up processes. It doesn't use threads. >BTW, the apps jobs consists largely of firing off SQL queries >in the correct sequence; It may help if you tell us more about these queries. In particular the one you mentioned that takes 36 hours.
В списке pgsql-general по дате отправления:
Предыдущее
От: Darren FergusonДата:
Сообщение: Re: Different views with same name for different users