Re: [PERFORM] Help specifying new web server/database machine
On Thu, 2005-06-09 at 17:44 +0100, Rory Campbell-Lange wrote: Hi All. Thanks very much for Joshua, William, Bjoern and Matthew's replies. I've now looked at the famous Server for 7K thread. In my case we are looking for a server for around 3000 pounds (UK); the server is to be an all-purpose web and database server. Processor: First of all I noted that we were intending to use Opteron processors. I guess this isn't a straightforward choice because I believe Debian (our Linux of choice) doesn't have a stable AMD64 port. Yes it does. Now sarge has become the new stable release, the amd64 version has also become stable. It doesn't have as many packages as the i386 port, but those it has will be supported by the Debian security team. Look at the debian-amd64 mailing list for more information. It only has PostgreSQL 7.4. To run 8.0, download the source packages from unstable and build them yourself. You need postgresql-8.0 and postgresql-common; if you also have an existing database to upgrade you need postgresql and postgresql-7.4. However some users on this list suggest that Opterons work very well even in a 32 bit environment. You can treat the machine as a 32bit machine and install the i386 version of Debian; it will run rather slower than with 64 bit software. Oliver Elphick ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Select-Insert-Query
On Tue, 2004-03-02 at 00:49, [EMAIL PROTECTED] wrote: what is the most performant way to select for example the first 99 rows of a table and insert them into another table... at the moment i do this: for userrecord in select * from table where account_id = a_account_id and counter_id = userrecord.counter_id and visitortable_id between a_minid and a_maxid limit 99 loop Using LIMIT without ORDER BY will give a selection that is dependent on the physical location of rows in the table; this will change whenever one of them is UPDATEd. insert into lastusers (account_id, counter_id, date, ip, hostname) values(a_account_id,userrecord.counter_id,userrecord.date ,userrecord.ip,userrecord.hostname); end loop; i think limit is a performance killer, is that right? but what to do instead I'm sure it is the loop that is the killer. Use a query in the INSERT statement: INSERT INTO lastusers (account_id, counter_id, date, ip, hostname) SELECT * FROM table WHERE account_id = a_account_id AND counter_id = userrecord.counter_id AND visitortable_id between a_minid and a_maxid ORDER BY date DESC LIMIT 99; -- Oliver Elphick [EMAIL PROTECTED] LFIX Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
