收录日期:2018/09/20 11:44:00 时间:2016/05/21 14:12:17 标签:基础编程
当然是快的时候多,但还是表现出不稳定,我用netstat -a看到很多TCP等待的连接,使用下面的命令看到数据库情况是这样:大哥们能不能帮我分析分析?什么原因?数据库参数设置问题?那么应该怎么设置?

mysql> show status;  
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 800        |
| Aborted_connects         | 79         |
| Bytes_received           | 3173510002 |
| Bytes_sent               | 1644654028 |
| Com_admin_commands       | 0          |
| Com_alter_table          | 0          |
| Com_analyze              | 0          |
| Com_backup_table         | 0          |
| Com_begin                | 0          |
| Com_change_db            | 4871035    |
| 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         | 0          |
| Com_delete               | 285292     |
| Com_drop_db              | 0          |
| Com_drop_function        | 0          |
| Com_drop_index           | 0          |
| Com_drop_table           | 0          |
| Com_flush                | 17         |
| Com_grant                | 0          |
| Com_insert               | 351966     |
| 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              | 0          |
| Com_replace_select       | 0          |
| Com_reset                | 0          |
| Com_restore_table        | 0          |
| Com_revoke               | 0          |
| Com_rollback             | 0          |
| Com_select               | 27220811   |
| Com_set_option           | 0          |
| Com_show_binlogs         | 0          |
| Com_show_create          | 0          |
| Com_show_databases       | 31         |
| Com_show_fields          | 295        |
| Com_show_grants          | 0          |
| Com_show_keys            | 0          |
| Com_show_logs            | 0          |
| Com_show_master_stat     | 0          |
| Com_show_open_tables     | 0          |
| Com_show_processlist     | 1          |
| Com_show_slave_stat      | 0          |
| Com_show_status          | 2          |
| Com_show_tables          | 31         |
| Com_show_variables       | 1          |
| Com_slave_start          | 0          |
| Com_slave_stop           | 0          |
| Com_truncate             | 0          |
| Com_unlock_tables        | 0          |
| Com_update               | 5861896    |
| Connections              | 4870953    |
| Created_tmp_disk_tables  | 106        |
| Created_tmp_tables       | 8403       |
| Created_tmp_files        | 482        |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 18         |
| Handler_delete           | 68862      |
| Handler_read_first       | 1276       |
| Handler_read_key         | 56724207   |
| Handler_read_next        | 849982457  |
| Handler_read_prev        | 78533625   |
| Handler_read_rnd         | 276515324  |
| Handler_read_rnd_next    | 4166066082 |
| Handler_update           | 12602302   |
| Handler_write            | 2599269    |
| Key_blocks_used          | 7793       |
| Key_read_requests        | 658875025  |
| Key_reads                | 1445244    |
| Key_write_requests       | 1312804    |
| Key_writes               | 1224859    |
| Max_used_connections     | 229        |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 64         |
| Open_files               | 72         |
| Open_streams             | 0          |
| Opened_tables            | 250231     |
| Questions                | 43513355   |
| Select_full_join         | 0          |
| Select_full_range_join   | 0          |
| Select_range             | 2627       |
| Select_range_check       | 0          |
| Select_scan              | 465859     |
| Slave_running            | OFF        |
| Slave_open_temp_tables   | 0          |
| Slow_launch_threads      | 1183       |
| Slow_queries             | 6653       |
| Sort_merge_passes        | 241        |
| Sort_range               | 4923624    |
| Sort_rows                | 383868037  |
| Sort_scan                | 120638     |
| Table_locks_immediate    | 31998810   |
| Table_locks_waited       | 1840082    |
| Threads_cached           | 0          |
| Threads_created          | 4870952    |
| Threads_connected        | 11         |
| Threads_running          | 3          |
| Uptime                   | 1126852    |
能说清楚些吗
mysql> show variables;
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Variable_name                    Value                                                                                                                                                                                                   
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 back_log                         50                                                                                                                                                                                                      
 basedir                          /usr/                                                                                                                                                                                                   
 bdb_cache_size                   8388600                                                                                                                                                                                                 
 bdb_log_buffer_size              32768                                                                                                                                                                                                   
 bdb_home                         /var/lib/mysql/                                                                                                                                                                                         
 bdb_max_lock                     10000                                                                                                                                                                                                   
 bdb_logdir                                                                                                                                                                                                                               
 bdb_shared_data                  OFF                                                                                                                                                                                                     
 bdb_tmpdir                       /tmp/                                                                                                                                                                                                   
 bdb_version                      Sleepycat Software: Berkeley DB 3.2.9a: (February 14, 2002)                                                                                                                                             
 binlog_cache_size                32768                                                                                                                                                                                                   
 character_set                    latin1                                                                                                                                                                                                  
 character_sets                   latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 
 concurrent_insert                ON                                                                                                                                                                                                      
 connect_timeout                  5                                                                                                                                                                                                       
 datadir                          /var/lib/mysql/                                                                                                                                                                                         
 delay_key_write                  ON                                                                                                                                                                                                      
 delayed_insert_limit             100                                                                                                                                                                                                     
 delayed_insert_timeout           300                                                                                                                                                                                                     
 delayed_queue_size               1000                                                                                                                                                                                                    
 flush                            OFF                                                                                                                                                                                                     
 flush_time                       0                                                                                                                                                                                                       
 have_bdb                         YES                                                                                                                                                                                                     
 have_gemini                      NO                                                                                                                                                                                                      
 have_innodb                      DISABLED                                                                                                                                                                                                
 have_isam                        YES                                                                                                                                                                                                     
 have_raid                        NO                                                                                                                                                                                                      
 have_openssl                     NO                                                                                                                                                                                                      
 init_file                                                                                                                                                                                                                                
 innodb_additional_mem_pool_size  1048576                                                                                                                                                                                                 
 innodb_buffer_pool_size          8388608
