MySQL Troubleshooting

按有用程度 按页码先后 最新笔记

• 

  第218页

  Xupeng (不能再肥了)

  Maintenance Tools 1. Tools from the MySQL distribution 2. Percona Toolkit 3. MySQL WB Utilities 4. Monitoring tools 我用的有这些： 1. openark kit * Common utilities for MySQL * http://code.openark.org/forge/openark-kit 2. mycheckpoint * Lightweight, SQL oriented monitoring for MySQL * http://code.openark.org/forge/mycheckpoint 3. Percona XtraBackup ... (更多)

  Maintenance Tools 1. Tools from the MySQL distribution 2. Percona Toolkit 3. MySQL WB Utilities 4. Monitoring tools

  我用的有这些：

  1. openark kit

  * Common utilities for MySQL

  * http://code.openark.org/forge/openark-kit 2. mycheckpoint

  * Lightweight, SQL oriented monitoring for MySQL

  * http://code.openark.org/forge/mycheckpoint 3. Percona XtraBackup

  * http://www.percona.com/software/percona-xtrabackup/ 4. Percona Toolkit

  * http://www.percona.com/software/percona-toolkit/ 5. Percona Data Recovery Tool for InnoDB

  * http://www.percona.com/software/mysql-innodb-data-recovery-tools/ 6. Percona Tools

  * http://tools.percona.com/ 7. Percona benchmarks tool

  * http://www.percona.com/docs/wiki/benchworks:start 8. innotop

  * http://code.google.com/p/innotop/ 9. check-mysql-all

  * A suite of Nagios checks for MySQL 4.0+ and common surrounding techs (maatkit, mmm etc)

  * http://code.google.com/p/check-mysql-all/ 10. Percona things

  * http://www.percona.com/software/ 11. Google MySQL tools

  * http://code.google.com/p/google-mysql-tools/ 12. aspersa (part of Percona toolkit now)

  * http://code.google.com/p/aspersa/ 13. ratesniff

  * http://code.google.com/p/ratesniff/ 14. relayfetch

  * http://code.google.com/p/relay-fetch/ (收起)

  2012-03-03 12:48:07   回应

  收藏

  推荐
• 

  第211页

  Xupeng (不能再肥了)

  Benchmarking Tools: 1. mysqlslap mysqlslap --socket=/tmp/mysql51.sock --user=root --delimiter=";" \ --create-schema=mstest --create="CREATE TABLE mstest(id INT NOT NULL \ AUTO_INCREMENT PRIMARY KEY, f1 VARCHAR(255)) ENGINE=InnoDB" --query="INSERT INTO \ mstest(f1) VALUES(MD5(RAND())); SELECT f1 FROM mstest;" --concurrency=10 \ --iterations=1000 2. SysBench sys... (更多)

  Benchmarking Tools: 1. mysqlslap mysqlslap --socket=/tmp/mysql51.sock --user=root --delimiter=";" \ --create-schema=mstest --create="CREATE TABLE mstest(id INT NOT NULL \ AUTO_INCREMENT PRIMARY KEY, f1 VARCHAR(255)) ENGINE=InnoDB" --query="INSERT INTO \ mstest(f1) VALUES(MD5(RAND())); SELECT f1 FROM mstest;" --concurrency=10 \ --iterations=1000 2. SysBench sysbench --test=./sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql60.sock --mysql-user=root prepare 3. Gypsy 4. MySQL Test Framework

  (收起)

  2012-03-03 12:46:23   回应

  收藏

  推荐
• 

  第206页

  Xupeng (不能再肥了)

  General Steps to Take in Troubleshooting 1. Try to identify the actual query that causes the problem. 2. Check to make sure the query’s syntax is correct. 3. Confirm that the problem is in the query. 4. If the query returns wrong data, try to rewrite it to get correct results, i.e., those that you expect to obtain. 5. If a rewrite does not help, check the server options and try to det... (更多)

  General Steps to Take in Troubleshooting

  1. Try to identify the actual query that causes the problem.

  2. Check to make sure the query’s syntax is correct.

  3. Confirm that the problem is in the query.

  4. If the query returns wrong data, try to rewrite it to get correct results, i.e., those that you expect to obtain.

  5. If a rewrite does not help, check the server options and try to determine whether they’re affecting results.

  6. If you decide that the query is correct, go backward until you find a statement or action that corrupted the data on which the query is acting.

  7. If the problem is not repeatable in the MySQL CLI, examine whether it could be a concurrency issue.

  8. If the problem causes a crash or a hang, check the error log first.

  9. If the error logfile doesn’t give you a clue, try to find the last query before the crash.

  10. Use mysqld-debug to generate a core file from a failed server, then analyze it.

  11. Analyze and adjust configuration options.

  12. Use operating system tools to find out which external process could affect mysqld. (收起)

  2012-03-03 12:44:08   回应

  收藏

  推荐

