SDS Posted September 7, 2006 Share Posted September 7, 2006 %top last pid: 6061; load averages: 0.21, 0.36, 0.42 up 125+22:46:07 13:01:56 193 processes: 1 running, 192 sleeping CPU states: 7.0% user, 4.3% nice, 0.8% system, 0.8% interrupt, 87.2% idle Mem: 548M Active, 999M Inact, 275M Wired, 104M Cache, 199M Buf, 76M Free Swap: 1020M Total, 70M Used, 949M Free, 6% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 67679 www 2 0 25656K 19844K accept 0:25 0.44% 0.44% httpd 67620 www 2 0 25012K 19432K accept 0:22 0.44% 0.44% httpd 67607 www 2 0 24620K 17748K accept 0:24 0.39% 0.39% httpd 67659 www 2 0 24408K 18508K accept 0:25 0.34% 0.34% httpd 84033 mysql 18 14 229M 198M pause 209:49 0.29% 0.29% mysqld 67687 www 2 0 24932K 19340K accept 0:23 0.24% 0.24% httpd 67629 www 2 0 24856K 19500K accept 0:27 0.15% 0.15% httpd 67634 www 2 0 26696K 21128K accept 0:25 0.10% 0.10% httpd 15280 mysql 18 14 229M 198M pause 250:59 0.05% 0.05% mysqld 27312 mysql 18 14 229M 198M pause 237:29 0.05% 0.05% mysqld 15311 mysql 18 14 229M 198M pause 260:07 0.00% 0.00% mysqld 15322 mysql 18 14 229M 198M pause 258:40 0.00% 0.00% mysqld 357 mysql 18 14 229M 198M pause 257:51 0.00% 0.00% mysqld 368 mysql 18 14 229M 198M pause 255:00 0.00% 0.00% mysqld 360 mysql 18 14 229M 198M pause 249:58 0.00% 0.00% mysqld 359 mysql 18 14 229M 198M pause 249:34 0.00% 0.00% mysqld 15327 mysql 18 14 229M 198M pause 249:15 0.00% 0.00% mysqld Those sizes on the "SIZE" column scare me for some reason. They never used ot be that big. I would like to restart mysql, but then I *think* I may lose all the performance based adjustments that it learns over time - correct? Can anyone help me decipher this before opening day? Link to comment Share on other sites More sharing options...
inkman Posted September 7, 2006 Share Posted September 7, 2006 Link to comment Share on other sites More sharing options...
The Poojer Posted September 7, 2006 Share Posted September 7, 2006 Purple! Link to comment Share on other sites More sharing options...
drnykterstein Posted September 7, 2006 Share Posted September 7, 2006 I know mysql and unix stuffs.. but this is over my head. Link to comment Share on other sites More sharing options...
Fezmid Posted September 7, 2006 Share Posted September 7, 2006 Well one thing to keep in mind is that, although they all say that they're 198M, it's actually shared memory so the database is only taking 198M *TOTAL* right now. I don't know what you normally see there, but I wouldn't think that 200M is overly large (of course if you normally see that at 10M, maybe it is...) I think you're correct about losing it's "info over time" if you restart it --I'm pretty sure Oracle does, so I would expect mysql to behave similarly. Let me know if that helps at all, or if you want any more info and I can try to help you out. CW Link to comment Share on other sites More sharing options...
Fan in San Diego Posted September 7, 2006 Share Posted September 7, 2006 Does your MySQL help guide talk about what is saved when you restart ? Link to comment Share on other sites More sharing options...
kegtapr Posted September 7, 2006 Share Posted September 7, 2006 Work on it when the board crashes Sunday after the loss. Link to comment Share on other sites More sharing options...
SDS Posted September 7, 2006 Author Share Posted September 7, 2006 Work on it when the board crashes Sunday after the loss. 764086[/snapback] well, the good news is at least I'll be at home and not in Buffalo when it happens.... Link to comment Share on other sites More sharing options...
mead107 Posted September 7, 2006 Share Posted September 7, 2006 Work on it when the board crashes Sunday after the loss. 764086[/snapback] you should have said after the WIN Link to comment Share on other sites More sharing options...
kegtapr Posted September 7, 2006 Share Posted September 7, 2006 you should have said after the WIN 764096[/snapback] I'm not really one to prod the football Gods. Link to comment Share on other sites More sharing options...
agilen Posted September 7, 2006 Share Posted September 7, 2006 I'm not a mysql admin by any means, but those numbers don't really strike me as being bad. I'm not exactly sure how this stuff gets reported through top, but linux (if this is linux) doesn't free memory until it needs it, so often it will look like you are using a lot of memory when in reality there is plenty there when you need it. Unless you begin swapping excessivly (which you aren't), I'd leave it alone. Link to comment Share on other sites More sharing options...
VABills Posted September 7, 2006 Share Posted September 7, 2006 Just restart it? What's the worst that can happen, people B word and moan, some of the impatient ones go elsewhere and you get better by subtraction. Link to comment Share on other sites More sharing options...
kegtapr Posted September 7, 2006 Share Posted September 7, 2006 Just restart it? What's the worst that can happen, people B word and moan, some of the impatient ones go elsewhere and you get better by subtraction. 764133[/snapback] So you're saying you'd leave? Link to comment Share on other sites More sharing options...
inkman Posted September 7, 2006 Share Posted September 7, 2006 Work on it when the board crashes Sunday after the loss. 764086[/snapback] Boooo Link to comment Share on other sites More sharing options...
/dev/null Posted September 7, 2006 Share Posted September 7, 2006 Just restart it? What's the worst that can happen 764133[/snapback] When I read this earlier I was gonna reply that it might give crayonz a way back in. But then I saw it already happend Welcome back crayon boy Link to comment Share on other sites More sharing options...
mcjeff215 Posted September 7, 2006 Share Posted September 7, 2006 Just restart it. Your system is into swap space and that's never a good thing over sustained periods of time. You've got 76M free, which isn't going to go very far. FreeBSD will LRU release some of that cached memory, but you're still a bit low. At least, it looks like a BSD top and not a Linux top. Restarting MySQL isn't going to hurt. I'm certain it's going to cache data over time so the more that's hit, the larger it's size is going to be. Is that limitation configurable in my.cnf? I almost never deal with database administration of any type, so I'm not sure what configurations are available. If you restart it, it's going to have to go to disk more, not necessarily a bad thing. Size is fine as long as you've got all the physical RAM to back it up. Might want to toss another 512 in the thing before the season starts, that wouldn't hurt. -Jeff %top last pid: 6061; load averages: 0.21, 0.36, 0.42 up 125+22:46:07 13:01:56 193 processes: 1 running, 192 sleeping CPU states: 7.0% user, 4.3% nice, 0.8% system, 0.8% interrupt, 87.2% idle Mem: 548M Active, 999M Inact, 275M Wired, 104M Cache, 199M Buf, 76M Free Swap: 1020M Total, 70M Used, 949M Free, 6% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 67679 www 2 0 25656K 19844K accept 0:25 0.44% 0.44% httpd 67620 www 2 0 25012K 19432K accept 0:22 0.44% 0.44% httpd 67607 www 2 0 24620K 17748K accept 0:24 0.39% 0.39% httpd 67659 www 2 0 24408K 18508K accept 0:25 0.34% 0.34% httpd 84033 mysql 18 14 229M 198M pause 209:49 0.29% 0.29% mysqld 67687 www 2 0 24932K 19340K accept 0:23 0.24% 0.24% httpd 67629 www 2 0 24856K 19500K accept 0:27 0.15% 0.15% httpd 67634 www 2 0 26696K 21128K accept 0:25 0.10% 0.10% httpd 15280 mysql 18 14 229M 198M pause 250:59 0.05% 0.05% mysqld 27312 mysql 18 14 229M 198M pause 237:29 0.05% 0.05% mysqld 15311 mysql 18 14 229M 198M pause 260:07 0.00% 0.00% mysqld 15322 mysql 18 14 229M 198M pause 258:40 0.00% 0.00% mysqld 357 mysql 18 14 229M 198M pause 257:51 0.00% 0.00% mysqld 368 mysql 18 14 229M 198M pause 255:00 0.00% 0.00% mysqld 360 mysql 18 14 229M 198M pause 249:58 0.00% 0.00% mysqld 359 mysql 18 14 229M 198M pause 249:34 0.00% 0.00% mysqld 15327 mysql 18 14 229M 198M pause 249:15 0.00% 0.00% mysqld Those sizes on the "SIZE" column scare me for some reason. They never used ot be that big. I would like to restart mysql, but then I *think* I may lose all the performance based adjustments that it learns over time - correct? Can anyone help me decipher this before opening day? 763972[/snapback] Link to comment Share on other sites More sharing options...
billsfanone Posted September 8, 2006 Share Posted September 8, 2006 Don't let Jay Rubeo anywhere near it. Link to comment Share on other sites More sharing options...
JÂy RÛßeÒ Posted September 8, 2006 Share Posted September 8, 2006 Don't let Jay Rubeo anywhere near it. 764437[/snapback] I'm looking at this with interest but not offering any comment. ALthough I will say that regenerating database stats is sometimes a good thing because it will force recompliation of execution plans based on current state which should improve performance. Link to comment Share on other sites More sharing options...
Dan Gross Posted September 8, 2006 Share Posted September 8, 2006 From the wife: MySQL queries are normally humongous. If you have a query that returns one record from a MySQL table, the query size in memory can easily be the size of the entire table. I hit those ceilings frequently. Restarting MySQL will not lose any performance data - that is wrong. In fact, periodically stopping (resting) then restarting it will clear out old temp files and broken inodes/pointers to help it run faster. Not something you want to do constantly - but definitely not harmful. However, the amount of time your sql daemons are running is of concern -- it is possible that your code does not disconnect from SQL. Many, many programs connect but fail to disconnect because MySQL will disconnect automatically. And www programmers are accustomed to a state-based architecture, expecting everything about the page delivery to dissolve once it has been accomplished. MySQL, however, hangs onto an open connection for a longer-than-necessary period of time. The fact that your mysql processes are in 'pause' supports that theory. Check your code for that. There are a few excellent guides if you google for tweak MySQL (note tweak - not tune) and mytop is a handy tool to keep an eye on the SQL load and what it might be doing. On the other hand, unless you're hitting the 'mysql - too many connections' error or your drive is thrashing, you really have nothing to actively worry about Hope that helps, -- Loy (fellow www admin) Link to comment Share on other sites More sharing options...
mcjeff215 Posted September 10, 2006 Share Posted September 10, 2006 Doesn't table-type make a lot of difference with MySQL, too? I don't know much about it, I make the DBAs do the work. I've always thought that there were different types? -Jeff From the wife: MySQL queries are normally humongous. If you have a query that returns one record from a MySQL table, the query size in memory can easily be the size of the entire table. I hit those ceilings frequently. Restarting MySQL will not lose any performance data - that is wrong. In fact, periodically stopping (resting) then restarting it will clear out old temp files and broken inodes/pointers to help it run faster. Not something you want to do constantly - but definitely not harmful. However, the amount of time your sql daemons are running is of concern -- it is possible that your code does not disconnect from SQL. Many, many programs connect but fail to disconnect because MySQL will disconnect automatically. And www programmers are accustomed to a state-based architecture, expecting everything about the page delivery to dissolve once it has been accomplished. MySQL, however, hangs onto an open connection for a longer-than-necessary period of time. The fact that your mysql processes are in 'pause' supports that theory. Check your code for that. There are a few excellent guides if you google for tweak MySQL (note tweak - not tune) and mytop is a handy tool to keep an eye on the SQL load and what it might be doing. On the other hand, unless you're hitting the 'mysql - too many connections' error or your drive is thrashing, you really have nothing to actively worry about Hope that helps, -- Loy (fellow www admin) 764599[/snapback] Link to comment Share on other sites More sharing options...
Recommended Posts