mysql> show variables;
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Variable_name                    Value                                                                                                                                                                                                   
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 back_log                         50                                                                                                                                                                                                      
 basedir                          /usr/                                                                                                                                                                                                   
 bdb_cache_size                   8388600                                                                                                                                                                                                 
 bdb_log_buffer_size              32768                                                                                                                                                                                                   
 bdb_home                         /var/lib/mysql/                                                                                                                                                                                         
 bdb_max_lock                     10000                                                                                                                                                                                                   
 bdb_logdir                                                                                                                                                                                                                               
 bdb_shared_data                  OFF                                                                                                                                                                                                     
 bdb_tmpdir                       /tmp/                                                                                                                                                                                                   
 bdb_version                      Sleepycat Software: Berkeley DB 3.2.9a: (February 14, 2002)                                                                                                                                             
 binlog_cache_size                32768                                                                                                                                                                                                   
 character_set                    latin1                                                                                                                                                                                                  
 character_sets                   latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 
 concurrent_insert                ON                                                                                                                                                                                                      
 connect_timeout                  5                                                                                                                                                                                                       
 datadir                          /var/lib/mysql/                                                                                                                                                                                         
 delay_key_write                  ON                                                                                                                                                                                                      
 delayed_insert_limit             100                                                                                                                                                                                                     
 delayed_insert_timeout           300                                                                                                                                                                                                     
 delayed_queue_size               1000                                                                                                                                                                                                    
 flush                            OFF                                                                                                                                                                                                     
 flush_time                       0                                                                                                                                                                                                       
 have_bdb                         YES                                                                                                                                                                                                     
 have_gemini                      NO                                                                                                                                                                                                      
 have_innodb                      DISABLED                                                                                                                                                                                                
 have_isam                        YES                                                                                                                                                                                                     
 have_raid                        NO                                                                                                                                                                                                      
 have_openssl                     NO                                                                                                                                                                                                      
 init_file                                                                                                                                                                                                                                
 innodb_additional_mem_pool_size  1048576                                                                                                                                                                                                 
 innodb_buffer_pool_size          8388608
innodb_data_file_path
innodb_data_home_dir
innodb_file_io_threads                4
innodb_force_recovery0
innodb_thread_concurrency             8
innodb_flush_log_at_trx_commit         OFF
innodb_fast_shutdown                  OFF
innodb_flush_method
innodb_lock_wait_timeout               50
innodb_log_arch_dir
innodb_log_archive                     OFF
innodb_log_buffer_size                1048576
innodb_log_file_size                  5242880
innodb_log_files_in_group             2
innodb_log_group_home_dir
innodb_mirrored_log_groups            1
interactive_timeout                   28800
join_buffer_size                      131072
key_buffer_size                        8388600
language/usr/share/mysql/english/
large_files_support                    ON
locked_in_memory                         OFF
log                                       OFF
log_update                                OFF
log_bin                                  OFF
log_slave_updates                       OFF
log_long_queries                         OFF
long_query_time                       10
low_priority_updates                   OFF
lower_case_table_names                  0
max_allowed_packet                   1048576
max_binlog_cache_size                4294967295
max_binlog_size                      1073741824
max_connections                      300
max_connect_errors                   10
max_delayed_threads                20
max_heap_table_size                 16777216
max_join_size                     4294967295
max_sort_length                     1024
max_user_connections                   0
max_tmp_tables                      32
max_write_lock_count              4294967295
myisam_max_extra_sort_file_size    256
myisam_max_sort_file_size           2047
myisam_recover_options              0
myisam_sort_buffer_size              8388608
net_buffer_length                     16384
net_read_timeout                     30
net_retry_count                       10
net_write_timeout                    60
open_files_limit                         0
pid_file/var/run/mysqld/mysqld.pid
port                                3306
protocol_version                         10
record_buffer                          131072
record_rnd_buffer                      131072
query_buffer_size                      0
safe_show_database                      OFF
server_id                                 0
slave_net_timeout                        3600
skip_locking                        ON
skip_networking                       OFF
skip_show_database                         OFF
slow_launch_time                          2
socket/var/lib/mysql/mysql.sock
sort_buffer                               2097144
sql_mode                                  0
table_cache                                64
table_typeMYISAM
thread_cache_size                             0
thread_stack                               65536
transaction_isolation                 READ-COMMITTED
timezoneCST
tmp_table_size                          33554432
tmpdir/tmp/
version                             3.23.49
wait_timeout                               28800