> Another option is to run the 3.23.51 server for a while and when you > get a load problem do 'mysqladmin proc ext'. This command should show > us if MySQL is using more table scans than usual.
The load problem shows up immediately. +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Aborted_clients | 172 | | Aborted_connects | 87 | | Bytes_received | 92732 | | Bytes_sent | 631789 | | Com_admin_commands | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 990 | | Com_change_master | 0 | | Com_check | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 1 | | Com_delete | 8 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_insert | 49 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_purge | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 11 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_select | 402 | | Com_set_option | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 0 | | Com_show_fields | 0 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 2 | | Com_show_slave_status | 0 | | Com_show_status | 46 | | Com_show_tables | 0 | | Com_show_variables | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 183 | | Connections | 316 | | Created_tmp_disk_tables | 1 | | Created_tmp_tables | 5 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 430 | | Handler_read_first | 5 | | Handler_read_key | 564 | | Handler_read_next | 5441 | | Handler_read_prev | 0 | | Handler_read_rnd | 62 | | Handler_read_rnd_next | 3406 | | Handler_update | 172 | | Handler_write | 123 | | Key_blocks_used | 773 | | Key_read_requests | 5199 | | Key_reads | 770 | | Key_write_requests | 1067 | | Key_writes | 133 | | Max_used_connections | 121 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 147 | | Open_files | 193 | | Open_streams | 0 | | Opened_tables | 155 | | Questions | 1662 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 16 | | Select_range_check | 0 | | Select_scan | 75 | | Slave_running | OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 29 | | Slow_queries | 54 | | Sort_merge_passes | 0 | | Sort_range | 19 | | Sort_rows | 52 | | Sort_scan | 5 | | Table_locks_immediate | 499 | | Table_locks_waited | 188 | | Threads_cached | 64 | | Threads_created | 122 | | Threads_connected | 58 | | Threads_running | 11 | | Uptime | 81 | +--------------------------+--------+ 8:15pm up 1 day, 23:48, 1 user, load average: 49.13, 15.49, 3.84 +-----+----------------------+------------+---------+------------+------ +------------------+------+ | Id | User | Host | db | Command | Time | State | Info | +-----+----------------------+------------+---------+------------+------ +------------------+------+ | 7 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 8 | webuser | webserver | main | Statistics | 46 ||| | 9 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 10 | webuser | webserver | main | Query | 1 | closing tables | update ... | | 16 | webuser | webserver | main | Init DB | 1 | cleaning up | | | 18 | webuser | webserver | main | Statistics | 46 ||| | 20 | webuser | webserver | logging | Query | 0 ||| | 22 | webuser | webserver | logging | Query | 1 | Opening table | update ... | | 28 | webuser | webserver | logging | Query | 1 | closing tables | update ... | | 36 | webuser | webserver | main | Query | 0 | cleaning up | select ... | | 39 | webuser | webserver | main | Init DB | 1 ||| | 45 | webuser | webserver | logging | Init DB | 19 ||| | 59 | webuser | webserver | main | Init DB | 19 ||| | 61 | webuser | webserver | main | Init DB | 19 ||| | 62 | webuser | webserver | main | Sleep | 5 ||| | 66 | webuser | webserver | main | Init DB | 19 ||| | 72 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 77 | webuser | webserver | main | Sleep | 0 ||| | 79 | webuser | webserver | logging | Init DB | 0 ||| | 81 | webuser | webserver | main | Query | 0 | cleaning up | select ... | | 83 | webuser | webserver | main | Init DB | 19 ||| | 90 | webuser | webserver | main | Init DB | 19 ||| | 94 | webuser | webserver | main | Init DB | 19 ||| | 96 | webuser | webserver | main | Query | 0 ||| | 99 | webuser | webserver | main | Init DB | 0 ||| | 101 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 104 | webuser | webserver | main | Init DB | 19 ||| | 109 | webuser | webserver | main | Init DB | 19 ||| | 114 | webuser | webserver | main | Query | 1 ||| | 116 | webuser | webserver | main | Init DB | 5 | cleaning up | | | 119 | webuser | webserver | main | Init DB | 19 ||| | 122 | webuser | webserver | main | Query | 10 | cleaning up | select ... | | 123 | webuser | webserver | logging | Init DB | 2 | cleaning up | | | 130 | webuser | webserver | main | Statistics | 19 ||| | 134 | webuser | webserver | main | Init DB | 0 ||| | 143 | webuser | webserver | logging | Query | 1 | closing tables | insert ... | | 151 | webuser | webserver | logging | Query | 1 | Opening table | insert ... | | 152 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 154 | webuser | webserver | logging | Init DB | 0 ||| | 158 | webuser | webserver | main | Init DB | 1 | cleaning up | | | 159 | webuser | webserver | logging | Query | 1 ||| | 165 | webuser | webserver | main | Statistics | 3 | cleaning up | | | 176 | webuser | webserver | main | Init DB | 19 ||| | 178 | webuser | webserver | main | Init DB | 2 | cleaning up | | | 182 | webuser | webserver | main | Init DB | 2 | cleaning up | | | 185 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 187 | webuser | webserver | logging | Query | 1 | closing tables | delete ... | | 199 | webuser | webserver | main | Query | 1 ||| | 209 | webuser | webserver | main | Init DB | 0 ||| | 217 | webuser | webserver | main | Init DB | 19 ||| | 222 | webuser | webserver | logging | Query | 0 ||| | 226 | webuser | webserver | main | Query | 1 | Opening tables | select ... | | 227 | webuser | webserver | main | Query | 0 ||| | 228 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 229 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 232 | webuser | webserver | main | Statistics | 10 ||| | 239 | webuser | webserver | logging | Sleep | 19 ||| | 240 | webuser | webserver | logging | Init DB | 2 | cleaning up | | | 241 | webuser | webserver | main | Query | 10 | cleaning up | select ... | | 242 | webuser | webserver | main | Init DB | 19 ||| | 243 | webuser | webserver | main | Query | 19 ||| | 246 | webuser | webserver | logging | Sleep | 2 ||| | 247 | webuser | webserver | logging | Init DB | 2 | cleaning up | | | 249 | webuser | webserver | logging | Query | 1 | closing tables | update ... | | 250 | webuser | webserver | logging | Sleep | 43 ||| | 256 | webuser | webserver | main | Sleep | 2 ||| | 259 | webuser | webserver | main | Init DB | 19 ||| | 262 | webuser | webserver | main | Init DB | 19 ||| | 264 | webuser | webserver | logging | Init DB | 19 ||| | 267 | webuser | webserver | main | Init DB | 19 ||| | 270 | webuser | webserver | main | Init DB | 19 ||| | 274 | webuser | webserver | main | Query | 19 ||| | 275 | webuser | webserver | logging | Sleep | 0 ||| | 276 | webuser | webserver | logging | Init DB | 10 ||| | 278 | webuser | webserver | logging | Query | 1 | closing tables | update ... | | 280 | webuser | webserver | main | Query | 1 | closing tables | select ... | | 285 | webuser | webserver | logging | Init DB | 6 | cleaning up | | | 288 | webuser | webserver | logging | Query | 1 | closing tables | update ... | | 295 | webuser | webserver | logging | Init DB | 2 | cleaning up | | | 296 | webuser | webserver | | Connect | | login | | | 300 | root | localhost | | Query | 0 | | show processlist | | 301 | root | localhost | | Connect | | login | | | 302 | unauthenticated user | webserver | | Connect | | login | | | 303 | unauthenticated user | webserver | | Connect | | login | | | 304 | unauthenticated user | webserver | | Connect | | Reading from net | | | 305 | unauthenticated user | webserver | | Connect | | login | | | 306 | unauthenticated user | webserver | | Connect | | login | | | 307 | unauthenticated user | webserver | | Connect | | Reading from net | | | 308 | unauthenticated user | webserver | | Connect | | login | | +-----+----------------------+------------+---------+------------+------ +------------+ The process list was taken a little bit after the other (my ssh tool didn't have enough lines saved to get both simultaneously). Note the uptime in between. > Could you by any change check by using the slow query log if there is > some specific query that is causing problems ? All queries seem to be slow. Not every query going in, but every type. Almost everything in there is for 7-8 seconds (0 for lock). Simple stuff like 'select col from table where id_col = 123' and the id_col is an auto inc primary key. Explain works fine (but slow too). BTW - the smack tests use UNIX sockets right? That test works fine, but the real load is from TCP connections, which dies. Could this have anything to do with it? Tomorrow I'll try doing my own compile with gcc3.1. Note the other guy that had the same problem that went away after he compiled it himself: http://marc.theaimsgroup.com/?l=mysql&m=102537522606976&w=2 In that case, I doubt he has the altered glibc compiled in. Could changes there have this effect? Sincerely, Steven Roussey http://Network54.com/?pp=e --------------------------------------------------------------------- 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
