环境:Redhat Linux AS 4 下使用MySQL 5.0.2。使用InnoDB,数据存储采用裸设备。
/etc/my.cnf的内容:
[mysqld]
#datadir=/var/NSN/SS_ADAPP_MYSQL/data
datadir=/var/lib/mysql_raw/data
socket=/var/lib/mysql/mysql.sock
#socket=/var/lib/mysql_raw/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
 
innodb_data_home_dir =
innodb_data_file_path = /dev/mapper/emc0-mysql_80G:70Gnewraw
 
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=1G
innodb_log_buffer_size=256M
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=256M
innodb_lock_wait_timeout = 50
innodb_additional_mem_pool_size = 20M
#innodb_file_io_threads=16
innodb_thread_concurrency = 8
 
thread_concurrency = 8
max_connections = 200
max_allowed_packet = 4M
 
skip-locking
key_buffer = 384M
max_allowed_packet = 4M
table_cache = 512
#read_rnd_buffer_size = 4M
 
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
#myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_size = 64M
 
#thread_cache_size = 64
#query_cache_size= 64M
query_cache_limit=256M
query_cache_type=1
 
[mysql.server]
user=mysql
basedir=/var/lib/mysql_raw
 
[mysqld_safe]
#log-error=/var/lib/mysql/mysqld.log
log-error=/var/lib/mysql_raw/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
pid-file=/var/lib/mysql_raw/mysqld.pid
 
[mysql]
no-auto-rehash

/dev/mapper/emc0-mysql_80G有80G的空间,在配置中只使用70G。
目前的情况是:
当我在配置好后,我在MySQL中建立XE数据库,数据库里建一张表,type为InnoDB。
以一个插入操作,一个读取操作,一个更新操作,一个删除操作为一个业务。
在表为空的情况下,经统计能达到上千个业务每秒。
但当我插入100万条记录在表里,再进行同样的业务操作,只能达到1个业务每秒。高手指点看我的配置等是否有问题,性能为何会下降的如此离谱?谢谢

