Re: resetting mysql server "gently"
- Original Message -
From: "Bryce C" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 10, 2002 7:14 AM
Subject: resetting mysql server "gently"
Bryce wrote:
BC> I have a script that runs every minute to deterimine
BC> if the load is above a certain value (or if swap is
BC> high) and if so, it kills all the httpd processes,
That is the Wrong Way (tm). You should not be killing off processes.
Instead, you should not let them come to life. Several appropriate Apache
directives leap to mind: MinSpareServers, MaxSpareServers, MaxClients. And
to fine-tune it even further: KeepAlive, MaxKeepAliveRequests,
KeepAliveTimeout. Just to name a few.
BC> does /etc/rc.d/init.d/mysql stop
Killing off processes, even threads you got by iterating over the
processlist, is simply a very bad idea. Far worse even than with Apache.
Because killing the HTTP daemon, while sloppy, is relatively harmless. But
killing off MySQL threads is a sure ticket to corrupting your database.
> Bottom line - killing off httpd is not too bad since
> users see a temporary glitch, but killing mysql seems
> to give a lot problems - scripts don't like to have
> the mysql server suddenly go away.
You said it. :) So, don't do that.
> Is there a gentler way that I can "reset" mysql?
No. There is nothing gentle about resetting MySQL. Programs like sendmail
come with excellent built-in directives to deal with high load
(ConnectionRateThrottle, QueueLA, RefuseLA, and such). Maybe one day MySQL
will support these too?
You could "nice" the mysqld process, if it really takes that much CPU load.
But that is, in itself, something you need to investigate. I run an entire
news server on MySql, and the CPU load still does not exceed a few
percent -- and that only at peaks.
You could also take a more inventive approach. :) Off the top of my hat, if
you run mysql on TCP, run mysqld on a different port, say, 9000, and call it
"custom_mysql" in /etc/services. Then configure xinetd to redirect to port
3306. That gives you the advantage that you can use xinetd's
"throttle-control". On my server that would look like something like this:
service custom_mysql
{
log_type= FILE /var/log/mysql_xinetd.log
log_on_success = HOST
flags = REUSE
cps = 3 5
per_source = 5
max_load= 2.5
socket_type = stream
protocol= tcp
wait= no
user= root
redirect= 127.0.0.1 3306
}
"max_load" is platform dependent, though. I have not tested the xinetd
option yet, but I see no reason this should not work.
- Mark
System Administrator Asarian-host.org
---
"If you were supposed to understand it,
we wouldn't call it code." - FedEx
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: resetting mysql server "gently"
I agree completely with Egor! You should not be killing your processes to solve load problems. If there is a load problem 1) You need to write your applications better, 2) you need to have a better database structure, 3) you need to analyze where indexes would help you, or 4) you need a more powerful machine. I know Apache has directives in the config file to tell it the maximum number of daemons to run. As in real life, so it is with computers: killing never solves the real problem, it just make it go away for a while. So, tune your app, tune Apache, tune MySQL, or get a bigger machine. But DO NOT implement your "kill when load is high" solution. BTW, enough "temporary glitches" and you won't have users any more. People like a trouble-free web site: unreliability is perceived as being unprofessional, unprofessional web sites lose clients in a hurry. j- k- On Thursday 10 October 2002 07:56, Egor Egorov wrote: > BC> I have a script that runs every minute to deterimine > BC> if the load is above a certain value (or if swap is > BC> high) and if so, it kills all the httpd processes, > BC> does /etc/rc.d/init.d/mysql stop > > BC> The script then starts everything back up again. > BC> I am curious if there is a better way to restart mysql > > I can say that solving performance/load problems by resetting > SQL server or web server is a totally wrong solution. > > The Right Way(tm) is to fine-tune both MySQL and Apache to fit in > memory/CPU load - this way you'll get much better performance and > reliability! > > So you can take a look at > http://www.mysql.com/doc/en/MySQL_Optimisation.html -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: resetting mysql server "gently"
Hello Bryce, Thursday, October 10, 2002, 8:14:22 AM, you wrote: BC> I have a mysql + php + apache server that is under BC> heavy load. BC> I have a script that runs every minute to deterimine BC> if the load is above a certain value (or if swap is BC> high) and if so, it kills all the httpd processes, BC> does /etc/rc.d/init.d/mysql stop BC> The script then starts everything back up again. BC> I am curious if there is a better way to restart mysql [] I can say that solving performance/load problems by resetting SQL server or web server is a totally wrong solution. The Right Way(tm) is to fine-tune both MySQL and Apache to fit in memory/CPU load - this way you'll get much better performance and reliability! So you can take a look at http://www.mysql.com/doc/en/MySQL_Optimisation.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: resetting mysql server "gently"
What seemed to work for me was to reduce the wait_timeout and interactive_timeout to a reasonable number, not less than 60 seconds. 90-500 should be okay. Any processes running longer than this value will be killed. At least you dont have connections running for thousands of seconds. Rgds Terence --- Uptime: 90471 Threads: 10 Questions: 3071980 Slow queries: 3 Opens: 1274 Flush tables: 1 Open tables: 64 Queries per second avg: 33.955 - Original Message - From: "Bryce C" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 10, 2002 1:14 PM Subject: resetting mysql server "gently" Hi, I have a mysql + php + apache server that is under heavy load. I have a script that runs every minute to deterimine if the load is above a certain value (or if swap is high) and if so, it kills all the httpd processes, does /etc/rc.d/init.d/mysql stop The script then starts everything back up again. I am curious if there is a better way to restart mysql that would kill off any hung/long-running queries but not totally bring the server down. Any other ideas for managing a big load. Bottom line - killing off httpd is not too bad since users see a temporary glitch, but killing mysql seems to give a lot problems - scripts don't like to have the mysql server suddenly go away. Is there a gentler way that I can "reset" mysql? Thanks for any input, ideas, etc! __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: resetting mysql server "gently"
> I am curious if there is a better way to restart mysql > that would kill off any hung/long-running queries but > not totally bring the server down. Any other ideas for > managing a big load. What you can do, assuming the mysql user has proper privs, is list and kill all mysql threads running when you shut down apache. Steps involved: kill apache SQL: 'show processlist;' ..foreach id returned.. SQL: 'kill $id;' sleep a few start apache You'll have killed all running mysql threads without stopping the server. You could limit it to just queries of certain status as well. Make sure not to kill the thread that's killing threads. ;) I'm not a mysql developer, but I would think doing this could have negative results. Half-completed transactions will halt replication. I wouldn't be surprised if you experienced some data corruption either. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
resetting mysql server "gently"
Hi, I have a mysql + php + apache server that is under heavy load. I have a script that runs every minute to deterimine if the load is above a certain value (or if swap is high) and if so, it kills all the httpd processes, does /etc/rc.d/init.d/mysql stop The script then starts everything back up again. I am curious if there is a better way to restart mysql that would kill off any hung/long-running queries but not totally bring the server down. Any other ideas for managing a big load. Bottom line - killing off httpd is not too bad since users see a temporary glitch, but killing mysql seems to give a lot problems - scripts don't like to have the mysql server suddenly go away. Is there a gentler way that I can "reset" mysql? Thanks for any input, ideas, etc! __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
