[root@slave1 logs]# cat /etc/my.cnf [mysqld]basedir=/usr/local/mysql/ datadir=/usr/local/mysql/data/ user=mysql#打开慢查询日志slow_query_log=on#慢查询日志的位置slow_query_log_file=/usr/local/mysql/logs/mysql-slow#慢查询的基准线超过5s的查询将会被记录long_query_time=5# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=/usr/local/mysql/logs/mysql-binserver-id=1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysql]socket=/var/lib/mysql/mysql.sock[root@slave1 logs]# /etc/init.d/mysqld restart[root@slave1 logs]# mysql -uroot -pmysql> show variables like '%query%';+------------------------------+----------------------------------+| Variable_name | Value |+------------------------------+----------------------------------+| binlog_rows_query_log_events | OFF || ft_query_expansion_limit | 20 || have_query_cache | YES || long_query_time | 5.000000 || query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 1048576 || query_cache_type | OFF || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || slow_query_log | ON || slow_query_log_file | /usr/local/mysql/logs/mysql-slow |+------------------------------+----------------------------------+[root@slave1 ~]# cat /usr/local/mysql/logs/mysql-slow/usr/local/mysql/bin/mysqld, Version: 5.6.15-log (Source distribution). started with:Tcp port: 0 Unix socket: (null)Time Id Command Argumentmysql> select 1;mysql> select sleep(3);mysql> select sleep(4);mysql> select sleep(5);mysql> select sleep(6);[root@slave1 logs]# cat mysql-slow /usr/local/mysql/bin/mysqld, Version: 5.6.15-log (Source distribution). started with:Tcp port: 0 Unix socket: (null)Time Id Command Argument# Time: 140618 11:05:53# User@Host: root[root] @ localhost [] Id: 2# Query_time: 5.000229 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1403060753;select sleep(5);# Time: 140618 11:06:01# User@Host: root[root] @ localhost [] Id: 2# Query_time: 5.000222 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1403060761;select sleep(5);# Time: 140618 11:06:15# User@Host: root[root] @ localhost [] Id: 2# Query_time: 6.000216 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1403060775;select sleep(6);[root@slave1 logs]# mysqldumpslow mysql-slow Reading mysql slow query log from mysql-slowCount: 3 Time=5.33s (16s) Lock=0.00s (0s) Rows=1.0 (3), root[root]@localhost select sleep(N) #下载hackmysql.com 推出的一款日志分析工具 mysqlsla 。 [root@slave1 opt]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz[root@slave1 opt]# ll mysqlsla-2.03.tar.gz -rw-r--r-- 1 root root 33674 Nov 11 2008 mysqlsla-2.03.tar.gz[root@slave1 opt]# tar -xf mysqlsla-2.03.tar.gz -C /usr/src/[root@slave1 opt]# cd /usr/src/mysqlsla-2.03/[root@slave1 mysqlsla-2.03]# yum install perl -y[root@slave1 mysqlsla-2.03]# yum install perl-devel -y[root@slave1 mysqlsla-2.03]# yum install perl-CPAN -y[root@slave1 mysqlsla-2.03]# yum install perl-Time-HiRes -y[root@slave1 mysqlsla-2.03]# perl Makefile.PL Checking if your kit is complete...Looks goodWriting Makefile for mysqlsla[root@slave1 mysqlsla-2.03]# makecp lib/mysqlsla.pm blib/lib/mysqlsla.pmcp bin/mysqlsla blib/script/mysqlsla/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/mysqlslaManifying blib/man3/mysqlsla.3pm[root@slave1 mysqlsla-2.03]# make installInstalling /usr/local/share/perl5/mysqlsla.pmInstalling /usr/local/share/man/man3/mysqlsla.3pmInstalling /usr/local/bin/mysqlslaAppending installation info to /usr/lib64/perl5/perllocal.pod第一次通过CPAN安装perl模块时，需要进行相关的配置，大部分配置采用默认值，一路回车即可#perl -MCPAN -e shell然后在下面的各个询问中可以改已经配置好的选项然后执行下面的命令保存CPAN>reload indexCPAN>reload cpan安装DBI模块CPAN>install DBI[root@slave1 mysqlsla-2.03]# mysqlsla -lt slow /usr/local/mysql/logs/mysql-slow Report for slow logs: /usr/local/mysql/logs/mysql-slow3 queries total, 1 uniqueSorted by 't_sum'Grand Totals: Time 16 s, Lock 0 s, Rows sent 3, Rows Examined 0______________________________________________________________________ 001 ___Count : 3 (100.00%)Time : 16.000667 s total, 5.333556 s avg, 5.000222 s to 6.000216 s max (100.00%)Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)Rows sent : 1 avg, 1 to 1 max (100.00%)Rows examined : 0 avg, 0 to 0 max (0.00%)Database : Users : root@localhost : 100.00% (3) of query, 100.00% (3) of all usersQuery abstract:SET timestamp=N; SELECT sleep(N);Query sample:SET timestamp=1403060753;select sleep(5); 使用 percona-toolkit 的 pt-query-digest （好工具）。root@slave1 opt]# wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.8-1.noarch.rpm[root@slave1 opt]# ls -s percona-toolkit-2.2.8-1.noarch.rpm 1652 percona-toolkit-2.2.8-1.noarch.rpm[root@slave1 opt]# yum install percona-toolkit-2.2.8-1.noarch.rpm -y[root@slave1 opt]# pt-query-digest --user=root /usr/local/mysql/logs/mysql-slow # 280ms user time, 30ms system time, 24.47M rss, 207.34M vsz# Current date: Wed Jun 18 13:49:23 2014# Hostname: slave1.hadoop.com# Files: /usr/local/mysql/logs/mysql-slow# Overall: 3 total, 1 unique, 0.14 QPS, 0.73x concurrency ________________# Time range: 2014-06-18 11:05:53 to 11:06:15# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 16s 5s 6s 5s 6s 369ms 5s# Lock time 0 0 0 0 0 0 0# Rows sent 3 1 1 1 1 0 1# Rows examine 0 0 0 0 0 0 0# Query size 45 15 15 15 15 0 15# Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ================== ============== ===== ====== ===== ======# 1 0xF9A57DD5A41825CA 16.0007 100.0% 3 5.3336 0.03 SELECT# Query 1: 0.14 QPS, 0.73x concurrency, ID 0xF9A57DD5A41825CA at byte 548# This item is included in the report because it matches --limit.# Scores: V/M = 0.03# Time range: 2014-06-18 11:05:53 to 11:06:15# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 100 3# Exec time 100 16s 5s 6s 5s 6s 369ms 5s# Lock time 0 0 0 0 0 0 0 0# Rows sent 100 3 1 1 1 1 0 1# Rows examine 0 0 0 0 0 0 0 0# Query size 100 45 15 15 15 15 0 15# String:# Hosts localhost# Users root# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s ################################################################# 10s+# EXPLAIN /*!50100 PARTITIONS*/select sleep(6)/G
What are stored procedures in MySQL?May 01, 2025 am 12:27 AMStored procedures are precompiled SQL statements in MySQL for improving performance and simplifying complex operations. 1. Improve performance: After the first compilation, subsequent calls do not need to be recompiled. 2. Improve security: Restrict data table access through permission control. 3. Simplify complex operations: combine multiple SQL statements to simplify application layer logic.
How does query caching work in MySQL?May 01, 2025 am 12:26 AMThe working principle of MySQL query cache is to store the results of SELECT query, and when the same query is executed again, the cached results are directly returned. 1) Query cache improves database reading performance and finds cached results through hash values. 2) Simple configuration, set query_cache_type and query_cache_size in MySQL configuration file. 3) Use the SQL_NO_CACHE keyword to disable the cache of specific queries. 4) In high-frequency update environments, query cache may cause performance bottlenecks and needs to be optimized for use through monitoring and adjustment of parameters.
What are the advantages of using MySQL over other relational databases?May 01, 2025 am 12:18 AMThe reasons why MySQL is widely used in various projects include: 1. High performance and scalability, supporting multiple storage engines; 2. Easy to use and maintain, simple configuration and rich tools; 3. Rich ecosystem, attracting a large number of community and third-party tool support; 4. Cross-platform support, suitable for multiple operating systems.
How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AMThe steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.
What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AMMySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.
What is MySQL clustering?Apr 30, 2025 am 12:28 AMMySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe
How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AMOptimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.
How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AMTooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft
MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.
PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
Zend Studio 13.0.1
Powerful PHP integrated development environment
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
