search
HomeDatabaseMysql TutorialMysql入门系列:优化MYSQL服务器_MySQL

Mysql入门系列:优化MYSQL服务器_MySQL

Jun 01, 2016 pm 01:55 PM
InfluenceserverbufferOperating environment

  MySQL服务器有几个影响其操作的参数(变量)。如果缺省的参数值不合适,可以将其修改为对服务器运行环境更合适的值。例如,如果您有大量的内存,可以告诉服务为磁盘和索引操作使用较大的缓冲区。这将使内存持有更多的信息并减少了必须进行的磁盘访问的数

  量。如果是一般的系统,可以告诉服务器使用较小的缓冲区,以防止它扰乱系统资源损害其他的进程。

  系统变量的当前值可以通过执行mysqladmin variables 命令来检查。变量可利用- - set - variable var_name = value 选项在命令行设置( -ovar_name = value 是等价的)。如果要想设置几个变量,可使用多个--set-variable 选项,还可以使用下列语法在一个选项文件的[mysqld] 组中设置变量:

  set -variale=var_name=value

  在附录E的mysql程序的条款下给出了服务器变量的全部清单。有关性能优化比较常用的变量已在以下列表中给出。您还可以在MySQL参考手册的“从MySQL中获得最高性能”一章中找到该主题的附加讨论。

  back_log 引入的客户机连接请求的数量,这些请求在从当前客户机中处理时排队。如果您有一个很忙的站点,可以增加该变量的值。

  delayed_queue_size 此变量控制被排队的INSERT DELAYED 语句中的行数。如果该队列已满,则更多的INSERT DELAYED 将堵塞,直到队列有空间为止,这样可防止发布那些语句的客户机继续进行操作。如果您有许多执行这种INSERT 的客户机,且发现它们正在堵塞,那么应增加该变量,使更多的客户机更快地进行工作( INSERT D E L AYED 在4 . 5节“调度与锁定问题”中讨论)

  flush_time 如果系统有问题并且经常锁死或重新引导,应将该变量设置为非零值,这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能,但可减少表讹误或数据丢失的机会。

  在Windows 中,可以在命令行上用--flush 选项启动服务器,以迫使表的修改在每次更新后被刷新。

  key _ buffer_size 用于存放索引块缓冲区的大小。如果增加该变量值,将加快创建和修改索引操作的时间。值越大MySQL就越有可能在内存中查找键值,这将减少索引处理所需的磁盘访问次数。

  在MySQL3.23 以前的版本中,该变量名为key _ buffer。MySQL3.23 及后来的版本同时识别这两个名字。

  max_allowed_packet 客户机通信所使用的缓冲区大小的最大值。如果有客户机要发送大量的BLOB 或TEXT 的值,该服务器变量值可能需要增大。

  客户机目前使用大小为24MB 的缺省缓冲区。如果有使用较小缓冲区的旧客户机。可能需要使该客户机的缓冲区大一些。例如, mysql可以按如下调用来指定一个2 4 MB 信息包的限制值:

  mysql--set-varibale max_allowed_packet=24M

  max_connections 服务器允许的客户机同时连接的最大数量。如果服务器繁忙,可能需要增加该值。例如,如果您的MySQL服务器被Web 服务器使用来处理由DBI 或PHP 脚本产生的查询,并且还有大量的Web 通信,如果该变量设置太低的话,则您站点的访问者会发现请求被拒绝。

  table_cache 表的高速缓存的大小。增加该值可以使mysqld 保持更多的表,同时打开并减少必须进行的文件打开和关闭操作的次数。

  如果增加了max_connections 或table_cache 值的大小,服务器将需要大量的文件描述符。这将引起有关操作系统对文件描述符总进程数量限定的问题,在这种情况下您需要增加该限制值或逐步解决它。由于增加文件描述符数量的限制值,过程会发生变化,所以您可能会在一个运行脚本中使用ulimit 命令时来这样做,该脚本可用于启动服务器,或用于重新配置您的系统。有些系统可以通过编辑系统描述文件来简单地配置和重新引导。对于其他一些系统,则必须编辑一个内核描述文件并重建该内核。如何继续进行下去,请参考您系统的文档。

  解决总进程文件描述符限制的一个方法是:将数据目录分离成多个数据目录并运行多个服务器。这样,通过运行多个服务器使可用的描述符数量成倍增长。但另一方面,其他的复杂因素可能会引起问题。由于命名了两个服务器,您不能从一个单个的服务器上访问不同数

  据目录中的数据库,并且还需要在不同服务器之间复制授权表的权限,以便用户需要访问一个以上的服务器。

  有两个变量是管理员为提高性能时常增加的,它们是record _ buffer 和sort _ buffer。这些缓冲区在连接和分类操作中使用,但其值是属于每个连接的。也就是说,每个客户机都获得属于自己的缓冲区。如果使这些变量的值很大,性能可能会由于昂贵的系统资源的消耗而遭受实际的损失。如果想要修改这些变量,先执行mysqladmin variables 查看一下它们当前的值,然后增量调整其值。这个操作使您能估计为减少严重的性能降低所进行的修改的效果。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

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.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools