On Tue, 20 Aug 2002, Matt Heaton wrote: > Hi, I am running 3.23.49 and had a quick question I hope someone can answer. > 70% of the time my sql server is doing just fine. I have 4 GIGs of memory, > and a 1.33 Ghz processor. I am running Redhat 7.3 with a 2.4.19 kernel. > > 70% of the time my server is fine, the other 30% it is 100% maxed out and I > have 0% free on the cpu for 10-20 seconds at a time. My question is this - > Is there a program or something that I can run that will show me exactly what > queries are being given to mysql. I want to know which queries I am giving > to the server that are giving it such a hard time so I can find an > alternative? If you have any ideas for the best way to do this I would > really appreciate it.
Hi Matt, There are two things to look at. One is "mysqladmin process" -- that will show you the current process list -- not much good if your system is completely locked up, however.. but worth a shot. What might even be better is to turn on your "slow query logging". In your my.cnf file, add this line in [mysqld] and [safemysqld]: log-slow-queries= /var/log/slowquery.log Make sure that this file already exists, and give it the same owner as your mysqld process user. (i.e. from shell, echo "" > /var/log/slowquery.log; chown mysql /var/log/slowquery.log) As your MySQL server runs into queries that take a long time to process (selects, inserts, etc.) it will record them in this log. Looks something like this: # Time: 020820 14:37:27 # User@Host: member[member] @ [10.100.3.1] # Query_time: 13 Lock_time: 0 Rows_sent: 0 Rows_examined: 19733 select CaseMem from FishTank where Smitty like 'Angelina' and active=1; And lastly, after you've stared at your process list and slow queries, you'll want to spend time figuring out WHY your database server is maxing out your CPU. I would look at server settings -- you'll want to be careful about how you tune things such as "key_buffer", and especially "thread_concurrency". Look at: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html (section 5.5.2) and then look on the web for tuning help pages as well. http://www.mysql.com/information/presentations/presentation-oscon2000-20000719/index.html The "Optimizing MySQL" Presentation Slides Learn what each of the --set-variable settings does in your my.cnf -- don't get too ambitious with your limits either, because that can cause excessive memory and CPU usage. Neil Mansilla whatUseek.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
