一个 my.cnf 请高人看看有没有问题

[Admstor]

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /home/mysqldata/
skip-locking
key_buffer_size = 768M
max_allowed_packet = 999M
table_open_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connections = 10000

interactive_timeout = 10000
wait_timeout = 10000

log_slow_queries = ON
log_slow_queries=slow-log
long_query_time=5

log-queries-not-using-indexes


########
skip-locking
skip-name-resolve

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
log-bin=mysql-bin


# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 999M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 768M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[Admstor]

主要是一个门户网站,客户总是反应数据库慢慢慢,有时候查看的确mysql占用CPU高达400%
目前CPU是4核,内存32G
系统为centos 6.3 64位

我是运维方面,代码我管不到也不会去管...
所以即便从不少结果看,100%是代码的问题,例如,开启后1小时不到
Slow_queries我设置3秒,结果大概十几分钟就有2.3K记录
Handler_read_rnd_next 151.8M
Handler_read_rnd 1.2M

请教的是,如果尽可能的提高服务器的负载水平,以及尽可能在这种情况下,保证不崩溃

[minbaby]

看慢查询,然后分析,然后告诉开发人员,哪些sql查询有问题,让他们改,

[towser]

“Slow_queries我设置3秒,结果大概十几分钟就有2.3K记录”。
还不改程序更待何时？

[shiny]

通过 my.cnf 优化提升的空间是有限的，最好还是分析 slow queries 然后检查语句、数据库结构、索引。要不就是硬件层次的改善，提升硬盘 IO 性能、加内存。

[akira]

分析慢查询吧，这种情况优化sql带来的效果更明显

[Admstor]

这个其实我也是这么和客户说的,但是你们懂的......
加上我对mysql的优化也只是浅浅的了解,所以想请高人看看这个配置文件可有什么不妥当的地方
Slow_queries其实也挺奇怪,里面有些语句我单独执行的时候也是很快

很多时候发现CPU占用极高的,用show processlist查看,会有大量的sleep和query
感觉像是表锁了

[dbow]

@Admstor myisam 还是innodb?

[Admstor]

@dbow 基本都是myisam

[feuvan]

锁表的操作太多了吧。。

[dbow]

@Admstor explain一下那些慢查询，看看扫描的rows是不是巨大, 看看有没有filesort和tmp table, 看看查询用的什么索引.

[simapple]

多大的数据量，看索引，索引是多大，能不能再优化索引，服务器内存还可以，把索引做好，全部加载到内存去，你开启了慢查询就按个优化吧

[Admstor]

数据库1G多点...
谢谢各位我去看看