• 

  第38页

  Xupeng (不能再肥了)

  What jumps out is the high value of Handler_read_rnd_next, which shows how often the next row in a datafile was read. A high value typically means that a table scan was used, which is not good for performance. Handler_read_key is a related variable showing the number of index read requests. It shouldn’t be so low in relation to Handler_read_rnd_next, because that means a lot of rows were read ins... (更多)

  What jumps out is the high value of Handler_read_rnd_next, which shows how often the next row in a datafile was read. A high value typically means that a table scan was used, which is not good for performance. Handler_read_key is a related variable showing the number of index read requests. It shouldn’t be so low in relation to Handler_read_rnd_next, because that means a lot of rows were read instead of using an index. In addition, the values of Handler_commit and Handler_read_first have increased slightly. These refer respectively to the number of commits and the number of times the first entry in an index was read. Finally, the unobtrusive 1 in Handler_read_first shows that we asked the server to read a first entry in the index, which can be a symptom of a full index scan.

  查询优化不能只看 `explain` 的结果，有时候也需要观察查询实际的执行情况，一般会先执行 `flush status` 以重置当前会话的状态变量计数，然后执行实际的查询，之后可以使用 `show status like 'Handler_%'` 查看当前会话的索引和数据访问计数。

  另外全局的 Handler_% 变量（`show global status like 'Handler_%'`）对判断整个服务器的索引使用状况也有一定的参考意义，每个 Handler 变量的含义在 MySQL 手册中有详细的描述：http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Handler_commit

  另外也可以对查询做 profiling，了解更为详细的查询执行情况，见：http://dev.mysql.com/doc/refman/5.1/en/show-profiles.html (收起)

  2012-03-02 20:28:19   回应

  收藏

  推荐
