show statusHi all, I am running MySQL version 4.0.14-standard-log on Suse Linux 2.4.20 i686. All tables in my db are InnoDB tables.
Today morning I've got troubles with perfomance... Generally all queries are slower in 4-5 times than usually. Also I see that number of deadlocks is in 10-15 times more than usually. I didn't change any code that is working with MySQL, didn't upgrade any software on the server, didn't change any configuration parameters. Last week everything worked perfectly... On weekend I've executed 3 commands: 1. OPTIMIZE TABLES <list of all my tables> 2. ANALYZE TABLE for every table 3. CHECK TABLE for every table No errors. I've checked /var/log/messages and MySQL log file as well. Everything looks good. I have no idea what's happend :( Any suggestions, comments, question are really appreciated. Thanks in advace, Mikhail. Below are outputs of 'show variables' and 'show status' commands. show variables (121 Records) Variable_nameValue back_log 50 basedir /usr/local/mysql-standard-4.0.14-pc-linux-i686/ binlog_cache_size 32768 bulk_insert_buffer_size 1048576 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 convert_character_set datadir /usr/local/mysql/data/ default_week_format 0 delay_key_write OFF delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -><()~*:""&| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_stopword_file (built-in) have_bdb NO have_crypt YES have_innodb YES have_isam YES have_raid NO have_symlink YES have_openssl NO have_query_cache YES init_file innodb_additional_mem_pool_size 20971520 innodb_buffer_pool_size 335544320 innodb_data_file_path ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata 6:10M:autoextend innodb_data_home_dir /usr/local/mysql/data/ innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdown ON innodb_flush_method O_DSYNC innodb_lock_wait_timeout 50 innodb_log_arch_dir /usr/local/mysql/data/ innodb_log_archive OFF innodb_log_buffer_size 8388608 innodb_log_file_size 83886080 innodb_log_files_in_group 2 innodb_log_group_home_dir /usr/local/mysql/data/ innodb_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 interactive_timeout 28800 join_buffer_size 520192 key_buffer_size 2097152 language /usr/local/mysql-standard-4.0.14-pc-linux-i686/share/mysql/english/ large_files_support ON local_infile ON locked_in_memory OFF log OFF log_update OFF log_bin ON log_slave_updates OFF log_slow_queries ON log_warnings ON long_query_time 10 low_priority_updates OFF lower_case_table_names OFF max_allowed_packet 1047552 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 268435456 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_recover_options OFF myisam_sort_buffer_size 67108864 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF open_files_limit 0 pid_file /usr/local/mysql/data/blackcat.pid log_error port 3306 protocol_version 10 read_buffer_size 1044480 read_only OFF read_rnd_buffer_size 1044480 rpl_recovery_rank 0 query_cache_limit 1048576 query_cache_size 16777216 query_cache_type ON server_id 1 slave_net_timeout 3600 skip_external_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /tmp/mysql.sock sort_buffer_size 4194296 sql_mode 0 table_cache 256 table_type MYISAM thread_cache_size 8 thread_stack 126976 tx_isolation REPEATABLE-READ timezone GMT tmp_table_size 41943040 tmpdir /tmp/ version 4.0.14-standard-log wait_timeout 28800 show status (132 Records) Variable_nameValue Aborted_clients 1094 Aborted_connects 9 Bytes_received 3370224049 Bytes_sent 1615721075 Com_admin_commands 1536 Com_alter_table 0 Com_analyze 1 Com_backup_table 0 Com_begin 3360480 Com_change_db 625 Com_change_master 0 Com_check 1 Com_commit 3360480 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 362871 Com_delete 8 Com_delete_multi 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 245 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_insert 4071111 Com_insert_select 362737 Com_kill 2 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 8 Com_optimize 0 Com_purge 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 2 Com_restore_table 0 Com_revoke 0 Com_rollback 0 Com_savepoint 0 Com_select 649791 Com_set_option 1398 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_create 272 Com_show_databases 7 Com_show_fields 388 Com_show_grants 0 Com_show_keys 117 Com_show_logs 0 Com_show_master_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_processlist 39543 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 39841 Com_show_innodb_status 4 Com_show_tables 30 Com_show_variables 39 Com_slave_start 0 Com_slave_stop 0 Com_truncate 362614 Com_unlock_tables 0 Com_update 206724 Connections 737 Created_tmp_disk_tables 0 Created_tmp_tables 388850 Created_tmp_files 30 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 3360480 Handler_delete 0 Handler_read_first 272510 Handler_read_key 292789727 Handler_read_next 421619197 Handler_read_prev 0 Handler_read_rnd 4277200 Handler_read_rnd_next 454587400 Handler_rollback 842 Handler_update 100397800 Handler_write 8643791 Key_blocks_used 2 Key_read_requests 9 Key_reads 2 Key_write_requests 2 Key_writes 2 Max_used_connections 15 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables 101 Open_files 5 Open_streams 0 Opened_tables 726995 Questions 32648923 Qcache_queries_in_cache 38 Qcache_inserts 124900 Qcache_hits 19829412 Qcache_lowmem_prunes 0 Qcache_not_cached 524902 Qcache_free_memory 15104144 Qcache_free_blocks 2 Qcache_total_blocks 92 Rpl_status NULL Select_full_join 92037 Select_full_range_join 0 Select_range 375241 Select_range_check 0 Select_scan 2800 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 94859 Sort_merge_passes 0 Sort_range 811 Sort_rows 4173027 Sort_scan 384890 Table_locks_immediate 7202620 Table_locks_waited 0 Threads_cached 2 Threads_created 51 Threads_connected 14 Threads_running 1 Uptime 674972 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
