Re: db performance
In response to Robert Fitzpatrick [EMAIL PROTECTED]: We have several postfix transport gateways on different networks all working with local amavisd-maia+SA using a remote postgresql backend at one location. I am getting delays in the queues on a gateway server at a remote site since we added more memory to the db server. After seeing the issues of SA TIMED OUT in the logs, seems this was happening prior to the upgrade, but never to the extent, I guess, to delay much mail. Queues on a gateway of the same network as the db server working fine, but messages with the timeouts differ from one server to another. After reading tuning, it suggests the SWAP should be double RAM. According to dmesg... real memory = 3220635648 (3071 MB) avail memory = 3150565376 (3004 MB) we have 3GB of RAM available with actually 4GB physical RAM installed? Anyway, the SWAP is only 2GB, even with the average usage shown here, will increasing SWAP to 6-8GB help? last pid: 49828; load averages: 0.23, 0.21, 0.18up 8+18:33:08 15:42:23 184 processes: 5 running, 158 sleeping, 21 waiting CPU states: 2.6% user, 0.0% nice, 1.0% system, 0.0% interrupt, 96.4% idle Mem: 446M Active, 1646M Inact, 236M Wired, 138M Cache, 112M Buf, 30M Free Swap: 2048M Total, 164K Used, 2048M Free Adding swap is unlikely to help you, as you're not really using much memory. I also have assumed in the past that db performance could be better if I get off the system RAID-5 and put it on 1+0? The system has 4 SATA drives. That will speed things up if IO is your bottleneck, but you've not demonstrated that. Which machine in this system is the bottleneck? Are the Amavis machines timing out, or is the PostgreSQL server too slow? If I understand your description, it sounds like a network problem to me ... i.e., machines not on the same gateway as the PG server are experience slow network response (or dropped packets?) that's causing amavis to time out while trying to talk to PG. I would suggest investigating there first. -- Bill Moran http://www.potentialtech.com ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
After reading tuning, it suggests the SWAP should be double RAM. According to dmesg... installing database on RAID-5 or asking if to add swap (when almost none is used)? what is more stupid? whould we vote? ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
On Thu, 2008-01-17 at 15:53 -0500, Bill Moran wrote: In response to Robert Fitzpatrick [EMAIL PROTECTED]: I also have assumed in the past that db performance could be better if I get off the system RAID-5 and put it on 1+0? The system has 4 SATA drives. That will speed things up if IO is your bottleneck, but you've not demonstrated that. Which machine in this system is the bottleneck? Are the Amavis machines timing out, or is the PostgreSQL server too slow? If I understand your description, it sounds like a network problem to me ... i.e., machines not on the same gateway as the PG server are experience slow network response (or dropped packets?) that's causing amavis to time out while trying to talk to PG. I would suggest investigating there first. The SA timeouts I'm finding on all the servers. Even the db server that runs it's own amavisd process for backup purposes and some minor domains just to make sure it is there and working. This is why I think you're right, the pgsql db is too slow. Would I possibly see dramatic differences in speed with the RAID switch? -- Robert ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
In response to Robert Fitzpatrick [EMAIL PROTECTED]: On Thu, 2008-01-17 at 15:53 -0500, Bill Moran wrote: In response to Robert Fitzpatrick [EMAIL PROTECTED]: I also have assumed in the past that db performance could be better if I get off the system RAID-5 and put it on 1+0? The system has 4 SATA drives. That will speed things up if IO is your bottleneck, but you've not demonstrated that. Which machine in this system is the bottleneck? Are the Amavis machines timing out, or is the PostgreSQL server too slow? If I understand your description, it sounds like a network problem to me ... i.e., machines not on the same gateway as the PG server are experience slow network response (or dropped packets?) that's causing amavis to time out while trying to talk to PG. I would suggest investigating there first. The SA timeouts I'm finding on all the servers. Even the db server that runs it's own amavisd process for backup purposes and some minor domains just to make sure it is there and working. This is why I think you're right, the pgsql db is too slow. Would I possibly see dramatic differences in speed with the RAID switch? You're not even close to proposing a solution yet. Take a deep breath and take a little time to understand the problem before you start throwing hardware at it. I don't know anything about amavisd's usage of databases. If it's doing a lot of small writes, then it's likely that getting off RAID 5 will make a marked difference. You need to investigate more, though. Otherwise you're just randomly flipping switches. Watching top on the PG machine, how much RAM is in use? What is the average CPU usage when you see timeouts? Run top -m io in another terminal and see if a lot of IO is happening on the part of PostgreSQL ... is it reads or writes? And what tuning have you done to PostgreSQL? PG doesn't perform well without tuning. Install the pg_buffercache addon and see if you've got enough shared_buffers to get decent performance out of it. Are you running vacuum and analyze frequently? Turn on query timing and watch the logs to see what queries are taking up time. Read the following links and follow the advice therein: http://www.powerpostgresql.com/PerfList http://www.revsys.com/writings/postgresql-performance.html -- Bill Moran http://www.potentialtech.com ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
On Thu, 2008-01-17 at 22:19 +0100, Ivan Voras wrote: Robert Fitzpatrick wrote: real memory = 3220635648 (3071 MB) avail memory = 3150565376 (3004 MB) we have 3GB of RAM available with actually 4GB physical RAM installed? If you're using a 32-bit (i386) kernel you need PAE. Or switch to 64-bit (amd64). Yes, this is something else I've found I need to do to these i386 servers since we upgraded the memory, I guess I'll get PAE in the kernel and switch the RAID, should provide quite a difference, yes? Some people have suggested the PAE drivers may not be stable with my hardware. -- Robert ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
db performance
We have several postfix transport gateways on different networks all working with local amavisd-maia+SA using a remote postgresql backend at one location. I am getting delays in the queues on a gateway server at a remote site since we added more memory to the db server. After seeing the issues of SA TIMED OUT in the logs, seems this was happening prior to the upgrade, but never to the extent, I guess, to delay much mail. Queues on a gateway of the same network as the db server working fine, but messages with the timeouts differ from one server to another. After reading tuning, it suggests the SWAP should be double RAM. According to dmesg... real memory = 3220635648 (3071 MB) avail memory = 3150565376 (3004 MB) we have 3GB of RAM available with actually 4GB physical RAM installed? Anyway, the SWAP is only 2GB, even with the average usage shown here, will increasing SWAP to 6-8GB help? last pid: 49828; load averages: 0.23, 0.21, 0.18up 8+18:33:08 15:42:23 184 processes: 5 running, 158 sleeping, 21 waiting CPU states: 2.6% user, 0.0% nice, 1.0% system, 0.0% interrupt, 96.4% idle Mem: 446M Active, 1646M Inact, 236M Wired, 138M Cache, 112M Buf, 30M Free Swap: 2048M Total, 164K Used, 2048M Free I also have assumed in the past that db performance could be better if I get off the system RAID-5 and put it on 1+0? The system has 4 SATA drives. All servers running FreeBSD 6.2 and latest ports of postfix+amavisd-maia +SA+ClamAV. Thanks for any input. -- Robert ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
Robert Fitzpatrick wrote: real memory = 3220635648 (3071 MB) avail memory = 3150565376 (3004 MB) we have 3GB of RAM available with actually 4GB physical RAM installed? If you're using a 32-bit (i386) kernel you need PAE. Or switch to 64-bit (amd64). signature.asc Description: OpenPGP digital signature
Re: db performance
In response to Robert Fitzpatrick [EMAIL PROTECTED]: On Thu, 2008-01-17 at 22:19 +0100, Ivan Voras wrote: Robert Fitzpatrick wrote: real memory = 3220635648 (3071 MB) avail memory = 3150565376 (3004 MB) we have 3GB of RAM available with actually 4GB physical RAM installed? If you're using a 32-bit (i386) kernel you need PAE. Or switch to 64-bit (amd64). Yes, this is something else I've found I need to do to these i386 servers since we upgraded the memory, I guess I'll get PAE in the kernel and switch the RAID, should provide quite a difference, yes? Some people have suggested the PAE drivers may not be stable with my hardware. I don't recommend PAE simply because amd64 works so well and PAE is a holdover hack. That being said, if your hardware is i386 only, you're stuck with PAE. -- Bill Moran http://www.potentialtech.com ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
On Thu, 2008-01-17 at 16:34 -0500, Bill Moran wrote: In response to Robert Fitzpatrick [EMAIL PROTECTED]: I don't know anything about amavisd's usage of databases. If it's doing a lot of small writes, then it's likely that getting off RAID 5 will make a marked difference. I believe this is the case with SA learning on and auto-whitelisting. Disabling things like that are my last resort. You need to investigate more, though. Otherwise you're just randomly flipping switches. I really appreciate the pointers! Watching top on the PG machine, how much RAM is in use? What is the average CPU usage when you see timeouts? Run top -m io in another terminal and see if a lot of IO is happening on the part of PostgreSQL ... is it reads or writes? I see mainly postgres in the top 8-10 with mainly WRITEs of mainly less than 100 regularly, mostly less than 30 WRITES at a time. And what tuning have you done to PostgreSQL? PG doesn't perform well without tuning. Install the pg_buffercache addon and see if you've got enough shared_buffers to get decent performance out of it. Are you running vacuum and analyze frequently? Turn on query timing and watch the logs to see what queries are taking up time. Read the following links and follow the advice therein: http://www.powerpostgresql.com/PerfList http://www.revsys.com/writings/postgresql-performance.html This is what I have setup now, thanks for the links, I'll re-check my tuning... mx1# cat /etc/sysctl.conf kern.ipc.shm_use_phys=1 kern.ipc.shmmax=1073741824 kern.ipc.shmall=262144 kern.ipc.semmsl=512 kern.ipc.semmap=256 I'm sure some of my tuning could use some help, like the shm_use_phys, maybe this is why my swap is not being used much? This is what I've changed from defaults in postgresql.conf... max_connections = 250 shared_buffers = 500MB work_mem = 64MB # min 64kB maintenance_work_mem = 256MB# min 1MB max_fsm_pages = 256000 -- Robert ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
On Thu, Jan 17, 2008 at 10:17:09PM +0100, Wojciech Puchar wrote: After reading tuning, it suggests the SWAP should be double RAM. According to dmesg... installing database on RAID-5 or asking if to add swap (when almost none is used)? what is more stupid? whould we vote? That is not a very helpful response. jerry ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED] ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
On Thu, 2008-01-17 at 22:17 +0100, Wojciech Puchar wrote: After reading tuning, it suggests the SWAP should be double RAM. According to dmesg... installing database on RAID-5 or asking if to add swap (when almost none is used)? what is more stupid? whould we vote? That was my whole point of showing you the low usage. I take that as a yes, RAID 1+0 would provide a dramatic difference in speed, thanks! -- Robert ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
is used)? what is more stupid? whould we vote? That was my whole point of showing you the low usage. I take that as a yes, RAID 1+0 would provide a dramatic difference in speed, thanks! the only adventage of RAID-5 is less wasted space than RAID-1. one and the only adventage. write performance is terrible on small writes - exactly what happens on database usage. with today sizes of disks more wasted space doesn't make much a problem, as i don't think your database have hundreds of gigabytes. did you look how much disks (no matter what RAID or just devices) are actually used?! use systat ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
On Thu, 2008-01-17 at 22:49 +0100, Wojciech Puchar wrote: is used)? what is more stupid? whould we vote? That was my whole point of showing you the low usage. I take that as a yes, RAID 1+0 would provide a dramatic difference in speed, thanks! the only adventage of RAID-5 is less wasted space than RAID-1. one and the only adventage. write performance is terrible on small writes - exactly what happens on database usage. with today sizes of disks more wasted space doesn't make much a problem, as i don't think your database have hundreds of gigabytes. did you look how much disks (no matter what RAID or just devices) are actually used?! use systat Using 'systat -iostat' it shows mostly idle with 25-70 MB/s on the aacd0 array. Most of time above 50. Thanks for the help! -- Robert ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: db performance
use systat Using 'systat -iostat' it shows mostly idle with 25-70 MB/s on the aacd0 array. Most of time above 50. Thanks for the help! -- Robert 70MB/s can't be mostly idle. or you meant CPU mostly idle. changing to RAID-not5 will help. seeking why disk traffic is so high - will help even more. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
