Hi Benjamin I have enabled the log for slow queries with following result.
----------------------------------------------------------------------- /usr/sbin/mysqld, Version: 3.23.46-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument # Time: 011210 13:39:58 # User@Host: localhost [] # Query_time: 11 Lock_time: 0 Rows_sent: 188565 Rows_examined: 378760 SELECT SQL_BIG_RESULT Locations.Location_ID FROM Locations 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 ; # 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 ; # Time: 011210 13:58:22 # User@Host: eff21[eff21] @ localhost [] # Query_time: 13 Lock_time: 0 Rows_sent: 0 Rows_examined: 133659 SELECT Locations.Location_ID, COUNT(Events.Event_ID) AS Count_Events, Locations.Kategorien_Locations1_ID AS Loc_Kat FROM Locations LEFT JOIN Termine ON Locations.Location_ID = Termine.Location_ID LEFT JOIN Events ON Termine.Event_ID = Events.Event_ID WHERE ( Locations.Location_Name LIKE '%routenplaner%' OR Locations.Ort LIKE '%routenplaner%' OR Locations.Region LIKE '%routenplaner%' OR Locations.Kategorien_Locations1_Name LIKE '%routenplaner%' OR Locations.Kategorien_Locations2_Name LIKE '%routenplaner%' OR Locations.Kategorien_Locations3_Name LIKE '%routenplaner%' OR Locations.Location_Name LIKE '%routenplaner%' OR Locations.Ort LIKE '%routenplaner%' OR Locations.Region LIKE '%routenplaner%' OR Locations.Kategorien_Locations1_Name LIKE '%routenplaner%' OR Locations.Kategorien_Locations2_Name LIKE '%routenplaner%' OR Locations.Kategorien_Locations3_Name LIKE '%routenplaner%' OR Locations.Location_Name LIKE '%routenplaner%' OR Locations.Ort LIKE '%routenplaner%' OR Locations.Region LIKE '%routenplaner%' OR Locations.Kategorien_Locations1_Name LIKE '%routenplaner%' OR Locations.Kategorien_Locations2_Name LIKE '%routenplaner%' OR Locations.Kategorien_Locations3_Name LIKE '%routenplaner%' ) AND Locations.PLZ LIKE '%' AND Locations.Ort LIKE '%%' AND Locations.Sichtbar = '1' AND Locations.Kino != '1' AND Locations.Bundesland LIKE '%%' GROUP BY Locations.Location_ID HAVING Loc_Kat != 10000 OR (Count_Events != 0 AND Loc_Kat = 10000) ; (END) ----------------------------------------------------------------------- Did this say anything to you advertently my high load ? Thank you for your time & regards - Daniel Backhausen -----Ursprungliche Nachricht----- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Im Auftrag von Benjamin Pflugmann Gesendet: Montag, 10. Dezember 2001 12:13 An: Daniel Backhausen Cc: Alexander Doen; mySQL Mailingliste Betreff: Re: HELP... lot of Load Hi. On Mon, Dec 10, 2001 at 11:41:34AM +0100, [EMAIL PROTECTED] wrote: > 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. It was compiled with the compiler on the distro, so you would get the same problem. You would have to upgrade/replace the compile system, but don't ask me which version will work for sure, I don't know. RH developed an own branch of gcc to fix some shortcomings of the official gcc at that time. For me, it's not completely sure if their gcc version is faulty or if it is some stuff in MySQL is strange, but the resulting binary of the combination is known for various stability problems. If you have no compelling reason to run your own version you are safest (and probably fastest) with the official MySQL binary. > 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 ? Enable the log for slow queries (http://www.mysql.com/doc/S/l/Slow_query_log.html) and find out why they were slow. If you don't see the reason, post the belonging excerpt from the log with an EXPLAIN of the query. I cannot explain all possibilities of what may go wrong here... it would take quite some time. One common situation is that one SELECT needs a long time and subsequent UPDATEs will wait until the SELECT has finished. Then, other SELECTs will also be blocked (due to the UPDATE), which would normally run parallel to the slow SELECT. Most SELECTs are finished so quick that one never notices this. To each pending SELECT belongs a MySQL thread and probably an Apache process and that will make your load going up. That *could* be your problem. And there are several methods of resolution for this (the straight-forward is making the SELECT going faster), but more about this, when you have learned the real cause. 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
