AW: HELP... lot of Load !
Hello Benjamin. Thanks a lot. Yes, you are right. It is a search engine. But I can?t get rid of the OR expressions, cause it is implemented in the search formular mask. So the User can choose any search criteria, which he wants. Neverthe- less I will follow your hint and check out these queries manually to view the load gage. Greetz Daniel -Ursprungliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Im Auftrag von Benjamin Pflugmann Gesendet: Dienstag, 11. Dezember 2001 22:04 An: Daniel Backhausen Cc: mySQL Mailingliste Betreff: Re: HELP... lot of Load ! Hi. On Mon, Dec 10, 2001 at 02:06:41PM +0100, [EMAIL PROTECTED] wrote: --- [...] # Time: 011210 13:40:23 # User@Host: eff21[eff21] @ localhost [] # Query_time: 25 Lock_time: 0 Rows_sent: 20 Rows_examined: 596309 SELECT SQL_BIG_RESULT Locations.Location_ID, Locations.Neu, Locations.Sichtbar, Locations.Location_Name, Locations.PLZ, Locations.Ort, Locations.Strasse, COUNT(Termine.Location_ID) AS Count_Termine FROM Locations LEFT JOIN Termine ON Locations.Location_ID = Termine.Location_ID WHERE Locations.Kino != '1' AND (Locations.Location_Name LIKE '%%' OR Locations.Ort LIKE '%%' OR Locations.PLZ LIKE '%' OR Locations.Kategorien_Locations1_Name LIKE '%%' OR Locations.Kategorien_Locations2_Name LIKE '%%' ) GROUP BY Locations.Location_ID ORDER BY Locations.Location_Name LIMIT 0,20 --- Did this say anything to you advertently my high load ? [...] Not directly. But it is a *possible* reason. To see if these queries are the reason, run such a query by hand and watch the load (note that OS caching may influence the result for subsequent runs) and the result of 'mysqladmin processlist'. If the query is to blame, there should be a lot of LOCKED processes. The query needed 25 seconds and had to scan a major part of the table (596309 rows). MySQL is not good in optimizing OR expression. Note, that the OR expressions above are redundant, as they will always match. If you can get rid of them (I assume the query is build automatically), the query will run faster. Btw, one of the queries returned thousand of rows. Does that make really sense (looks like a search engine to me). If not, don't allow such queries, or use LIMIT shorten the search to a reasonable size. Unfortunately you did not quote the result of EXPLAIN as I asked for, so I cannot say more. But simply start with what I have told and see how far you get. Bye, Benjamin. -- [EMAIL PROTECTED] - 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
AW: HELP... lot of Load
Hi Benjamin. Thank you for your fast reply. I have another two small detailed questions. 1st: Do you think, if I compile the mysql source on my own it should be better ? I readed, that RedHat 7 has pre-compiled the mysql with an errorous version of gcc. ... so I would compile it on my own. 2nd: You said that I have to look these slow queries are. Can you explain it a little bit ? I remember that joins can be very slow, and if there is an mistake in my query it can produce such a load ? Regards - Daniel Backhausen Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Im Auftrag von Benjamin Pflugmann Gesendet: Montag, 10. Dezember 2001 11:28 An: Daniel Backhausen Cc: [EMAIL PROTECTED] Betreff: Re: HELP... lot of Load Hi. On Mon, Dec 10, 2001 at 11:06:24AM +0100, [EMAIL PROTECTED] wrote: Hello. When I start the mysql database my load is nearly zero. After some time e.g. 30 min the load goes up to 11-12 and sometimes up to 25. Both CPU´s are going wild till 90% user 10% system. Normaly this system could handle a lot of database operations. Here are some system information: - Dual Intel Pentium III 733 - 512 MB Ram - RedHat Linux (Kernel 2.2.16) - mySQL version 3.23.41. I have created an output of my 'mysqlbug' status, so it might gonna help solving the problem. -- -- --- Server version[10C3.23.41-log Protocol version[8C10 Connection[14CLocalhost via UNIX socket UNIX socket[13C/var/lib/mysql/mysql.sock Uptime:[17C7 hours 33 min 15 sec Threads: 64 Questions: 186759 Slow queries: 2799 Opens: 362 Flush tables: 1 You should have a look what these slow queries are. With bad luck, one bad query can queue up all others after it. Open tables: 39;1H139 Queries per second avg: 6.867 [...] GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.0) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BI [49;1HTS=64 -D_LARGEFILE_SOURCE' CXX='c++' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_F [50;1HILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' LDFLAGS='' [...] If that is the RH delivered binary (and to me it looks so), you may want consider to use a pre-compiled binary from MySQL's website. There are known issues with RH compiler and MySQL (and pre-compiled) binaries - although I am not sure whether thy apply to your environment. Btw, I doubt that this would be the source of your load problems - I mentioned it just for completeness. Bye, Benjamin. -- [EMAIL PROTECTED] - 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
