Ulrich Wisser wrote: > Hello, > > one of our services is click counting for on line advertising. We do > this by importing Apache log files every five minutes. This results in a > lot of insert and delete statements. At the same time our customers > shall be able to do on line reporting.
What are you deleting? I can see having a lot of updates and inserts, but I'm trying to figure out what the deletes would be. Is it just that you completely refill the table based on the apache log, rather than doing only appending? Or are you deleting old rows? > > We have a box with > Linux Fedora Core 3, Postgres 7.4.2 > Intel(R) Pentium(R) 4 CPU 2.40GHz > 2 scsi 76GB disks (15.000RPM, 2ms) > > I did put pg_xlog on another file system on other discs. > > Still when several users are on line the reporting gets very slow. > Queries can take more then 2 min. If it only gets slow when you have multiple clients it sounds like your select speed is the issue, more than conflicting with your insert/deletes. > > I need some ideas how to improve performance in some orders of > magnitude. I already thought of a box with the whole database on a ram > disc. So really any idea is welcome. How much ram do you have in the system? It sounds like you only have 1 CPU, so there is a lot you can do to make the box scale. A dual Opteron (possibly a dual motherboard with dual core (but only fill one for now)), with 16GB of ram, and an 8-drive RAID10 system would perform quite a bit faster. How big is your database on disk? Obviously it isn't very large if you are thinking to hold everything in RAM (and only have 76GB of disk storage to put it in anyway). If your machine only has 512M, an easy solution would be to put in a bunch more memory. In general, your hardware is pretty low in overall specs. So if you are willing to throw money at the problem, there is a lot you can do. Alternatively, turn on statement logging, and then post the queries that are slow. This mailing list is pretty good at fixing poor queries. One thing you are probably hitting is a lot of sequential scans on the main table. If you are doing mostly inserting, make sure you are in a transaction, and think about doing a COPY. There is a lot more that can be said, we just need to have more information about what you want. John =:-> > > Ulrich > > >
Description: OpenPGP digital signature