I am currently using a dual Opteron (248) single core system (RAM PC3200) and for a change I am finding that the bottleneck is not disk I/O but CPU/RAM (not sure which). The reason for this is that the most frequently accessed tables/indexes are all held in RAM and when querying the database there is almost no disk activity which is great, most of the time. However, the database is growing and this database is supporting an OLTP system where the retrieval of the data is an order of magnitude more important than the insertion and general upkeep of the data. It supports a search engine[0] and contains a reverse index, lexicon and the actual data table (currently just under 2Gb for the three tables and associated indexes). At the moment everything is working OK but I am noticing an almost linear increase in time to retrieve data from the database as the data set increases in size. Clustering knocks the access times down by 25% but it also knocks users off the website and can take up to 30 minutes which is hardly an ideal scenario. I have also considered partitioning the tables up using extendible hashing and tries to allocate the terms in the index to the correct table but after some testing I noticed no noticeable gain using this method which surprised me a bit. The actual size of the database is not that big (4Gb) but I am expecting this to increase to at least 20Gb over the next year or so. This means that search times are going to jump dramatically which also means the site becomes completely unusable. This also means that although disk access is currently low I am eventually going to run out of RAM and require a decent disk subsystem. Do people have any recommendations as to what hardware would alleviate my current CPU/RAM problem but with a mind to the future would still be able to cope with heavy disk access. My budget is about £2300/$4000 which is not a lot of money when talking databases so suggestions of a Sun Fire T2000 or similar systems will be treated with the utmost disdain ;) unless you are about to give me one to keep. -- Harry http://www.hjackson.org http://www.uklug.co.uk Before anyone asks I have considered using tsearch2.
