文章/答案/技术大牛

发布

问MySQL优化
EN

Server Fault用户

提问于 2010-06-07 15:05:37

回答 5查看 879关注 0票数 3

我的uses服务器使用专用主机：

Intel(R) Xeon(R) CPU E5620 8core
12Gram
Centos32bit/Driectadmin
DISK SAS 80G
Php-cgi

这个主机正在运行一个网站。

Use wordpress 2.92(+plugin cache...)
Database size 600MB
only 100online

我的网站运行得很慢。请帮我配置my.cnf。

    [mysqld]
user=mysql
key_buffer=128M
set-variable = max_connections=1000
socket          = /var/lib/mysql/mysql.sock
key_buffer =32M
table_cache = 1024
open_files_limit = 16344
join_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 8M
tmp_table_size=512M
read_rnd_buffer_size=8M
max_heap_table_size=256M
#myisam_sort_buffer_size=256M
thread_cache_size=8
thread_cache=32
query_cache_type=1
query_cache_limit=1024M
query_cache_size=1024M
thread_concurrency = 16
wait_timeout = 10
connect_timeout = 10
interactive_timeout = 10
long_query_time=1
log-slow-queries = /var/log/mysqlslowqueries.log
max_allowed_packet=32M
skip-innodb
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

和MySQLTuner

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.47-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 471M (Tables: 30)
[!!] Total fragmented tables: 2

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 39m 2s (6M q [57.776 qps], 60K conn, TX: 999M, RX: 2B)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 1.3G global + 32.2M per thread (1000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 32.7G (275% of installed RAM)
[OK] Slow queries: 0% (10K/6M)
[OK] Highest usage of available connections: 5% (52/1000)
[OK] Key buffer size / total MyISAM indexes: 32.0M/65.5M
[OK] Key buffer hit rate: 100.0% (938M cached / 108K reads)
[OK] Query cache efficiency: 51.4% (3M cached / 5M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (929 temp sorts / 2M sorts)
[!!] Temporary tables created on disk: 49% (2M on disk / 4M total)
[OK] Thread cache hit rate: 99% (580 created / 60K connections)
[OK] Table cache hit rate: 97% (281 open / 288 opened)
[OK] Open file limit used: 1% (319/16K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses

无法运行mysqld

[root@server ~]#
[root@server ~]# mysqld
100607 18:21:17 [Warning] mysqld: Option '--set-variable' is deprecated. Use --
100607 18:21:17 [Warning] '--log_slow_queries' is deprecated and will be remove
100607 18:21:17 [Note] Plugin 'FEDERATED' is disabled.
100607 18:21:17 [Note] Plugin 'InnoDB' is disabled.
mysqld: File '/var/log/mysqlslowqueries.log' not found (Errcode: 13)
100607 18:21:17 [ERROR] Could not use /var/log/mysqlslowqueries.log for logging                                          urn it on again: fix the cause, shutdown the MySQL server and restart it.
100607 18:21:17 [ERROR] Can't start server: Bind on TCP/IP port: Address alread
100607 18:21:17 [ERROR] Do you already have another mysqld server running on po
100607 18:21:17 [ERROR] Aborting

100607 18:21:17 [Note] mysqld: Shutdown complete

和顶部的输出

root@server ~#顶

top - 18:45:03 up 3 days,  4:40,  1 user,  load average: 1.80, 1.42, 1.40
Tasks: 266 total,   1 running, 265 sleeping,   0 stopped,   0 zombie
Cpu(s):  4.6%us,  2.5%sy,  0.0%ni, 92.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  12462096k total,  2741388k used,  9720708k free,   599584k buffers
Swap:  8385920k total,        0k used,  8385920k free,  1612488k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 7487 mysql     15   0 1138m 241m 3572 S 98.7  2.0 811:12.72 mysqld
 1508 admin     16   0  166m  43m  21m S  0.3  0.4   0:00.80 php-cgi
 2019 apache    15   0  9264 3268 1444 S  0.3  0.0   0:00.06 httpd
 2089 root      15   0  2336 1132  800 R  0.3  0.0   0:00.15 top
32137 admin     15   0  173m  51m  22m S  0.3  0.4   0:02.11 php-cgi
    1 root      15   0  2072  580  504 S  0.0  0.0   0:02.42 init
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.06 migration/0
    3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.03 migration/1
    6 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/1
    7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.18 migration/2
    9 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/2
   10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.02 migration/3
   12 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/3
   13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
   14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/4
   15 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/4
   16 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/4
   17 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/5
   18 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/5
   19 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/5
   20 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/6
   21 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/6
   22 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/6
   23 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/7
   24 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/7
   25 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/7
   26 root      RT  -5     0    0    0 S  0.0  0.0   0:00.05 migration/8
   27 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/8
   28 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/8
   29 root      RT  -5     0    0    0 S  0.0  0.0   0:00.05 migration/9

apache-2.2

mysql

回答 5

Server Fault用户

发布于 2010-06-07 21:25:06

如果您不知道从哪里开始，那么从MySQLTuner开始。

您只需运行下载脚本即可。

它询问mysql管理员的用户名和密码，然后列出一些一般统计信息，然后是存储统计数据，然后是性能指标，然后最后给出推荐列表。

它节省了对服务器实例的初始检查时间。

一旦解决了问题，我建议每隔一个小时左右运行一次脚本(可能不经常/可能更长时间，这取决于数据库的繁忙程度)，以查看是否需要在真正运行后更改设置。

还请看一下像这样的资源。

一旦您知道您的MySQL实例是正常的，您将需要优化应用程序代码中的特定查询，您可以通过签出慢速查询日志(如果有的话)并使用像XDebug这样的分析器来识别最严重的问题。

票数 5

Server Fault用户

发布于 2010-06-08 01:14:40

第一个问题是在32位操作系统上运行。您有12G的Ram (如果我正确地阅读了您的帖子)，而且您将永远不会看到Mysql使用这么多，因为您运行32位。将Centos升级到64位OS，然后开始使用它们提供的my.large.cnf文件。这会让你朝着更好的方向前进。如果您仍然有问题，那么需要评估的事情是这样的--主要是读、写，两者都有。这可以告诉您您应该使用Innodb或MYISAM。Innodb对许多更新/写入和读取都有好处，如果所有内容都是读的，那么MyISAM是一个很好的选择。我们还需要知道这一点，以帮助计算关键缓存等，但第一件事是让操作系统使用所有的RAM。

票数 3

Server Fault用户

发布于 2010-06-07 15:21:36

show status
show variables
show processlist

你在使用php-cgi吗？快的还是快的？

票数 1

页面原文内容由Server Fault提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://serverfault.com/questions/148926

复制

相似问题

问MySQL优化
EN

回答 5

Server Fault用户

Server Fault用户

Server Fault用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL优化EN

回答 5

Server Fault用户

Server Fault用户

Server Fault用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL优化
EN