• 

  第52页

  Xupeng (不能再肥了)

  As a best practice, I recommend careful study of how MySQL privileges work. This is especially important if you grant privileges on the object level because you should understand how a grant on one level affects grants of others. The same considerations apply to revoking privileges, which can be even more critical because if you think you revoked a privilege and it is still present, this allows un... (更多)

  As a best practice, I recommend careful study of how MySQL privileges work. This is especially important if you grant privileges on the object level because you should understand how a grant on one level affects grants of others. The same considerations apply to revoking privileges, which can be even more critical because if you think you revoked a privilege and it is still present, this allows unwanted access.

  MySQL 的权限控制是个坑，的确需要仔细读文档，另外需要定期检查是否有异常授权，openarkkit (http://code.google.com/p/openarkkit/) 中的 oak-security-audit 是个不错的工具。 (收起)

  2012-03-02 20:52:43   回应

  收藏

  推荐
• 

  第69页

  Xupeng (不能再肥了)

  To summarize, when you work with multistatement transactions, commit them as soon as you can. Don’t leave an uncommitted transaction around after its last update has finished, even if it does not modify any further rows. 偶尔会有同学说，为什么我在删除/更新某条记录时总是会超时： ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 嗯，就是这个原因。 (更多)

  To summarize, when you work with multistatement transactions, commit them as soon as you can. Don’t leave an uncommitted transaction around after its last update has finished, even if it does not modify any further rows.

  偶尔会有同学说，为什么我在删除/更新某条记录时总是会超时：

  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  嗯，就是这个原因。 (收起)

  2012-03-02 21:04:31   回应

  收藏

  推荐

• 

  第218页

  Xupeng (不能再肥了)

  Maintenance Tools 1. Tools from the MySQL distribution 2. Percona Toolkit 3. MySQL WB Utilities 4. Monitoring tools 我用的有这些： 1. openark kit * Common utilities for MySQL * http://code.openark.org/forge/openark-kit 2. mycheckpoint * Lightweight, SQL oriented monitoring for MySQL * http://code.openark.org/forge/mycheckpoint 3. Percona XtraBackup ... (更多)

  Maintenance Tools 1. Tools from the MySQL distribution 2. Percona Toolkit 3. MySQL WB Utilities 4. Monitoring tools

  我用的有这些：

  1. openark kit

  * Common utilities for MySQL

  * http://code.openark.org/forge/openark-kit 2. mycheckpoint

  * Lightweight, SQL oriented monitoring for MySQL

  * http://code.openark.org/forge/mycheckpoint 3. Percona XtraBackup

  * http://www.percona.com/software/percona-xtrabackup/ 4. Percona Toolkit

  * http://www.percona.com/software/percona-toolkit/ 5. Percona Data Recovery Tool for InnoDB

  * http://www.percona.com/software/mysql-innodb-data-recovery-tools/ 6. Percona Tools

  * http://tools.percona.com/ 7. Percona benchmarks tool

  * http://www.percona.com/docs/wiki/benchworks:start 8. innotop

  * http://code.google.com/p/innotop/ 9. check-mysql-all

  * A suite of Nagios checks for MySQL 4.0+ and common surrounding techs (maatkit, mmm etc)

  * http://code.google.com/p/check-mysql-all/ 10. Percona things

  * http://www.percona.com/software/ 11. Google MySQL tools

  * http://code.google.com/p/google-mysql-tools/ 12. aspersa (part of Percona toolkit now)

  * http://code.google.com/p/aspersa/ 13. ratesniff

  * http://code.google.com/p/ratesniff/ 14. relayfetch

  * http://code.google.com/p/relay-fetch/ (收起)

  2012-03-03 12:48:07   回应

  收藏

  推荐
• 

  第211页

  Xupeng (不能再肥了)

  Benchmarking Tools: 1. mysqlslap mysqlslap --socket=/tmp/mysql51.sock --user=root --delimiter=";" \ --create-schema=mstest --create="CREATE TABLE mstest(id INT NOT NULL \ AUTO_INCREMENT PRIMARY KEY, f1 VARCHAR(255)) ENGINE=InnoDB" --query="INSERT INTO \ mstest(f1) VALUES(MD5(RAND())); SELECT f1 FROM mstest;" --concurrency=10 \ --iterations=1000 2. SysBench sys... (更多)

  Benchmarking Tools: 1. mysqlslap mysqlslap --socket=/tmp/mysql51.sock --user=root --delimiter=";" \ --create-schema=mstest --create="CREATE TABLE mstest(id INT NOT NULL \ AUTO_INCREMENT PRIMARY KEY, f1 VARCHAR(255)) ENGINE=InnoDB" --query="INSERT INTO \ mstest(f1) VALUES(MD5(RAND())); SELECT f1 FROM mstest;" --concurrency=10 \ --iterations=1000 2. SysBench sysbench --test=./sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql60.sock --mysql-user=root prepare 3. Gypsy 4. MySQL Test Framework

  (收起)

  2012-03-03 12:46:23   回应

  收藏

  推荐
• 

  第206页

  Xupeng (不能再肥了)

  General Steps to Take in Troubleshooting 1. Try to identify the actual query that causes the problem. 2. Check to make sure the query’s syntax is correct. 3. Confirm that the problem is in the query. 4. If the query returns wrong data, try to rewrite it to get correct results, i.e., those that you expect to obtain. 5. If a rewrite does not help, check the server options and try to det... (更多)

  General Steps to Take in Troubleshooting

  1. Try to identify the actual query that causes the problem.

  2. Check to make sure the query’s syntax is correct.

  3. Confirm that the problem is in the query.

  4. If the query returns wrong data, try to rewrite it to get correct results, i.e., those that you expect to obtain.

  5. If a rewrite does not help, check the server options and try to determine whether they’re affecting results.

  6. If you decide that the query is correct, go backward until you find a statement or action that corrupted the data on which the query is acting.

  7. If the problem is not repeatable in the MySQL CLI, examine whether it could be a concurrency issue.

  8. If the problem causes a crash or a hang, check the error log first.

  9. If the error logfile doesn’t give you a clue, try to find the last query before the crash.

  10. Use mysqld-debug to generate a core file from a failed server, then analyze it.

  11. Analyze and adjust configuration options.

  12. Use operating system tools to find out which external process could affect mysqld. (收起)

  2012-03-03 12:44:08   回应

  收藏

  推荐