数据库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问题,查询学生表中的学生性别为男,且总成绩大于500.
- sql存储过程分页算法研究
- 编写一个MYSQL存储过程,要求使用事物机制,完成简单的数据写入和统计功能
- sql2000里的数据想四舍五入到小数点后一位。应该怎么设置???
- mysql 查詢問題
- 怎样往日期型的字段里插入数据?
- 关于MYSQL的ROOT用户更改密码的问题!
- 求解一个奇怪的问题
- mysql 索引是否可以去掉几个?复合索引
- 求助!!!create database 显示 not connected!
- 《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>