[PERFORM] 8 way Intel Xeon system
Hi all, Hopefully a quick query. Is there anything special I should look at when configuring either PostgreSQL and Linux to work together on an 8-way Intel P-III based system? I currently have the database system running fairly nicely on a 2 CPU Pentium-II 400 server with 512MB memory, hardware RAID with 32MB cache on the controllers, unfortunately the db file systems are RAID-5 but I am not in a position to change that. I'm mainly interested in what I should concentrate on from a Linux/PostgreSQL config point of view to see if we can take advantage of the extra CPUs. - Yes it's overkill but this is a piece of surplus hardware we don't have to buy. Perhaps some may say Linux isn't the best option for an 8 CPU server but this is what I have to work with for reasons we won't get into :-) The current usage is along the lines of a few thousands updates spread over the space of a few hours in the morning then followed by a thousand select queries to do some reporting. The server also runs an apache web server for people to access but is only used to view html files on a fairly ad-hoc basis, nothing serious on the web side of things. Currently RedHat 9 with PostgreSQL 7.3.2 installed. Thanks for your time Linz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] One table or many tables for data set
Apologies, let me clear this up a bit (hopefully) :-) The data structure looks like this: element date num1 num2 num3 num4 units There are approx 12,000 distinct elements for a total of about 6 million rows of data. The scanning technology I want to use may need a different number of rows and different columns depending on the scan formula; eg scan1 may need num1, num2 and num3 from the last 200 rows for element x scan2 may need num1, units from the last 10 rows for element y I can either do the scans and calculate what i need within SQL or drag the data out and process it outside of SQL, my preference is to go inside SQL as I've assumed that would be faster and less development work. If I went with the many tables design I would not expect to need to join between tables, there is no relationship between the different elements that I need to cater for. Cheers, Linz Castle, Lindsay wrote and snipped: I'm working on a project that has a data set of approximately 6million rows with about 12,000 different elements, each element has 7 columns of data. I'm wondering what would be faster from a scanning perspective (SELECT statements with some calculations) for this type of set up; one table for all the data one table for each data element (12,000 tables) one table per subset of elements (eg all elements that start with a in a table) I, for one, am having difficulty understanding exactly what your data looks like, so it's hard to give advice. Maybe some concrete examples of what you are calling rows, elements, and columns would help. Does each of 6 million rows have 12000 elements, each with 7 columns? Or do you mean that out of 6 million rows, there are 12000 distinct kinds of elements? Can I do anything with Indexing to help with performance? I suspect for the majority of scans I will need to evaluate an outcome based on 4 or 5 of the 7 columns of data. Again, this isn't clear to me -- but maybe I'm just being dense ;-) Does this mean you expect 4 or 5 items in your WHERE clause? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] One table or many tables for data set
Thanks Rod My explanations will be better next time. :-) -Original Message- From: Rod Taylor [mailto:[EMAIL PROTECTED] Sent: Wednesday, 23 July 2003 11:41 AM To: Castle, Lindsay Cc: Postgresql Performance Subject: Re: One table or many tables for data set On Tue, 2003-07-22 at 21:50, Rod Taylor wrote: Ok.. Unless I'm missing something, the data will be static (or near static). It also sounds as if the structure is common for elements, so you probably only want 2 tables. I misunderstood. Do what Joe suggested. ---(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
[PERFORM] One table or many tables for data set
Hi all, I'm working on a project that has a data set of approximately 6million rows with about 12,000 different elements, each element has 7 columns of data. I'm wondering what would be faster from a scanning perspective (SELECT statements with some calculations) for this type of set up; one table for all the data one table for each data element (12,000 tables) one table per subset of elements (eg all elements that start with a in a table) The data is static once its in the database, only new records are added on a regular basis. I'd like to run quite a few different formulated scans in the longer term so having efficient scans is a high priority. Can I do anything with Indexing to help with performance? I suspect for the majority of scans I will need to evaluate an outcome based on 4 or 5 of the 7 columns of data. Thanks in advance :-) Linz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
