Hello, itīs me again. For the new readers: mysql 3.23.41, ~50q/s, load jumps to 200. If have been experimenting with the slow log to find out about these slow queries that are apparently locking too long. Much to my horror, I have lots of queries of this type popping up: # Time: 21 Lock_time: 17 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999880284, votescast="1304", prevvote="-1" where uid=69860; -- # Time: 20 Lock_time: 16 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999881577, votescast="937", prevvote="-213" where uid=69196; -- # Time: 20 Lock_time: 13 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999881605, numlogins="1" where uid=71386; -- # Time: 20 Lock_time: 5 Rows_sent: 0 Rows_examined: 0 update users set gotmail="t" where uid=6662; -- # Time: 21 Lock_time: 9 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999886543, votescast="2871", prevvote="147" where uid=38648; -- # Time: 24 Lock_time: 20 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999889591, votescast="8334", prevvote="3" where uid=39933; -- # Time: 20 Lock_time: 9 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999899878, votescast="2525", prevvote="221" where uid=53470; -- # Time: 21 Lock_time: 7 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999899996, votescast="199", prevvote="-7" where uid=65997; -- # Time: 28 Lock_time: 12 Rows_sent: 0 Rows_examined: 0 update users set lastlogin=999900020, votescast="3395", prevvote="-161" where uid=38648; These times are _way_ too long. usually an update takes ~1msec on my system. The table users has a primary index on uid and an index on lastlogin. I just donīt understand it. The manual says, the timing starts after having aquired all locks. So all the DB has to do is a) find the row b) update it c) maybe update the index on lastlogin d) release the locks. How can this take 20+ seconds? (20+ seconds that the user table is completely locked, of course, what makes it clear that everything comes to a halt). Above happens in a ~15 load condition. Or is the cause for it. Now that iīve told apache to spawn no more than 60 children, the load doesnīt go up to 200 anymore, because everybody is just waiting instead of issuing new queries (what is not really a solution) So again: how can an update with all tables already locked take longer that a blink of the eye? If this is a bug (and otherwise as well) i would be happy to report all details (again). Waiting for enlightenment (_not_ the window manager), Henning --------------------------------------------------------------------- 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
