tuning_使用MySQL Performance Tuning Analyzer脚本

tuning

When you are working on increasing the speed of your website, a very important piece is making sure you get every last drop of performance out of your database server. Unfortunately, for most of us that aren’t normally database administrators this can be a difficult proposition.

在提高网站速度时，非常重要的一点是确保从数据库服务器获得性能的每一个下降。 不幸的是，对于我们大多数通常不是数据库管理员的人来说，这可能是个难题。

There’s a number of performance tuning scripts that will analyze your server settings and current status and give you information on recommended changes that you should make. You shouldn’t necessarily follow all of the suggestions, but it’s worthwhile to take a look at anyway.

有许多性能调整脚本可以分析服务器设置和当前状态，并为您提供有关应进行的建议更改的信息。 您不一定必须遵循所有建议，但还是值得一看。

The script I’ve been using gives you recommendations for the following:

我一直在使用的脚本为您提供以下建议：

• Slow Query Log
  慢查询日志
• Max Connections
  最大连接数
• Worker Threads
  工作线程
• Key Buffer
  按键缓冲区
• Query Cache
  查询缓存
• Sort Buffer
  排序缓冲区
• Joins
  加入
• Temp Tables
  临时表
• Table (Open & Definition) Cache
  表(打开和定义)缓存
• Table Locking
  表锁定
• Table Scans (read_buffer)
  表扫描(read_buffer)
• Innodb Status
  Innodb状态

• Once you download the script, you’ll need to make it executable with the following command:

  下载脚本后，需要使用以下命令将其设置为可执行文件：

  chmod u+x tuning-primer.sh

  chmod u + x tuning-primer.sh

  If you run this script as a regular user, it will prompt you for your password, so you’ll have to make sure to set access accordingly. If you run it as root it’ll pick up the mysql password from Plesk if you have that installed.

  如果您以普通用户身份运行此脚本，它将提示您输入密码，因此您必须确保相应地设置访问权限。 如果您以root身份运行它，则将在安装后从Plesk中获取mysql密码。

  I’ve cut out a lot of the output, which had a lot more recommendations, but was just too long to fit on the page.

  我删去了很多输出，其中有很多建议，但是太长了，无法容纳在页面上。

  ./tuning-primer.sh

  ./tuning-primer.sh

          — MYSQL PERFORMANCE TUNING PRIMER —             – By: Matthew Montgomery –

  — MYSQL性能调整基础知识— –作者：Matthew Montgomery –

  MySQL Version 4.1.20 i686

  MySQL版本4.1.20 i686

  Uptime = 5 days 10 hrs 46 min 5 secAvg. qps = 4Total Questions = 2020809Threads Connected = 1

  正常运行时间= 5天10小时46分钟5秒平均 qps = 4总问题数= 2020809连接的线程数= 1

  Server has been running for over 48hrs.It should be safe to follow these recommendations

  服务器已经运行了48小时以上，因此请务必遵循以下建议

  ———– snipped ————–

  —————— ——————

  QUERY CACHEQuery cache is enabledCurrent query_cache_size = 8 MCurrent query_cache_used = 7 MCurrent query_cach_limit = 1 MCurrent Query cache fill ratio = 89.38 %However, 254246 queries have been removed from the query cache due to lack of memoryPerhaps you should raise query_cache_sizeMySQL won’t cache query results that are larger than query_cache_limit in size

  QUERY CACHE已启用查询缓存当前query_cache_size = 8 M当前query_cache_used = 7 M当前query_cach_limit = 1 M当前查询缓存填充率= 89.38％但是由于内存不足，254 246个查询已从查询缓存中删除也许您应该提高query_cache_size MySQL不会缓存查询结果大于query_cache_limit的大小

  ———– snipped ————–

  —————— ——————

  Looks like I need to increase my query cache… I set it to only 8MB but it’s cleaning out the cache far too often.

  看来我需要增加查询缓存 …我将其设置为仅8MB，但是它经常清理缓存。

  ———– snipped ————–

  —————— ——————

  TEMP TABLESCurrent max_heap_table_size = 16 MCurrent tmp_table_size = 32 MOf 35170 temp tables, 74% were created on diskEffective in-memory tmp_table_size is limited to max_heap_table_size.Perhaps you should increase your tmp_table_size and/or max_heap_table_sizeto reduce the number of disk-based temporary tablesNote! BLOB and TEXT columns are not allow in memory tables.If you are using these columns raising these values might not impact your ratio of on disk temp tables.

  TEMP TABLES当前max_heap_table_size = 16 MCurrent tmp_table_size = 32 MOf 35170临时表，在磁盘上创建了74％有效的内存中tmp_table_size被限制为max_heap_table_size，也许您应该增加tmp_table_size和/或基于max_heap_table_size来减少磁盘数量！ 内存表中不允许使用BLOB和TEXT列。如果使用这些列，则提高这些值可能不会影响磁盘临时表的比率。

  ———– snipped ————–

  —————— ——————

  This type of information is just invaluable when you are trying to tune the performance of your website.

  当您尝试调整网站的性能时，此类信息非常宝贵。

  Download MySQL Performance Tuning Primer Script

  下载MySQL性能优化入门脚本

  翻译自: https://www.howtogeek.com/howto/linux/using-a-mysql-performance-tuning-analyzer-script/

  tuning