Re: [PERFORM] Loading the entire DB into RAM
If memory serves me correctly I have seen several posts about this in the past. I'll try to recall highlights. 1. Create a md in linux sufficiently large enough to handle the data set you are wanting to store. 2. Create a HD based copy somewhere as your permanent storage mechanism. 3. Start up your PostgreSQL instance with the MD as the data store 4. Load your data to the MD instance. 5. Figure out how you will change indexes _and_ ensure that your disk storage is consistent with your MD instance. I haven't done so, but it would be interesting to have a secondary database somewhere that is your primary storage. It needn't be especially powerful, or even available. It serves as the place to generate your indexing data. You could then use SLONY to propogate the data to the MD production system. Of course, if you are updating your system that resides in ram, you should be thinking the other way. Have SLONY replicate changes to the other, permanent storage, system. Either way you do it, I can't think of an out of the box method to doing it. Somehow one has to transfer data from permanent storage to the md instance, and, likewise, back to permanent storage. Out of curiosity, what are you using as the search engine? Charles A. Landemaine wrote: I have a web server with PostgreSQL and RHEL. It hosts a search engine, and each time some one makes a query, it uses the HDD Raid array. The DB is not very big, it is less than a GB. I plan to add more RAM anyway. What I'd like to do is find out how to keep the whole DB in RAM so that each time some one does a query, it doesn't use the HDD. Is it possible, if so, how? Thanks, Charles. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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
Rodrigo Madera wrote: 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, My experience has been that this is a very bad idea. Many people want to store all sorts of data in a database such as email messages, pictures, etc... The idea of a relational database is to perform queries against data. If you are needing to just store data then store it on a disk and use the database as the indexer of the data. Keep in mind the larger the database the slower some operations become. Unless you are operating on the frame data (which you either store as blobs or hex-encoded data) I'd recommend you store the data on a hard drive and let the database store meta data about the video such as path information, run time, author, etc... We do this on an application storing close to a million images and the performance is impressive. 1. we don't have to do any sort of data manipulation storing the data in or retrieving the data out of the database. 2. our database is compact and extremely fast - it is using the database for what it was designed for - relational queries. My $0.02 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 5: don't forget to increase your free space map settings
Re: [PERFORM] Scaling further up
Quoting Andrew Sullivan <[EMAIL PROTECTED]>: > On Fri, Mar 12, 2004 at 06:25:48PM -0500, Anjan Dave wrote: > > We upgraded from 8GB to 12GB RAM a month or so ago, but even in the > > past, I've never seen the system exhaust on it's system cache (~6GB, in > > 'top'), while it's swapping. > > > > Some one had mentioned why not have the entire DB in memory? How do I > > configure that, for knowledge? > > You don't. It'll automatically be in memory if (a) you have enough > memory, (b) you don't have anything else on the machine using the > memory, and (c) it's been read at least one time. This is the preferred method, but you could create a memory disk if running linux. This has several caveats, though. 1. You may have to recompile the kernel for support. 2. You must store the database on a hard drive partition during reboots. 3. Because of #2 this option is generally useful if you have static content that is loaded to the MD upon startup of the system. You could have some fancy methodology of shutting down the system and then copying the data to a disk-based filesystem, but this is inherently bad since at any moment a power outage would erase any updates changes. The option is there to start with all data in memory, but in general, this is probablt not what you want. Just an FYI. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
