On Thu, Aug 07, 2008 at 12:28:56AM -0400, bofh wrote: > I'm looking at a project that I'm trying to run on openbsd. All that > box will have is postgresql. At this time, it's just 2 programmers > and 1 sysadmin type person that's involved, no DBAs, so apologies if > the questions are... too simplistic. > > And I realize if I want to maximize performance, I need to examine > OSes as well. But at this point, I want to explore what is the > biggest postgresql server I can run under openbsd. If at all > possible, I want to run everything in memory. > > What I'm looking for is what is the biggest database I can run on a 4 > socket (4 core per socket) AMD motherboard with 64GB or 128GB of Ram > using a standard kernel?
PostgreSQL scales very well, but it depends a bit on what the data in the database is and what types of queries you'll run on it. If all you have are simple records (numbers and small bits of text) the number of records can be huge for little bits of diskspace. On the other hand, if you use lots of big records in your database (for example images) size quickly increases. The database at my work uses approximately 700MB for ~1000 images (for the web, so the images are not that large). The size of the database is also dependant on the number of concurrent updates: each new or updated record is created in new diskspace, and old records are cleaned up only periodically, and only if they are not in use by any transaction. As for running everything in memory: you'd probably want to maximize the file-system buffers. I'd especially look into the inode cache, since in my experience, inode lookups are the most expensive. If you are using a hardware raid with a backup battery, you can turn on write-caching, so the writes to disk are grouped together. > For example, what should shmmax be set to? > http://developer.postgresql.org/pgdocs/postgres/kernel-resources.html > recommends several hundred megabytes. postgresql.conf says that if I increase the number of connections, I would need to have 400 bytes per connection, and I'd need to increase shared_buffers, which is 16kB per connection. So it depends a bit on the number of connections that you wish to support. > I understand I may need to recompile the kernel. Any recommendations > for something that can run on 64/128GB ram, 16 cpus, running only > postgresql? I think shm settings are changeable via sysctl. So I think running a generic.mp should do the trick. But I have no experience with large databases with lots of concurrency under OpenBSD. I imagine that on a 64-bit platform, you don't need to recompile a kernel just to be able to use more shm memory. > Thanks in advance. I'm exploring what is possible at this point. I > realize I haven't given out more information, but I don't have that in > hand yet. But I'm interested in finding out what my top end is. > Thanks! I think the top end is actually dependant on the data contained in the database. Ciao, Ariane
