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 !
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
HELP... lot of Load
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 Open tables: 39;1H139 Queries per second avg: 6.867 Environment: 41;9Hmachine, os, target, libraries (multiple lines) System: Linux 2.2.16-22smp #1 SMP Tue Aug 22 16:39:21 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc 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='' LIBC: lrwxrwxrwx1 root root[11C11 Feb 17 2001 /lib/libc.so.6 - libc-2.2.so -rwxr-xr-x1 root root 5155229 Jan 11 2001 /lib/libc-2.2.so -rw-r--r--1 root root 24498288 Jan 11 2001 /usr/lib/libc.a -rw-r--r--1 root root[10C178 Jan 11 2001 /usr/lib/libc.so Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/u [57;1Hsr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --i ncludedir=/usr/include -- [58;1Hlibdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --shar edstatedir=/usr/com --man [59;1Hdir=/usr/share/man --infodir=/usr/share/info --without-debug --withou t-readline --enable-share[56;1H[1m[34m@ --- So if somebody has an idea, I will be really gratefully. Regards. - Daniel Backhausen - 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
HELP .. lot of load
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 Open tables: 39;1H139 Queries per second avg: 6.867 Environment: 41;9Hmachine, os, target, libraries (multiple lines) System: Linux 2.2.16-22smp #1 SMP Tue Aug 22 16:39:21 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc 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='' LIBC: lrwxrwxrwx1 root root[11C11 Feb 17 2001 /lib/libc.so.6 - libc-2.2.so -rwxr-xr-x1 root root 5155229 Jan 11 2001 /lib/libc-2.2.so -rw-r--r--1 root root 24498288 Jan 11 2001 /usr/lib/libc.a -rw-r--r--1 root root[10C178 Jan 11 2001 /usr/lib/libc.so Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/u [57;1Hsr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --i ncludedir=/usr/include -- [58;1Hlibdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --shar edstatedir=/usr/com --man [59;1Hdir=/usr/share/man --infodir=/usr/share/info --without-debug --withou t-readline --enable-share[56;1H[1m[34m@ --- So if somebody has an idea, I will be really gratefully. Regards. - Daniel Backhausen - 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: 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
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
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
