MySQL优化

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

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

我的网站运行速度很慢。请帮我configurationmy.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

 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

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

您只需下载并运行perl脚本。

它要求mysqlpipe理员的用户名和密码，然后列出一些常规统计信息，然后列出存储统计信息，然后列出性能指标，最后给出build议列表。

它可以节省您的服务器实例的初始审查小时数。

一旦你解决了一些问题，我会build议你每隔一个小时左右运行脚本一个星期（也许不太经常/也许更长一些，取决于数据库的繁忙程度），以确定在真正运行之后是否需要改变任何设置。

也看看这样的资源。

一旦你知道你的MySQL实例是健全的，你将需要优化你的应用程序代码中的具体查询，你可以通过检查慢查询日志（如果有什么内容）和使用类似XDebug的分析器来识别最糟糕的违规者。

你遇到的第一个问题是你正在一个32位的操作系统上运行。你有12G的RAM（如果我读你的文章的权利），你永远不会看到MySQL使用那么多，因为你的32位运行。将Centos升级到64位操作系统，然后开始使用他们提供的my.large.cnf文件。这应该让你朝着更好的方向前进。如果你仍然有问题，那么评估的东西大多是读，写。这可以告诉你，你应该使用Innodb或MYISAM。 Innodb将会适合大量的更新/写入和读取，如果所有内容都是读取的，那么MyISAM是一个不错的select。我们也需要知道这一点，以帮助找出密钥caching等，但首先是让操作系统使用所有的RAM。

 show status show variables show processlist

你使用php-cgi吗？ fcgid还是fastcgi？

你通过mysql没有太多的用户，但是在“top”屏幕截图中消耗了98.7％的CPU就是系统大部分时间performance的代表？如果是这样的话，那么有些东西是非常错误的（而高CPU占用率是执行问题的一个关键问题）。即使在大量内存的情况下，mysqld也应该在I / O等待中花费大量的时间。

这个bug描述了使用64位MySQL的高CPU问题 – 虽然是旧版本。

mysqladmin processlist显示什么？

你有没有看看MySQL内部正在运行的MySQL进程？（即通过mytop？）

MySQLd进程的高CPU负载给了我震惊…