数据库2000W条信息。数据表有的是几W条,最多的是60W条信息左右。运行SQL:update 表1 set 字段1=9999结果:几W条信息的可以同时运行6条SQL语句,60W多万条信息的数据表中无法运行SQL语句。运行到一半就无法显示(在phpmyadmin中操作) 请问配置方面哪些需要做改进,谢谢!!
MYSQL 版本 :mysqlnd 5.0.8MYSQL 配置:
| protocol_version | 10
|
| proxy_user |
|
| pseudo_thread_id | 265
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 9437184
|
| query_cache_type | ON
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 8192
|
| rand_seed1 | 0
|
| rand_seed2 | 0
|
| range_alloc_block_size | 4096
|
| read_buffer_size | 65536
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log |
|
| relay_log_index |
|
| relay_log_info_file | relay-log.info
|
| relay_log_purge | ON
|
| relay_log_recovery | OFF
|
| relay_log_space_limit | 0
|
| report_host |
|
| report_password |
|
| report_port | 3306
|
| report_user |
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| secure_file_priv |
|
| server_id | 0
|
| shared_memory | OFF
|
| shared_memory_base_name | MYSQL
|
| skip_external_locking | ON
|
| skip_name_resolve | OFF
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_compressed_protocol | OFF
|
| slave_exec_mode | STRICT
|
| slave_load_tmpdir | C:\WINDOWS\TEMP
|
| slave_net_timeout | 3600
|
| slave_skip_errors | OFF
|
| slave_transaction_retries | 10
|
| slave_type_conversions |
|
| slow_launch_time | 2
|
| slow_query_log | OFF
|
slow_query_log_file | E:\MYSQL\Data\wd1754-slow.
log
|
| socket | MySQL
|
| sort_buffer_size | 262144
|
| sql_auto_is_null | OFF
|
| sql_big_selects | ON
|
| sql_big_tables | OFF
|
| sql_buffer_result | OFF
|
| sql_log_bin | ON
|
| sql_log_off | OFF
|
| sql_low_priority_updates | OFF
|
| sql_max_join_size | 18446744073709551615
|
| sql_mode | STRICT_TRANS_TABLES,NO_AUT
O_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
| sql_notes | ON
|
| sql_quote_show_create | ON
|
| sql_safe_updates | OFF
|
| sql_select_limit | 18446744073709551615
|
| sql_slave_skip_counter | 0
|
| sql_warnings | OFF| storage_engine | InnoDB
|
| sync_binlog | 0
|
| sync_frm | ON
|
| sync_master_info | 0
|
| sync_relay_log | 0
|
| sync_relay_log_info | 0
|
| system_time_zone |
|
| table_definition_cache | 400
|
| table_open_cache | 700
|
| thread_cache_size | 17
|
| thread_concurrency | 10
|
| thread_handling | one-thread-per-connection
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| timestamp | 1304154421
|
| tmp_table_size | 314572800
|
| tmpdir | C:\WINDOWS\TEMP
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| unique_checks | ON
|
| updatable_views_with_limit | YES
|
| version | 5.5.11
|
| version_comment | MySQL Community Server (GP
L)
|
| version_compile_machine | x86
|
| version_compile_os | Win32
|
| wait_timeout | 28800
|
| warning_count | 1
MYSQL 版本 :mysqlnd 5.0.8MYSQL 配置:
| protocol_version | 10
|
| proxy_user |
|
| pseudo_thread_id | 265
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 9437184
|
| query_cache_type | ON
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 8192
|
| rand_seed1 | 0
|
| rand_seed2 | 0
|
| range_alloc_block_size | 4096
|
| read_buffer_size | 65536
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log |
|
| relay_log_index |
|
| relay_log_info_file | relay-log.info
|
| relay_log_purge | ON
|
| relay_log_recovery | OFF
|
| relay_log_space_limit | 0
|
| report_host |
|
| report_password |
|
| report_port | 3306
|
| report_user |
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| secure_file_priv |
|
| server_id | 0
|
| shared_memory | OFF
|
| shared_memory_base_name | MYSQL
|
| skip_external_locking | ON
|
| skip_name_resolve | OFF
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_compressed_protocol | OFF
|
| slave_exec_mode | STRICT
|
| slave_load_tmpdir | C:\WINDOWS\TEMP
|
| slave_net_timeout | 3600
|
| slave_skip_errors | OFF
|
| slave_transaction_retries | 10
|
| slave_type_conversions |
|
| slow_launch_time | 2
|
| slow_query_log | OFF
|
slow_query_log_file | E:\MYSQL\Data\wd1754-slow.
log
|
| socket | MySQL
|
| sort_buffer_size | 262144
|
| sql_auto_is_null | OFF
|
| sql_big_selects | ON
|
| sql_big_tables | OFF
|
| sql_buffer_result | OFF
|
| sql_log_bin | ON
|
| sql_log_off | OFF
|
| sql_low_priority_updates | OFF
|
| sql_max_join_size | 18446744073709551615
|
| sql_mode | STRICT_TRANS_TABLES,NO_AUT
O_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
| sql_notes | ON
|
| sql_quote_show_create | ON
|
| sql_safe_updates | OFF
|
| sql_select_limit | 18446744073709551615
|
| sql_slave_skip_counter | 0
|
| sql_warnings | OFF| storage_engine | InnoDB
|
| sync_binlog | 0
|
| sync_frm | ON
|
| sync_master_info | 0
|
| sync_relay_log | 0
|
| sync_relay_log_info | 0
|
| system_time_zone |
|
| table_definition_cache | 400
|
| table_open_cache | 700
|
| thread_cache_size | 17
|
| thread_concurrency | 10
|
| thread_handling | one-thread-per-connection
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| timestamp | 1304154421
|
| tmp_table_size | 314572800
|
| tmpdir | C:\WINDOWS\TEMP
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| unique_checks | ON
|
| updatable_views_with_limit | YES
|
| version | 5.5.11
|
| version_comment | MySQL Community Server (GP
L)
|
| version_compile_machine | x86
|
| version_compile_os | Win32
|
| wait_timeout | 28800
|
| warning_count | 1
解决方案 »
- mysql中 ,自动增长列计算问题。在线等!!!!!!!!!!!!1
- sql语句 is not null效率高么
- 有个疑问想请教。
- merge能否自动建立子表
- 有关求无限分级目录下的产品信息的SQL
- 100大分,寻高手解决!SQL2000下的MYSQL链接服务器不能执行远程INSERT
- 请教高手!Fedora 5 用C call MySQL 出现 Lost connection to MySQL Server during query 什么原因?
- mysql服务异常中断,在线求救附错误日志
- c++连接mysql问题
- 【求助】从A库提取的mixed格式的binlog,有没有办法把sql操作还原到新安装的B库上?
- 《PHP和MYSQL WEB开发》第4版 P171页(9.5.5 使用GRANT和REVOKE的例子) 有错 求解
- 问一问,怎样才可以实现数据库高效存储,查询和修改...?
-> ;
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.00 sec)mysql>