show processlist oddities
Personally I feel that I have a pretty good understanding on MySQL locking, however I am confused by the output I've received from SHOW PROCESSLIST at seemingly random times. At the end of this message is the output from SHOW FULL PROCESSLIST. ALL queries from output are shown. ALL WHERE clauses in all currently running queries are using an index and normally run in under 0.01 sec. My questions are: 1. How can Id 172387 (row 1) be in state 'end' for 5 seconds (it was already in this state at Time=1) 2. 172388 id updating and it appears 172407,172409,172415, and 172426 are all locked waiting for 172388 to finish. It has been in updating state for 5+ seconds since Time=1. How can it take so long when no other queries or using table v 3. Similarly 172404 has been preparing and 172406 has been statistics and 172406 has been updating since time=1. How can preparing or statistics take 5 seconds? Note again that these queries are not locked or waiting on other queries Remember normally these queries take 0.01 sec. Everything else is locked waiting for the first 5 unlocked queries The server is loaded enough that these first 5 'frozen' queries quickly lock everything else out and the database completely freezes. 3 seconds later than what is shown below, SHOW PROCESSLIST has hundreds of locked processes. *** 1. row *** Id:172387 Time:5 State:end Info:UPDATE si SET val=val-5 WHERE id=317675 *** 2. row *** Id:172388 Time:5 State:updating Info:UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=233822 *** 3. row *** Id:172404 Time:5 State:preparing Info:SELECT * FROM s WHERE id=321653 *** 4. row *** Id:172406 Time:5 State:statistics Info:SELECT * FROM ad WHERE z='t' ORDER BY seq LIMIT 1 *** 5. row *** Id:172407 Time:5 State:Locked Info:UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=236404 *** 6. row *** Id:172408 Time:5 State:update Info:INSERT INTO hist VALUES(261366,236313) *** 7. row *** Id:172409 Time:5 State:Locked Info:UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=233823 *** 8. row *** Id:172410 Time:5 State:Locked Info:INSERT INTO hist VALUES(265095,233822) *** 9. row *** Id:172412 Time:4 State:statistics Info:SELECT * FROM ad WHERE z='l' ORDER BY seq LIMIT 1 *** 10. row ** Id:172414 Time:3 State:Locked Info:INSERT INTO hist VALUES(287716,233818) *** 11. row ** Id:172415 Time:3 State:Locked Info:UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=233822 *** 12. row ** Id:172418 Time:3 State:Locked Info:INSERT INTO hist VALUES(306395,234632) *** 13. row ** Id:172420 Time:2 State:Sorting result Info:SELECT * FROM idx WHERE id=10523652 ORDER BY cnt *** 14. row ** Id:172421 Time:2 State:Sorting result Info:SELECT * FROM idx WHERE id=10523652 ORDER BY cnt *** 15. row ** Id:172423 Time:1 State:Locked Info:INSERT INTO hist VALUES(304442,5928601) *** 16. row ** Id:172424 Time:1 State:statistics Info:SELECT * FROM ad WHERE z='t' ORDER BY seq LIMIT 1 *** 17. row ** Id:172425 Time:1 State:Locked Info:INSERT INTO hist VALUES(282924,5681301) *** 18. row ** Id:172426 Time:1 State:Locked Info:UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=101086 *** 19. row ** Id:172427 Time:1 State:Sending data Info:SELECT * FROM plan p LEFT JOIN hist h ON p.id=h.id LEFT JOIN hist h2 ON p.id=h2.id AND h2.stat='i' LEFT JOIN s ON s.id=p.id WHERE h.stat IS NULL AND p.id=288135 ORDER BY seq LIMIT 1 *** 20. row ** Id:172428 Time:1 State:Locked Info:UPDATE plan SET stat='L' WHERE id=282924 *** 21. row
concurrent insert documentation clarification
The MySQL documentation says: To ensure that the update log/binary log can be used to re-create the original tables, MySQL will not allow concurrent inserts during INSERT SELECT Does this mean: A. An INSERT...SELECT cannot run if any other SELECT is running on the table OR B. An INSERT...SELECT cannot run if another INSERT...SELECT is already running on the table Which is correct? The wording of the documentation suggests that B is correct, but my actual tests on the server indicate A is correct (MySQL 3.23.43 RH Linux 7.1) Thanks - 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
connections not closing
What could be a possible cause for DB connections that do not close? I have a Apache-PHP-mysql setup and the apache/php thread appears to exit, but show processlist report connections that stay open and sleep Is there a way to determine what was run on that connection that may have caused it to stay open? Shouldn't the connection close itself when the php thread exits. I'm not using persistent connects. Any help appreciated. - 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: Serious LEFT JOIN bug in 3.23
The query returns 2 different result sets depending on whether the table on the right side of a LEFT JOIN has records or not. I just don't see how this is acceptable behavior. Either it should always return the result set or it should always return the empty set. The size of the right table should not affect the size or the result in a LEFT JOIN Hi. On Mon, Mar 19, 2001 at 09:06:49PM -0700, [EMAIL PROTECTED] wrote: hist.id will never be NULL and 5 at the same time. Your clauses conflict with each other. You need to re-write your query. This is ture, except the query suceeds if there is records in 'hist' Even if it is not a bug it is at least inconsistent behaviour of MySQL No, it is well possible that EXPLAIN fails and SELECT succeeds. EXPLAIN fails because it cannot tell you how it would optimize the query, because the query returns in an early stage with an empty result set. Maybe I didn't understand your objection completely. But what I see seems perfectly reasonable to me. Bye, Benjamin. "Michael Griffith" [EMAIL PROTECTED] wrote: EXPLAIN LEFT JOIN fails when joining on an empty table with constant in WHERE clause CREATE TABLE idx ( id mediumint, KEY id (id), ) TYPE=MyISAM CREATE TABLE hist ( id mediumint, link mediumint, KEY id (id,link) ) TYPE=MyISAM Table idx is populated with thousands or records. Consider the following query: 1. SELECT idx.id FROM idx 2. LEFT JOIN hist ON hist.link=idx.id AND hist.id=5 3. WHERE 4. hist.id=5 AND 5. hist.id IS NULL AND 6. idx.id=2 The query works perfectly, buy ONLY if table 'hist' is populated. If table 'hist' is empty then the query returns no results. This is easy to fix by commenting out line #4. However by commenting out line 4 the optimizer decides to use a temporary table which would slow it down. Here is the EXPLAIN output as shown above with empty 'hist' table: "Impossible WHERE noticed after reading const tables" Here is the EXPLAIN output from above query when 'hist' has 1 record: | table | type | possible_keys | key | key_len | ref| rows | Extra +---+--+---+--+-++--+--- --+ | idx | ref | id| id | 3 | const|3 | where used ; Using index (using temp without ln #4) | hist | ref | id| id | 6 | const,idx.id |2 | where used; Using index; Not exists +---+--+---+--+-++--+--- --+ It seems the optimizer is checking the constant in hist before it even considers this is a LEFT JOIN. This is a problem on my Windows MySQL 3.23.33. Based on the EXPLAIN output of a simpler query I do not believe 3.22 does the same thing. - 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: Serious LEFT JOIN bug in 3.23
I appreciate your explanation. You may be correct about Standard SQL, however I do not believe that this can be explained as a simple cross-product. If a LEFT JOIN were implemented starting with a cross product then NO LEFT JOIN would EVER return NULL in the right table. A pure cross product would not take into consideration a non existant record in the right table. I believe the implementation of a LEFT JOIN must include more than a cross-product. At the very least a NULL row must be added to the right table before performing the product. Based on my results I would guess the optimizer stops the my query before the server ever even tries to perform the join. Ah okay, I as thought, I misunderstood your objection. The behaviour is how Standard SQL defines it and what is to be expected. You may want consider to do some reading on relational database theory (don't take this proposal wrong...). Aside from that, I agree, that sometimes the behaviour you expected would be handy. Well, let's see if I can explain it (that's is not my strength :-): SQL operates (at least in theory) on sets, and the first step to resolve the query is to build the cross product of both tables involved in a join (i.e. each row from one table is put together with each table of the other table, creating N times M rows). Then depending on the join type, the intermediate result is restricted. The problem you encounter is, that if the right table is empty, the cross product already is empty. And there is nothing left to do. While, when the right table is non-empty, you get the result you expect. The inconsistence, as you called it, is that a LEFT JOIN is _not_ defined the way one usually tends to think about it, namely to take each row from the left table and simply look whether there is a match in the right table. It is more complex, and admittingly not as intuitive as it could be, but the way, RDBMs based on SQL work. Bye, Benjamin. On Wed, Mar 21, 2001 at 12:37:56AM -0700, [EMAIL PROTECTED] wrote: The query returns 2 different result sets depending on whether the table on the right side of a LEFT JOIN has records or not. I just don't see how this is acceptable behavior. Either it should always return the result set or it should always return the empty set. The size of the right table should not affect the size or the result in a LEFT JOIN [...] - 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
Serious LEFT JOIN bug in 3.23
EXPLAIN LEFT JOIN fails when joining on an empty table with constant in WHERE clause CREATE TABLE idx ( id mediumint, KEY id (id), ) TYPE=MyISAM CREATE TABLE hist ( id mediumint, link mediumint, KEY id (id,link) ) TYPE=MyISAM Table idx is populated with thousands or records. Consider the following query: 1. SELECT idx.id FROM idx 2. LEFT JOIN hist ON hist.link=idx.id AND hist.id=5 3. WHERE 4. hist.id=5 AND 5. hist.id IS NULL AND 6. idx.id=2 The query works perfectly, buy ONLY if table 'hist' is populated. If table 'hist' is empty then the query returns no results. This is easy to fix by commenting out line #4. However by commenting out line 4 the optimizer decides to use a temporary table which would slow it down. Here is the EXPLAIN output as shown above with empty 'hist' table: "Impossible WHERE noticed after reading const tables" Here is the EXPLAIN output from above query when 'hist' has 1 record: | table | type | possible_keys | key | key_len | ref| rows | Extra +---+--+---+--+-++--+--- --+ | idx | ref | id| id | 3 | const|3 | where used ; Using index (using temp without ln #4) | hist | ref | id| id | 6 | const,idx.id |2 | where used; Using index; Not exists +---+--+---+--+-++--+--- --+ It seems the optimizer is checking the constant in hist before it even considers this is a LEFT JOIN. This is a problem on my Windows MySQL 3.23.33. Based on the EXPLAIN output of a simpler query I do not believe 3.22 does the same thing. - 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: Serious LEFT JOIN bug in 3.23
hist.id will never be NULL and 5 at the same time. Your clauses conflict with each other. You need to re-write your query. This is ture, except the query suceeds if there is records in 'hist' Even if it is not a bug it is at least inconsistent behaviour of MySQL "Michael Griffith" [EMAIL PROTECTED] wrote: EXPLAIN LEFT JOIN fails when joining on an empty table with constant in WHERE clause CREATE TABLE idx ( id mediumint, KEY id (id), ) TYPE=MyISAM CREATE TABLE hist ( id mediumint, link mediumint, KEY id (id,link) ) TYPE=MyISAM Table idx is populated with thousands or records. Consider the following query: 1. SELECT idx.id FROM idx 2. LEFT JOIN hist ON hist.link=idx.id AND hist.id=5 3. WHERE 4. hist.id=5 AND 5. hist.id IS NULL AND 6. idx.id=2 The query works perfectly, buy ONLY if table 'hist' is populated. If table 'hist' is empty then the query returns no results. This is easy to fix by commenting out line #4. However by commenting out line 4 the optimizer decides to use a temporary table which would slow it down. Here is the EXPLAIN output as shown above with empty 'hist' table: "Impossible WHERE noticed after reading const tables" Here is the EXPLAIN output from above query when 'hist' has 1 record: | table | type | possible_keys | key | key_len | ref| rows | Extra +---+--+---+--+-++--+--- --+ | idx | ref | id| id | 3 | const|3 | where used ; Using index (using temp without ln #4) | hist | ref | id| id | 6 | const,idx.id |2 | where used; Using index; Not exists +---+--+---+--+-++--+--- --+ It seems the optimizer is checking the constant in hist before it even considers this is a LEFT JOIN. This is a problem on my Windows MySQL 3.23.33. Based on the EXPLAIN output of a simpler query I do not believe 3.22 does the same thing. - 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
forward slash in indexed search
Can anyone explain why LIKE queries do not use an index when the LIKE string contains a forward slash? Example: CREATE TABLE words ( word varchar(250) PRIMARY KEY ); Populate table with 200,000+ rows EXPLAIN SELECT * FROM words WHERE word LIKE 'abcdef'; This query uses the Primary key to search EXPLAIN SELECT * FROM words WHERE word LIKE 'abc/def'; This query does not use any key and scans ALL rows I am on Windows, MySQL 3.23.32. Is it a bug?
Re: forward slash in indexed search
Can anyone explain why LIKE queries do not use an index when the LIKE string contains a forward slash? Follow-up to my own post: The problem is with the '_' wildcard, but I still don't understand. My previous examples were simplified, but here is the exact query: SELECT * FROM words WHERE word LIKE 'Regional/North_America' SELECT * FROM words WHERE word LIKE 'Regional North_America' I understand the the underscore was matching a wildcard and not a literal, but I still don't understand why the first query DOES NOT use the index on word and the second query does. - 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
query optimization suggestion
When using mySQL I've learned to avoid OR in any queries as much as possible . Almost always this causes a major speed decrease. Consider this table: CREATE TABLE test( userID int, # (non-unique) testID int, # (non-unique) PRIMARY key(testid,userid) ); Suppose this table is populated with 1,000,000 rows. Then do this: DELETE FROM test WHERE userID= AND testID20 OR testID80; This query is EXTREMELY slow becasue it looks at every row in the DB. A significant improvement can be acheived by splitting it into 2 statements: DELETE FROM test WHERE userID= AND testID20; DELETE FROM test WHERE userID= AND testID80; On real data I've acheived at least a ten fold increase doing this. This is easy to optimize from the client side, however, I don't see any reason why this optimization can't or shouldn't be build into the server. Whenever an OR can be split into two separate queries on the same index this optimization should work. Food for thought. - 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: query optimization suggestion
Shouldn't the first query have parenthesis? As in: DELETE FROM test WHERE userID=X AND (testID20 OR testID80); Even if it works the other way, parenthesis make it more clear what you're trying to accomplish. -Angela Actually, yes. Sorry about the poor example. My point is that sometimes applications can be sped up by splitting OR queries into two separate queries, and I believe many times the server could recognize this, although I am wrong in this particular example. I'll have to check my code for an exact situation. Michael - 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
temp tables lock unrelated tables
Using SHOW PROCESSLIST or mysqladmin proc Every time a table reports status of 'copying to tmp table' all other UPDATE queries are locked, even in unrelated tables. For example: Query #1: SELECT * FROM a JOIN B WHERE Status: copying to tmp table Query #2: UPDATE C SET x=x+1 WHERE..Status: locked Even though table C is not used is query #2 it is locked until query #1 completed. This can cause huge problems when hundreds of quick updates are locked for more than a few seconds Why does a tmp table on one table lock another? How can I prevent this from happening?
Re: temp tables lock unrelated tables
My TMPDIR is actually not separate from the data. There is plently of disk
space. In fact, thinking it was some sort of RAM problem I reduced the size
of the database by almost 30% with no change in performace.
You say this should not happen, yet as I've been searching the mailing list
there's at least 2-3 threads in the last year talking about this problem.
Most posts have gone unanswered.
I acutally have over 250 unrelated threads that get locked during a 1 minute
long query. Since my web server is set for 256 MAX processes, the website
that links to the DB dies until the offending query ends.
This happens with many queries. All are tuned and have worked for months
until recently. In fact no code has changed in our scripts. Unrelated table
just started locking all of a sudden. Also we have run isamchk
100% of the tables are in RAM. The only disk access should be updates. This
may be useful. This is the mysqladmin ext output when NO query is locked:
+--++
| Variable_name| Value |
+--++
| Aborted_clients | 1 |
| Aborted_connects | 1 |
| Created_tmp_tables | 7809 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 69600 |
| Handler_read_first | 251|
| Handler_read_key | 6190764|
| Handler_read_next| 19966429 |
| Handler_read_rnd | 1085708844 |
| Handler_update | 940141 |
| Handler_write| 593065 |
| Key_blocks_used | 59010 |
| Key_read_requests| 18022185 |
| Key_reads| 57325 |
| Key_write_requests | 600364 |
| Key_writes | 277250 |
| Max_used_connections | 256|
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 511|
| Open_files | 106|
| Open_streams | 0 |
| Opened_tables| 1173 |
| Questions| 675496 |
| Running_threads | 5 |
| Slow_queries | 20005 |
| Uptime | 23360 |
+--++
Michael Griffith writes:
Using SHOW PROCESSLIST or mysqladmin proc
Every time a table reports status of 'copying to tmp table' all other
UPDATE queries are locked, even in unrelated tables.
For example:
Query #1: SELECT * FROM a JOIN B WHERE Status: copying to tmp
table
Query #2: UPDATE C SET x=x+1 WHERE..Status: locked
Even though table C is not used is query #2 it is locked until query #1
completed. This can cause huge problems when hundreds of quick updates are
locked for more than a few seconds
Why does a tmp table on one table lock another?
How can I prevent this from happening?
Hi!
The above should not happen.
You should look at entire processlist.
And check the size of your TMPDIR partition.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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
temp tables lock unrelated tables
Earlier I posted a message about SHOW PROCESSLIST reporting queries "locked" whenever another thread is "Copying to tmp table" After many more hours of diagnosis, the actual problem is somewhat different: MySQL does report other processes as locked. But they do not wait until the temp table is completely written to execute. For example: Query #1: SELECT * FROM a JOIN B WHERE Status: copying to tmp table Query #2: UPDATE C SET x=x+1 WHERE..Status: locked Query #3: UPDATE C SET x=x+1 WHERE..Status: locked With these queries, Query #2 does eventually finish even if #1 does not. The problem is that as soon as query #1 is issued and starts copying to temp table, everything else slows down to almost a crawl. It appears as if #3,#4,#5 are permanently locked because it is so slow. For practical purposes they are locked until query #1 is done. I have hundreds more locked queries identical to #2 #3. Although mysqladmin reports all as locked, it is most likely one of them that is locking the others and not #1. The problem is that it is extremely slow. MySQL acts as if Query#1 gets some kind of priority. As if the thread scheduling gives almost no time to any other thread. At least no disk time. What would cause writing to a temp table to preempt other disk operations? Can it be stopped?
