Thread: Linux TOP is a indicator?
Hi, I have one of my database server that I run the "top" command: top - 16:16:30 up 42 days, 13:23, 4 users, load average: 3.13, 3.52, 3.36 Tasks: 624 total, 1 running, 623 sleeping, 0 stopped, 0 zombie Cpu(s): 1.4%us, 1.1%sy, 0.0%ni, 84.4%id, 12.9%wa, 0.0%hi, 0.2%si, 0.0%st Mem: 16432240k total, 16344596k used, 87644k free, 27548k buffers Swap: 10241428k total, 3680860k used, 6560568k free, 6230376k cached I´m afraid of two things, one is the "load average", I think 3 is too much, another is the "swap", almost 4GB of swap, I think that is too much swap. Am I right? Can I use those indicators to know if my database is ok? Thanks Waldomiro
Waldomiro wrote: > Hi, > > I have one of my database server that I run the "top" command: > > top - 16:16:30 up 42 days, 13:23, 4 users, load average: 3.13, 3.52, > 3.36 > Tasks: 624 total, 1 running, 623 sleeping, 0 stopped, 0 zombie > Cpu(s): 1.4%us, 1.1%sy, 0.0%ni, 84.4%id, 12.9%wa, 0.0%hi, > 0.2%si, 0.0%st > Mem: 16432240k total, 16344596k used, 87644k free, 27548k buffers > Swap: 10241428k total, 3680860k used, 6560568k free, 6230376k cached > > I´m afraid of two things, one is the "load average", I think 3 is too > much, another is the "swap", almost 4GB of swap, I think that is too > much swap. > > Am I right? > > Can I use those indicators to know if my database is ok? Top only provides limited information about your system performance. disk IO is real important too, yous show 13% IO Wait there, indication that the processes are spedning a signficant amount of time waiting for disk. you can monitor disk IO by iostat -x <interval> (you may need to install the 'sysstat' package for your linux distribution). a load facgtor of 3 means three processes are active. how many cores do you have? what processes are active? those are some other questions worth asking ... as far as 'database is OK', well, is it performing adequately? is your data intact? if so, then I'd say yes, its performing OK.
On Wed, Oct 21, 2009 at 12:43 PM, Waldomiro <waldomiro@shx.com.br> wrote: > Hi, > > I have one of my database server that I run the "top" command: > > top - 16:16:30 up 42 days, 13:23, 4 users, load average: 3.13, 3.52, 3.36 > Tasks: 624 total, 1 running, 623 sleeping, 0 stopped, 0 zombie > Cpu(s): 1.4%us, 1.1%sy, 0.0%ni, 84.4%id, 12.9%wa, 0.0%hi, 0.2%si, > 0.0%st > Mem: 16432240k total, 16344596k used, 87644k free, 27548k buffers > Swap: 10241428k total, 3680860k used, 6560568k free, 6230376k cached > > I´m afraid of two things, one is the "load average", I think 3 is too much, Depends. If you've got enough CPU cores and IO bandwidth 3 isn't that much really. I've got dbs running smoothly at anything under 20 at work. On my laptop that would be way too much. > another is the "swap", almost 4GB of swap, I think that is too much swap. Again, that really depends. Linux will swap out seldom used things from memory to swap to make more room for kernel cache, which is often the right decision. Sometimes not. Sometimes it swaps out pgsql shared_buffers to make room, and that's usually bad. You can adjust this with sysctl. /sbin/sysctl -a|grep swappiness vm.swappiness = 60 sudo vi /etc/sysctl.conf (add line like this:) vm.swappiness = 5 > Am I right? Hard to say. 12% or more IO Wait could be a sign of trouble more than any of those other things. > Can I use those indicators to know if my database is ok? You mean the server I assume. Given that you're mostly at idle and io wait, I'd guess the real issue is you've got a single hard drive trying to do a RAID-10 array's worth of work.
As a followup to my previous post, here's what a healthy, well behaved but running under moderate load db server looks like: top - 15:47:51 up 436 days, 2:31, 3 users, load average: 12.03, 11.86, 12.26 Tasks: 394 total, 7 running, 387 sleeping, 0 stopped, 0 zombie Cpu(s): 17.3%us, 1.3%sy, 0.0%ni, 80.6%id, 0.4%wa, 0.1%hi, 0.3%si, 0.0%st Mem: 33031252k total, 32623600k used, 407652k free, 282760k buffers Swap: 9767480k total, 3768k used, 9763712k free, 21242700k cached Note load is 12, swap is 3Meg, and IO Wait is 0.4%. vmstat shows about 22Megs/second being written, most of that is WAL and logs, which we can see with "iostat -x 10" which shows about 44k 512 blocks per second going to /dev/sda, my pg_xlog / OS drive set. The single most important measurement here is IO Wait being low. If that's low the machine may degrade slowly under heavier and heavier load, but it won't just grind to a halt like high IO Wait will do.
In our system we have a hybrid security system. We have tables that I want to make sure that based on membership in a group that update and/or delete is not allowed to a specific group. We also have application level security which is much more granular and is much more job function based. Our application is a transportation application, so a user might have insert, update and delete in order entry but a dispatcher would not have the ability to delete a order, they must cancel it with a reason code. These would both be part of the same ROLE in the database. Trying to maintain the database to match the application security would become cumbersome for our customers. Now for the question, if I specifically revoke a update or delete on a per table basis for a role to I still have to specifically define what security attributes they have access on? If this doesn't work, would I give access to the schema and then just specifically revoke the update or delete functionality for just the tables I'm looking to protect. Best Regards Mike Gould
On Mon, Oct 26, 2009 at 10:32:05AM -0500, Michael Gould wrote: > In our system we have a hybrid security system. [...] > Trying to maintain the database > to match the application security would become cumbersome for our customers. Have you looked at using functions protected by "security definer"? I tend to use these a lot when I want to enforce any remotely complicated security properties. The functions can go away and check whatever properties you want and allowing you to have a parallel set of checks going on to the stuff directly supported by PG. -- Sam http://samason.me.uk/