解决方案 »

  1.   

    show status;如下
    +-----------------------------------+----------+
    | Variable_name                     | Value    |
    +-----------------------------------+----------+
    | Aborted_clients                   | 0        | 
    | Aborted_connects                  | 2        | 
    | Binlog_cache_disk_use             | 0        | 
    | Binlog_cache_use                  | 0        | 
    | Bytes_received                    | 58       | 
    | Bytes_sent                        | 67       | 
    ......
    | Com_xa_start                      | 0        | 
    | Compression                       | OFF      | 
    | Connections                       | 5        | 
    | Created_tmp_disk_tables           | 0        | 
    | Created_tmp_files                 | 4        | 
    | Created_tmp_tables                | 1        | 
    | Delayed_errors                    | 0        | 
    | Delayed_insert_threads            | 0        | 
    | Delayed_writes                    | 0        | 
    | Flush_commands                    | 1        | 
    | Handler_commit                    | 0        | 
    | Handler_delete                    | 0        | 
    | Handler_discover                  | 0        | 
    | Handler_prepare                   | 0        | 
    | Handler_read_first                | 0        | 
    | Handler_read_key                  | 0        | 
    | Handler_read_next                 | 0        | 
    | Handler_read_prev                 | 0        | 
    | Handler_read_rnd                  | 0        | 
    | Handler_read_rnd_next             | 0        | 
    | Handler_rollback                  | 0        | 
    | Handler_savepoint                 | 0        | 
    | Handler_savepoint_rollback        | 0        | 
    | Handler_update                    | 0        | 
    | Handler_write                     | 130      | 
    | Innodb_buffer_pool_pages_data     | 0        | 
    | Innodb_buffer_pool_pages_dirty    | 0        | 
    | Innodb_buffer_pool_pages_flushed  | 0        | 
    | Innodb_buffer_pool_pages_free     | 65536    | 
    | Innodb_buffer_pool_pages_latched  | 0        | 
    | Innodb_buffer_pool_pages_misc     | 0        | 
    | Innodb_buffer_pool_pages_total    | 65536    | 
    | Innodb_buffer_pool_read_ahead_rnd | 0        | 
    | Innodb_buffer_pool_read_ahead_seq | 0        | 
    | Innodb_buffer_pool_read_requests  | 0        | 
    | Innodb_buffer_pool_reads          | 0        | 
    | Innodb_buffer_pool_wait_free      | 0        | 
    | Innodb_buffer_pool_write_requests | 0        | 
    | Innodb_data_fsyncs                | 1        | 
    | Innodb_data_pending_fsyncs        | 0        | 
    | Innodb_data_pending_reads         | 0        | 
    | Innodb_data_pending_writes        | 0        | 
    | Innodb_data_read                  | 0        | 
    | Innodb_data_reads                 | 0        | 
    | Innodb_data_writes                | 71680    | 
    | Innodb_data_written               | 0        | 
    | Innodb_dblwr_pages_written        | 0        | 
    | Innodb_dblwr_writes               | 0        | 
    | Innodb_log_waits                  | 0        | 
    | Innodb_log_write_requests         | 0        | 
    | Innodb_log_writes                 | 0        | 
    | Innodb_os_log_fsyncs              | 0        | 
    | Innodb_os_log_pending_fsyncs      | 0        | 
    | Innodb_os_log_pending_writes      | 0        | 
    | Innodb_os_log_written             | 0        | 
    | Innodb_page_size                  | 16384    | 
    | Innodb_pages_created              | 0        | 
    | Innodb_pages_read                 | 0        | 
    | Innodb_pages_written              | 0        | 
    | Innodb_row_lock_current_waits     | 0        | 
    | Innodb_row_lock_time              | 0        | 
    | Innodb_row_lock_time_avg          | 0        | 
    | Innodb_row_lock_time_max          | 0        | 
    | Innodb_row_lock_waits             | 0        | 
    | Innodb_rows_deleted               | 0        | 
    | Innodb_rows_inserted              | 0        | 
    | Innodb_rows_read                  | 0        | 
    | Innodb_rows_updated               | 0        | 
    | Key_blocks_not_flushed            | 0        | 
    | Key_blocks_unused                 | 323359   | 
    | Key_blocks_used                   | 0        | 
    | Key_read_requests                 | 0        | 
    | Key_reads                         | 0        | 
    | Key_write_requests                | 0        | 
    | Key_writes                        | 0        | 
    | Last_query_cost                   | 0.000000 | 
    | Max_used_connections              | 1        | 
    | Not_flushed_delayed_rows          | 0        | 
    | Open_files                        | 12       | 
    | Open_streams                      | 0        | 
    | Open_tables                       | 6        | 
    | Opened_tables                     | 0        | 
    | Qcache_free_blocks                | 1        | 
    | Qcache_free_memory                | 67091232 | 
    | Qcache_hits                       | 0        | 
    | Qcache_inserts                    | 0        | 
    | Qcache_lowmem_prunes              | 0        | 
    | Qcache_not_cached                 | 1        | 
    | Qcache_queries_in_cache           | 0        | 
    | Qcache_total_blocks               | 1        | 
    | Questions                         | 2        | 
    | Rpl_status                        | NULL     | 
    | Select_full_join                  | 0        | 
    | Select_full_range_join            | 0        | 
    | Select_range                      | 0        | 
    | Select_range_check                | 0        | 
    | Select_scan                       | 1        | 
    | Slave_open_temp_tables            | 0        | 
    | Slave_retried_transactions        | 0        | 
    | Slave_running                     | OFF      | 
    | Slow_launch_threads               | 0        | 
    | Slow_queries                      | 0        | 
    | Sort_merge_passes                 | 0        | 
    | Sort_range                        | 0        | 
    | Sort_rows                         | 0        | 
    | Sort_scan                         | 0        | 
    | Ssl_accept_renegotiates           | 0        | 
    | Ssl_accepts                       | 0        | 
    | Ssl_callback_cache_hits           | 0        | 
    | Ssl_cipher                        |          | 
    | Ssl_cipher_list                   |          | 
    | Ssl_client_connects               | 0        | 
    | Ssl_connect_renegotiates          | 0        | 
    | Ssl_ctx_verify_depth              | 0        | 
    | Ssl_ctx_verify_mode               | 0        | 
    | Ssl_default_timeout               | 0        | 
    | Ssl_finished_accepts              | 0        | 
    | Ssl_finished_connects             | 0        | 
    | Ssl_session_cache_hits            | 0        | 
    | Ssl_session_cache_misses          | 0        | 
    | Ssl_session_cache_mode            | NONE     | 
    | Ssl_session_cache_overflows       | 0        | 
    | Ssl_session_cache_size            | 0        | 
    | Ssl_session_cache_timeouts        | 0        | 
    | Ssl_sessions_reused               | 0        | 
    | Ssl_used_session_cache_entries    | 0        | 
    | Ssl_verify_depth                  | 0        | 
    | Ssl_verify_mode                   | 0        | 
    | Ssl_version                       |          | 
    | Table_locks_immediate             | 12       | 
    | Table_locks_waited                | 0        | 
    | Tc_log_max_pages_used             | 0        | 
    | Tc_log_page_size                  | 0        | 
    | Tc_log_page_waits                 | 0        | 
    | Threads_cached                    | 0        | 
    | Threads_connected                 | 1        | 
    | Threads_created                   | 1        | 
    | Threads_running                   | 1        | 
    | Uptime                            | 650      | 
    +-----------------------------------+----------+同时,每个业务的插入,删除都会对50K的BLOB数据进行操作。100万条记录也是包含了每条50k的BLOB数据
      

  2.   

    4个字段,一个整形id,一个整形status,一个BLOB,一个时间戳
      

  3.   

    BLOB字段中存入的是什么内容?
      

  4.   

    去掉BLOB字段测试一下速度如何,对BLOB字段操作是比较费时的。
      

  5.   

    关键看你是如何操纵那个blob的,insert时,如果是直接send blob,可以提高效率不少。
    如果是先select出来,再update,那效率肯定要下降。我忘了5.0是否支持表分区。
      

  6.   

    官方文档的一段话: 警告:在32位GNU/Linux x86上,你必须要小心不要设置过高的内存用量。glibc可能允许进程堆积在线程堆栈上发展,它会造成你的服务器崩溃。如果下列表达式的值接近或者超过2GB,系统会面临危机:innodb_buffer_pool_size+ key_buffer_size+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)+ max_connections*2MB每个线程使用一个堆栈(通常是2MB,但在MySQL AB二进制分发版里只有256KB)并且在最坏的情况下也使用sort_buffer_size + read_buffer_size附加内存。你可以自己编译MySQL,在32位Windows上使用高达64GB物理内存。请参阅15.2.4节,“InnoDB启动选项”里对innodb_buffer_pool_awe_mem_mb的描述。 如何调整其它mysqld服务器参数?下列值是典型的,且适用于多数用户:[mysqld]skip-external-lockingmax_connections=200read_buffer_size=1Msort_buffer_size=1M## Set key_buffer to 5 - 50% of your RAM depending on how much# you use MyISAM tables, but keep key_buffer_size + InnoDB# buffer pool size < 80% of your RAMkey_buffer_size=value
      

  7.   

    ·         innodb_log_buffer_size InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小。明智的值是从1MB到8MB。默认的是1MB。一个大的日志缓冲允许大型事务运行而不需要在事务提交之前往磁盘写日志。因此,如果你有大型事务,使日志缓冲区更大以节约磁盘I/O
      

  8.   

    今天跟一个用Innodb比较多同事聊天聊到楼主的这个问题,一个表超过100w条记录之后,性能会下降挺多的
    好的办法就是,分表,按照号码或者其他什么规则,分成多个表,10到100个,都可以,这样,一个表才那么一点数据的。。
    楼主可以试一下
      

  9.   

    理论上说,MYSQL是个小型数据库,如果一个表到了10万以上记录就会很吃力。最简单的解决方法是换ORACLE我从你提问的角度上看觉得你应该不打算做程序上的性能优化。
      

  10.   


    请问如何换成ORACLE?谢谢!