Thread: How to increase shared mem for PostgreSQL on FreeBSD
My system is FreeBSD 4.3 with 1GHz P3, 1GB RAM, SCSI RAID 5. I read an article on PHPBuilder about optimizing PostgreSQL - one big thing was increasing shared memory. I asked on the FreeBSD mailing list and got different suggestions as to how to increase the shared memory - my main question is - do I need to rebuild my kernel? I used sysctl -w shmall=131072 to up my shared mem, then edited postgresql.conf to show: shared_buffers = 15200 sort_mem = 32168 and postgres wouldn't start. I assume that means the shared buffers is actually larger than the amount of shared mem, so it appears the sysctl didn't really do anything. If I comment out the shared_buffers line, postgres will start, but upping the sort mem doesn't help my insert speed. Can anyone help me out with how to get the shared mem increased so I can up the shared_buffers in postgres? Thanks, Joe
There is info in the Administrator's Guide that might help: http://www.postgresql.org/idocs/index.php?kernel-resources.html Bob On Thursday 13 December 2001 11:42 am, Joe Koenig wrote: > My system is FreeBSD 4.3 with 1GHz P3, 1GB RAM, SCSI RAID 5. I read an > article on PHPBuilder about optimizing PostgreSQL - one big thing was > increasing shared memory. I asked on the FreeBSD mailing list and got > different suggestions as to how to increase the shared memory - my main > question is - do I need to rebuild my kernel? I used sysctl -w > shmall=131072 to up my shared mem, then edited postgresql.conf to show: > > shared_buffers = 15200 > sort_mem = 32168 > > and postgres wouldn't start. I assume that means the shared buffers is > actually larger than the amount of shared mem, so it appears the sysctl > didn't really do anything. If I comment out the shared_buffers line, > postgres will start, but upping the sort mem doesn't help my insert > speed. Can anyone help me out with how to get the shared mem increased > so I can up the shared_buffers in postgres? Thanks, > > Joe > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Thursday 13 December 2001 10:42, Joe Koenig wrote: > My system is FreeBSD 4.3 with 1GHz P3, 1GB RAM, SCSI RAID 5. I read an > article on PHPBuilder about optimizing PostgreSQL - one big thing was > increasing shared memory. I asked on the FreeBSD mailing list and got > different suggestions as to how to increase the shared memory - my main > question is - do I need to rebuild my kernel? I used sysctl -w > shmall=131072 to up my shared mem, then edited postgresql.conf to show: > > shared_buffers = 15200 > sort_mem = 32168 That sort mem is REAL high. Have you read Bruce's tuning pages? He gives a real good description on the tradeoffs involved. http://www.ca.postgresql.org/docs/momjian/hw_performance/ > and postgres wouldn't start. I assume that means the shared buffers is > actually larger than the amount of shared mem, so it appears the sysctl > didn't really do anything. If I comment out the shared_buffers line, > postgres will start, but upping the sort mem doesn't help my insert > speed. Can anyone help me out with how to get the shared mem increased > so I can up the shared_buffers in postgres? Thanks, > > Joe > Hi, Please post the output from the following command: sysctl -a | grep shm Here is information from my primary server: FreeBSD 4.4-STABLE #1: Thu Nov 8 05:58:44 CST 2001 CPU: Pentium III/Pentium III Xeon/Celeron (845.64-MHz 686-class CPU) Origin = "GenuineIntel" Id = 0x683 Stepping = 3 real memory = 1073676288 (1048512K bytes) avail memory = 1040752640 (1016360K bytes) -- tms:postgres# sysctl -a | grep shm kern.ipc.shmmax: 128004097 kern.ipc.shmmin: 1 kern.ipc.shmmni: 512 kern.ipc.shmseg: 1024 kern.ipc.shmall: 31251 kern.ipc.shm_use_phys: 1 tms:postgres# -- Also, I just edited my kernel config file and recompiled. Please note that I've got ALOT of extra in a couple of places. This is for other applications than just PostgreSQL. Here is a section from my kernel config file. --SNIP-- # SysV stuff -- GB Mods options SYSVSHM #SYSV-style shared memory options SHMMAXPGS=31251 options SHMALL=31251 options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)" options SHMSEG=1024 options SHMMNI=512 options SHMMIN=1 options SYSVMSG #SYSV-style message queues options SYSVSEM #SYSV-style semaphores options SEMMNI=256 options SEMMNS=512 options SEMMNU=256 options SEMMAP=256 --SNIP-- And here is the section from my PG config file: --SNIP-- shared_buffers = 14336 sort_mem = 8096 --SNIP-- I hope some of this helps. GB -- GB Clark II - N5VMF - Roaming FreeBSD Admin gclarkii@VSServices.COM - Looking for extra work FreeBSD and PostgreSQL CTHULU for President - Why choose the lesser of two evils?
On Thu, 13 Dec 2001, GB Clark II wrote:
> On Thursday 13 December 2001 10:42, Joe Koenig wrote:
[Snip]
> > shared_buffers = 15200
> > sort_mem = 32168
>
> That sort mem is REAL high. Have you read Bruce's tuning pages?
They're not _that_ off-the-wall:
morannon:~/pgdata>egrep 'sort_mem|shared_buffers' postgresql.conf
sort_mem = 32168
shared_buffers = 15200
morannon:~/pgdata>ipcs
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x0052e2c1 163843 sauron 600 128688128 9
(My postgresql install runs as "sauron" - not "postgres", btw)
morannon:~/pgdata>cat /proc/sys/kernel/shmmax
134217728
morannon:~/pgdata>uname -a
Linux morannon 2.4.13 #2 Mon Oct 29 23:52:00 CST 2001 i686 unknown
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
First of all I would like to thank GB and everyone else who helped. I recompiled my kernel yesterday. I was able to increase my shared memory, but I've still yet to see any performance increase in the script I'm concerned with. The whole script takes about 27 minutes to run and inserts somewhere around 700,000 rows. I read somewhere to turn off fsync, but I also see that that is not a real safe move and won't save me much time in 7.1. When the script is solely doing inserts, it is able to insert around 200 rows per second. The inserts are wrapped in transactions in groups of 5000. It seems that I should be able to insert faster than that with my system (1GHz, 1GB RAM, RAID 5 w/10K 18GB drives). Are there other things I need to be concerned with that will help the speed? my shared_buffers is 15200 and sort_mem is at 8096 currently. Also, the script doing the inserts is written in PHP 4.1.0 - could that be slowing me, as well? Thanks, Joe GB Clark II wrote: > > On Thursday 13 December 2001 10:42, Joe Koenig wrote: > > My system is FreeBSD 4.3 with 1GHz P3, 1GB RAM, SCSI RAID 5. I read an > > article on PHPBuilder about optimizing PostgreSQL - one big thing was > > increasing shared memory. I asked on the FreeBSD mailing list and got > > different suggestions as to how to increase the shared memory - my main > > question is - do I need to rebuild my kernel? I used sysctl -w > > shmall=131072 to up my shared mem, then edited postgresql.conf to show: > > > > shared_buffers = 15200 > > sort_mem = 32168 > > That sort mem is REAL high. Have you read Bruce's tuning pages? > He gives a real good description on the tradeoffs involved. > > http://www.ca.postgresql.org/docs/momjian/hw_performance/ > > > and postgres wouldn't start. I assume that means the shared buffers is > > actually larger than the amount of shared mem, so it appears the sysctl > > didn't really do anything. If I comment out the shared_buffers line, > > postgres will start, but upping the sort mem doesn't help my insert > > speed. Can anyone help me out with how to get the shared mem increased > > so I can up the shared_buffers in postgres? Thanks, > > > > Joe > > > Hi, > > Please post the output from the following command: > > sysctl -a | grep shm > > Here is information from my primary server: > > FreeBSD 4.4-STABLE #1: Thu Nov 8 05:58:44 CST 2001 > CPU: Pentium III/Pentium III Xeon/Celeron (845.64-MHz 686-class CPU) > Origin = "GenuineIntel" Id = 0x683 Stepping = 3 > > real memory = 1073676288 (1048512K bytes) > avail memory = 1040752640 (1016360K bytes) > -- > > tms:postgres# sysctl -a | grep shm > kern.ipc.shmmax: 128004097 > kern.ipc.shmmin: 1 > kern.ipc.shmmni: 512 > kern.ipc.shmseg: 1024 > kern.ipc.shmall: 31251 > kern.ipc.shm_use_phys: 1 > tms:postgres# > -- > > Also, I just edited my kernel config file and recompiled. > Please note that I've got ALOT of extra in a couple of places. This is for > other applications than just PostgreSQL. > Here is a section from my kernel config file. > > --SNIP-- > # SysV stuff -- GB Mods > options SYSVSHM #SYSV-style shared memory > options SHMMAXPGS=31251 > options SHMALL=31251 > options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)" > options SHMSEG=1024 > options SHMMNI=512 > options SHMMIN=1 > > options SYSVMSG #SYSV-style message queues > > options SYSVSEM #SYSV-style semaphores > options SEMMNI=256 > options SEMMNS=512 > options SEMMNU=256 > options SEMMAP=256 > --SNIP-- > > And here is the section from my PG config file: > --SNIP-- > shared_buffers = 14336 > sort_mem = 8096 > --SNIP-- > > I hope some of this helps. > > GB > > -- > GB Clark II - N5VMF - Roaming FreeBSD Admin > gclarkii@VSServices.COM - Looking for extra work FreeBSD and PostgreSQL > CTHULU for President - Why choose the lesser of two evils?
You might try dropping any indexes on the tables affected while doing the inserts and then re-creating it when you're done... -p On Fri, 14 Dec 2001, Joe Koenig wrote: > First of all I would like to thank GB and everyone else who helped. I > recompiled my kernel yesterday. I was able to increase my shared memory, > but I've still yet to see any performance increase in the script I'm > concerned with. The whole script takes about 27 minutes to run and > inserts somewhere around 700,000 rows. I read somewhere to turn off > fsync, but I also see that that is not a real safe move and won't save > me much time in 7.1. When the script is solely doing inserts, it is able > to insert around 200 rows per second. The inserts are wrapped in > transactions in groups of 5000. It seems that I should be able to insert > faster than that with my system (1GHz, 1GB RAM, RAID 5 w/10K 18GB > drives). Are there other things I need to be concerned with that will > help the speed? my shared_buffers is 15200 and sort_mem is at 8096 > currently. Also, the script doing the inserts is written in PHP 4.1.0 - > could that be slowing me, as well? Thanks, > > Joe > > GB Clark II wrote: > > > > On Thursday 13 December 2001 10:42, Joe Koenig wrote: > > > My system is FreeBSD 4.3 with 1GHz P3, 1GB RAM, SCSI RAID 5. I read an > > > article on PHPBuilder about optimizing PostgreSQL - one big thing was > > > increasing shared memory. I asked on the FreeBSD mailing list and got > > > different suggestions as to how to increase the shared memory - my main > > > question is - do I need to rebuild my kernel? I used sysctl -w > > > shmall=131072 to up my shared mem, then edited postgresql.conf to show: > > > > > > shared_buffers = 15200 > > > sort_mem = 32168 > > > > That sort mem is REAL high. Have you read Bruce's tuning pages? > > He gives a real good description on the tradeoffs involved. > > > > http://www.ca.postgresql.org/docs/momjian/hw_performance/ > > > > > and postgres wouldn't start. I assume that means the shared buffers is > > > actually larger than the amount of shared mem, so it appears the sysctl > > > didn't really do anything. If I comment out the shared_buffers line, > > > postgres will start, but upping the sort mem doesn't help my insert > > > speed. Can anyone help me out with how to get the shared mem increased > > > so I can up the shared_buffers in postgres? Thanks, > > > > > > Joe > > > > > Hi, > > > > Please post the output from the following command: > > > > sysctl -a | grep shm > > > > Here is information from my primary server: > > > > FreeBSD 4.4-STABLE #1: Thu Nov 8 05:58:44 CST 2001 > > CPU: Pentium III/Pentium III Xeon/Celeron (845.64-MHz 686-class CPU) > > Origin = "GenuineIntel" Id = 0x683 Stepping = 3 > > > > real memory = 1073676288 (1048512K bytes) > > avail memory = 1040752640 (1016360K bytes) > > -- > > > > tms:postgres# sysctl -a | grep shm > > kern.ipc.shmmax: 128004097 > > kern.ipc.shmmin: 1 > > kern.ipc.shmmni: 512 > > kern.ipc.shmseg: 1024 > > kern.ipc.shmall: 31251 > > kern.ipc.shm_use_phys: 1 > > tms:postgres# > > -- > > > > Also, I just edited my kernel config file and recompiled. > > Please note that I've got ALOT of extra in a couple of places. This is for > > other applications than just PostgreSQL. > > Here is a section from my kernel config file. > > > > --SNIP-- > > # SysV stuff -- GB Mods > > options SYSVSHM #SYSV-style shared memory > > options SHMMAXPGS=31251 > > options SHMALL=31251 > > options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)" > > options SHMSEG=1024 > > options SHMMNI=512 > > options SHMMIN=1 > > > > options SYSVMSG #SYSV-style message queues > > > > options SYSVSEM #SYSV-style semaphores > > options SEMMNI=256 > > options SEMMNS=512 > > options SEMMNU=256 > > options SEMMAP=256 > > --SNIP-- > > > > And here is the section from my PG config file: > > --SNIP-- > > shared_buffers = 14336 > > sort_mem = 8096 > > --SNIP-- > > > > I hope some of this helps. > > > > GB > > > > -- > > GB Clark II - N5VMF - Roaming FreeBSD Admin > > gclarkii@VSServices.COM - Looking for extra work FreeBSD and PostgreSQL > > CTHULU for President - Why choose the lesser of two evils? > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Fri, 14 Dec 2001, Joe Koenig wrote: > concerned with. The whole script takes about 27 minutes to run and > inserts somewhere around 700,000 rows. ...... > When the script is solely doing inserts, it is able > to insert around 200 rows per second. The inserts are wrapped in > transactions in groups of 5000. It seems that I should be able to insert > faster than that with my system (1GHz, 1GB RAM, RAID 5 w/10K 18GB > drives). Are there other things I need to be concerned with that will > help the speed? my shared_buffers is 15200 and sort_mem is at 8096 > currently. Also, the script doing the inserts is written in PHP 4.1.0 - > could that be slowing me, as well? Thanks, I think if you were going to be doing such large updates that you would be better off using the copy command. As a reference I insert about 2.5 Million records in the neighborhood of 40 minutes or about 1050 inserts per second. The hardware is a 500Mhz pentinum III with 512MB ram, 8000 buffers on postgresql.conf. The drives are 2 IDE 7,2000 RPM drives on Raid 1 configuration. How many drives do you have? If you have 4 drives I would recommend you used Raid 1+0 or if your controller can't do it, then use two separate Raid 1 configurations. Also you could put the logs directory, pg_xlog I believe, in one raid set and the rest of the data on the other one. That may help on your inserts. I don't know anything about your data structures, but I think doing part of your problem may be that regular inserts update the indexes and I have been told copy doesn't. That may be a big part of your overhead. I just do a vacuum analyze when I am done copying all the data. Two quick suggestions: -Try dropping your indexes before you start the inserts -Try increasing the number of transanctions to a group of 10000. What is the lenght of your rows been inserted? The rows from the example above are about 60 bytes long. Doing a little math: 60 bytes * 1050 transactions per second = 61K/sec. The problem on my case is definitely not bandwith, but random access. I just got a 15K rpm drive today and will be putting that into a 1Gz machine with 1GB of RAM.
The situation I'm dealing with is not ideal by any means. All of the data is on a different server, and needs to be moved over to mine nightly. I guess there is a chance I could have a script connect to the other DB, export all of the data in a tab or CSV format, FTP the info over to my server and then use COPY to import all of the data. For some reason I'm more comfortable with the way it is now than with trying to transfer such a large file. I believe I would need to transfer at least 100MB of files and then run the copy. Since the FTP transfer would be involved, I can't see that saving me any time. I'll look into it though. Most of the data being transferred already exists on the current server, but the DB on the other side provides no way for me to get only the new information. No dates the record was created or updated or anything. Like I said, the situation is not at all ideal. By the way, the machine has 3 18GB Ultra3 SCSI drives. A couple people have mentioned to drop the indexes during the inserts, which I was previously doing. The largest (bytes per row, 222,000 rows) table averages around 400 bytes per row. So If my math is right, I'm looking at close to 80K/sec. Maybe I'm really doing better than I think. Once again, thanks to everyone who has replied with comments and advice. I really appreciate it. Joe Francisco Reyes wrote: > > On Fri, 14 Dec 2001, Joe Koenig wrote: > > > concerned with. The whole script takes about 27 minutes to run and > > inserts somewhere around 700,000 rows. > ...... > > When the script is solely doing inserts, it is able > > to insert around 200 rows per second. The inserts are wrapped in > > transactions in groups of 5000. It seems that I should be able to insert > > faster than that with my system (1GHz, 1GB RAM, RAID 5 w/10K 18GB > > drives). Are there other things I need to be concerned with that will > > help the speed? my shared_buffers is 15200 and sort_mem is at 8096 > > currently. Also, the script doing the inserts is written in PHP 4.1.0 - > > could that be slowing me, as well? Thanks, > > I think if you were going to be doing such large updates that you would be > better off using the copy command. As a reference I insert about 2.5 > Million records in the neighborhood of 40 minutes or about 1050 inserts > per second. The hardware is a 500Mhz pentinum III with 512MB ram, 8000 > buffers on postgresql.conf. The drives are 2 IDE 7,2000 RPM drives on Raid > 1 configuration. > > How many drives do you have? > If you have 4 drives I would recommend you used Raid 1+0 or if your > controller can't do it, then use two separate Raid 1 configurations. > > Also you could put the logs directory, pg_xlog I believe, in one > raid set and the rest of the data on the other one. That may help on your > inserts. > > I don't know anything about your data structures, but I think doing part > of your problem may be that regular inserts update the indexes and I have > been told copy doesn't. That may be a big part of your overhead. I just do > a vacuum analyze when I am done copying all the data. > > Two quick suggestions: > -Try dropping your indexes before you start the inserts > -Try increasing the number of transanctions to a group of 10000. > > What is the lenght of your rows been inserted? The rows from the example > above are about 60 bytes long. Doing a little math: 60 bytes * 1050 > transactions per second = 61K/sec. The problem on my case is definitely > not bandwith, but random access. I just got a 15K rpm drive today and will be > putting that into a 1Gz machine with 1GB of RAM.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 14 Dec 2001, Joe Koenig wrote: > The situation I'm dealing with is not ideal by any means. All of the > data is on a different server, and needs to be moved over to mine > nightly. I guess there is a chance I could have a script connect to the > other DB, export all of the data in a tab or CSV format, FTP the info > over to my server and then use COPY to import all of the data. For some > reason I'm more comfortable with the way it is now than with trying to > transfer such a large file. I believe I would need to transfer at least > 100MB of files and then run the copy. Since the FTP transfer would be > involved, I can't see that saving me any time. I'll look into it though. > Most of the data being transferred already exists on the current server, > but the DB on the other side provides no way for me to get only the new > information. No dates the record was created or updated or anything. > Like I said, the situation is not at all ideal. By the way, the machine > has 3 18GB Ultra3 SCSI drives. A couple people have mentioned to drop > the indexes during the inserts, which I was previously doing. The > largest (bytes per row, 222,000 rows) table averages around 400 bytes > per row. So If my math is right, I'm looking at close to 80K/sec. Maybe > I'm really doing better than I think. Once again, thanks to everyone who > has replied with comments and advice. I really appreciate it. > Would piping the output of pg_dump, through something like netcat, into psql work? I've never tried it, but I imagine it would work for simple one-way mirroring.... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjwaYGsACgkQwtU6L/A4vVDNYgCfSvwGw8JkAJg0ang1Tvy1ojld xUQAnijJJ/GV6LraO472IRm3Qwb0ylo/ =13oc -----END PGP SIGNATURE-----