re: Mysql versions, disks, speed, FTS, and EXPLAIN feature request (SELECT [CALC_RESOURCES])
On Saturday 21 December 2002 00:06, Steven Roussey wrote: I wanted to thank the MySQL team for making such a great product! We moved from 3.23 to 4.0.x a couple of months ago and everything works great. Just upgraded to 4.0.6 and glad to see it work out of the box without a rev 4.0.6a. Those glib issues were such a pain! 4.0.5a and 4.0.6 have been as solid as any 3.23.x in our experience. Just a note to users of Full Text Search: put it on some other machine. FTS was basically clearing the MySQL and Linux caches with all its read data. Putting it on a separate machine let all the other stuff run just fine. Short example: Main server: 3000 q/s Disk read: 540 KB/sLoad: 1 FTS server: 2 q/s Disk read: 7600 KB/sLoad: 12 Our queries are not representative of anyone else's! YMMV! Just a note on how FTS can really read a lot of data and how moving it can really clear things up. Which brings me to: EXPLAIN feature request: to have EXPLAIN RESOURCES SELECT or something similar to be able to show resource usage instead of query/index plan. Resources like CPU, disk read, and disk write. Likely it should not actually be EXPLAIN, since it would do the operation (where explain does not). So maybe SELECT [CALC_RESOURCES] ... followed by a SHOW RESOURCES_USAGE or something. I've consulted about your question with Sinisa Milivojevic. Here is what he said: EXPLAIN can not do that, because explain does not know: * how much code will be traversed * how fragmented are tables * how many functions will have to be evaluated * how loaded is a system (is relevant on some OS's) * how big temp tables or temp files will be * how well ANALYZE'd are indices * etc -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Mysql versions, disks, speed, FTS, and EXPLAIN feature request (SELECT [CALC_RESOURCES])
Hi all! I wanted to thank the MySQL team for making such a great product! We moved from 3.23 to 4.0.x a couple of months ago and everything works great. Just upgraded to 4.0.6 and glad to see it work out of the box without a rev 4.0.6a. Those glib issues were such a pain! 4.0.5a and 4.0.6 have been as solid as any 3.23.x in our experience. Just a note to users of Full Text Search: put it on some other machine. FTS was basically clearing the MySQL and Linux caches with all its read data. Putting it on a separate machine let all the other stuff run just fine. Short example: Main server: 3000 q/s Disk read: 540 KB/sLoad: 1 FTS server: 2 q/s Disk read: 7600 KB/sLoad: 12 Our queries are not representative of anyone else's! YMMV! Just a note on how FTS can really read a lot of data and how moving it can really clear things up. Which brings me to: EXPLAIN feature request: to have EXPLAIN RESOURCES SELECT or something similar to be able to show resource usage instead of query/index plan. Resources like CPU, disk read, and disk write. Likely it should not actually be EXPLAIN, since it would do the operation (where explain does not). So maybe SELECT [CALC_RESOURCES] ... followed by a SHOW RESOURCES_USAGE or something. -steve- Main server: Load is 1.0 Server version 4.0.6-gamma-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 14 hours 18 min 27 sec Threads: 228 Questions: 152864656 Slow queries: 2877 Opens: 89075 Flush tables: 1 Open tables: 2825 Queries per second avg: 2967.842 # iostat -k 10 avg-cpu: %user %nice%sys %idle 23.050.00 19.30 57.65 Device:tpskB_read/skB_wrtn/skB_readkB_wrtn dev3-00.60 0.0011.20 0112 dev8-0 31.6097.6078.80976788 dev8-1 27.5080.8073.60808736 dev8-2 30.2092.0068.80920688 dev8-3 29.0091.2070.00912700 dev8-4 26.9075.6069.60756696 dev8-5 30.7098.4074.40984744 FTS server: Load is 12 Server version 4.0.6-gamma-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 15 hours 50 min 12 sec Threads: 36 Questions: 132274 Slow queries: 2527 Opens: 332 Flush tables: 1 Open tables: 64 Queries per second avg: 2.320 # iostat -k 10 Device:tpskB_read/skB_wrtn/skB_readkB_wrtn dev3-0 268.40 7623.20 3.60 76232 36 - 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