麻烦楼主再将SHOW VARIABLES的结果贴一下,以及机器的CPU、内存等状况
解决方案 »
- 分不多了,请教个问题,好像有点难度
- sql脚本 如何判断字段或表是否存在
- 联接查询的问题
- 连接局域网里mysql数据库时无法连接
- 初学者,对my.ini这个文件使用的理解
- myisam btree 索引的弊端?
- MYSQL中把文档文件载入表
- 从PostgreSQL数据库中的数据库中直接生成E-R图
- 单条记录的长度是否会影响数据库查询的效率?
- Mysql设置默认编码集为UTF-8,仍无法输入中文,求解
- 我是MYSQL新手,请教各位大虾,在MYSQL中如何创建字符集为“UTF-8”或为“ISO8859-1”的数据库,能否给出SQL具体语句,万分感谢,我的VER
- 请问MySQL中的 join 语句是怎么个用法?能否举几个比较生动的例子:)
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
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_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
16314 apache 16 0 8784 8784 7248 S 7.7 0.8 0:00 httpd
16774 mysql 17 0 28232 27M 2232 R 5.9 2.7 0:00 mysqld
16092 apache 15 0 8560 8560 7216 S 3.1 0.8 0:01 httpd
16116 apache 16 0 0 0 0 Z 2.7 0.0 0:01 httpd <defunct>
16117 apache 15 0 8644 8644 7252 S 2.7 0.8 0:03 httpd
16311 apache 15 0 8676 8676 7216 S 2.3 0.8 0:00 httpd
16118 apache 15 0 0 0 0 Z 1.9 0.0 0:01 httpd <defunct>
16121 apache 16 0 8652 8652 7244 S 1.5 0.8 0:00 httpd
16315 apache 15 0 8672 8672 7240 S 1.5 0.8 0:00 httpd
16745 apache 16 0 8100 8100 7280 S 1.3 0.7 0:00 httpd
12754 zfw 15 0 1068 1068 836 R 1.1 0.1 0:03 top
16751 apache 15 0 7964 7964 7228 S 1.1 0.7 0:00 httpd
16093 apache 15 0 8404 8404 7184 S 0.9 0.8 0:00 httpd
16249 apache 15 0 8328 8328 7244 R 0.9 0.8 0:00 httpd
16299 apache 15 0 8624 8624 7244 S 0.9 0.8 0:00 httpd
16301 apache 15 0 8604 8604 7216 S 0.7 0.8 0:00 httpd
16443 apache 15 0 8656 8656 7240 S 0.7 0.8 0:00 httpd
16115 apache 15 0 8684 8684 7188 S 0.5 0.8 0:02 httpd
16313 apache 15 0 8544 8544 7272 S 0.5 0.8 0:00 httpd
16431 apache 15 0 8308 8308 7188 S 0.5 0.8 0:00 httpd
16288 apache 15 0 8580 8580 7244 S 0.3 0.8 0:00 httpd
16310 apache 15 0 8636 8636 7256 S 0.3 0.8 0:01 httpd
16312 apache 15 0 8808 8808 7260 S 0.3 0.8 0:00 httpd
16432 apache 15 0 8080 8080 7252 S 0.3 0.7 0:00 httpd
16445 apache 15 0 8520 8520 7244 S 0.3 0.8 0:00 httpd
16752 apache 15 0 7824 7824 7196 S 0.3 0.7 0:00 httpd
8014 root 15 0 2032 2032 1700 S 0.1 0.1 0:00 sshd
1 root 15 0 484 484 420 S 0.0 0.0 0:03 init
2 root 0K 0 0 0 0 SW 0.0 0.0 0:00 migration_CPU0
3 root 0K 0 0 0 0 SW 0.0 0.0 0:00 migration_CPU1
4 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
5 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
6 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU1
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 kswapd
8 root 25 0 0 0 0 SW 0.0 0.0 0:00 bdflush
9 root 15 0 0 0 0 SW 0.0 0.0 0:00 kupdated
10 root 25 0 0 0 0 SW 0.0 0.0 0:00 mdrecoveryd
16 root 25 0 0 0 0 SW 0.0 0.0 0:00 scsi_eh_0
17 root 25 0 0 0 0 SW 0.0 0.0 0:00 scsi_eh_1
20 root 15 0 0 0 0 SW 0.0 0.0 0:00 kjournald
[zfw@club zfw]$ iostat
avg-cpu: %user %nice %sys %idle
37.93 0.00 20.73 41.34Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 64.70 1268.03 224.86 1585372 281128[zfw@club zfw]$ vmstat
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
6 0 1 0 105712 8812 802536 0 0 261 57 479 374 38 21 41
[zfw@club zfw]$ sar
呵呵,俺个人领悟就暂时这么多了,欢迎犬犬、苦丁及众大侠评点!
非常感谢楼上朋友的仔细分析!先给50分,但问题不接,我希望继续讨论。我自己也仔细查资料分析一下。另外,看来也需要调整httpd.conf/
http://kusite.myetang.com/show/mysql-doc/manual_Performance.html#Server_parameters优化真不是那么简单的事情啊,我现在只是调整了一下APACHE的参数,MYSQL的参数怎么调整?SQL语句的优化更有难度,感觉。
为什么我这样
mysqld -O table_cache=80
不能更改呢?