Mysql入门系列:优化MYSQL服务器_MySQL
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 查看一下它们当前的值,然后增量调整其值。这个操作使您能估计为减少严重的性能降低所进行的修改的效果。

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

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

What happens when someone calls in airplane mode? Mobile phones have become one of the indispensable tools in people's lives. It is not only a communication tool, but also a collection of entertainment, learning, work and other functions. With the continuous upgrading and improvement of mobile phone functions, people are becoming more and more dependent on mobile phones. With the advent of airplane mode, people can use their phones more conveniently during flights. However, some people are worried about what impact other people's calls in airplane mode will have on the mobile phone or the user? This article will analyze and discuss from several aspects. first

Solution: 1. Check the eMule settings to make sure you have entered the correct server address and port number; 2. Check the network connection, make sure the computer is connected to the Internet, and reset the router; 3. Check whether the server is online. If your settings are If there is no problem with the network connection, you need to check whether the server is online; 4. Update the eMule version, visit the eMule official website, and download the latest version of the eMule software; 5. Seek help.

When trying to open a disk image in VirtualBox, you may encounter an error indicating that the hard drive cannot be registered. This usually happens when the VM disk image file you are trying to open has the same UUID as another virtual disk image file. In this case, VirtualBox displays error code VBOX_E_OBJECT_NOT_FOUND(0x80bb0001). If you encounter this error, don’t worry, there are some solutions you can try. First, you can try using VirtualBox's command line tools to change the UUID of the disk image file, which will avoid conflicts. You can run the command `VBoxManageinternal

As a LINUX user, we often need to install various software and servers on CentOS. This article will introduce in detail how to install fuse and set up a server on CentOS to help you complete the related operations smoothly. CentOS installation fuseFuse is a user space file system framework that allows unprivileged users to access and operate the file system through a customized file system. Installing fuse on CentOS is very simple, just follow the following steps: 1. Open the terminal and Log in as root user. 2. Use the following command to install the fuse package: ```yuminstallfuse3. Confirm the prompts during the installation process and enter `y` to continue. 4. Installation completed

What should I do if the RPC server is unavailable and cannot be accessed on the desktop? In recent years, computers and the Internet have penetrated into every corner of our lives. As a technology for centralized computing and resource sharing, Remote Procedure Call (RPC) plays a vital role in network communication. However, sometimes we may encounter a situation where the RPC server is unavailable, resulting in the inability to enter the desktop. This article will describe some of the possible causes of this problem and provide solutions. First, we need to understand why the RPC server is unavailable. RPC server is a

In network data transmission, IP proxy servers play an important role, helping users hide their real IP addresses, protect privacy, and improve access speeds. In this article, we will introduce the best practice guide on how to build an IP proxy server with PHP and provide specific code examples. What is an IP proxy server? An IP proxy server is an intermediate server located between the user and the target server. It acts as a transfer station between the user and the target server, forwarding the user's requests and responses. By using an IP proxy server

The role of a DHCP relay is to forward received DHCP packets to another DHCP server on the network, even if the two servers are on different subnets. By using a DHCP relay, you can deploy a centralized DHCP server in the network center and use it to dynamically assign IP addresses to all network subnets/VLANs. Dnsmasq is a commonly used DNS and DHCP protocol server that can be configured as a DHCP relay server to help manage dynamic host configurations in the network. In this article, we will show you how to configure dnsmasq as a DHCP relay server. Content Topics: Network Topology Configuring Static IP Addresses on a DHCP Relay D on a Centralized DHCP Server

What should I do if I can’t enter the game when the epic server is offline? This problem must have been encountered by many friends. When this prompt appears, the genuine game cannot be started. This problem is usually caused by interference from the network and security software. So how should it be solved? The editor of this issue will explain I would like to share the solution with you, I hope today’s software tutorial can help you solve the problem. What to do if the epic server cannot enter the game when it is offline: 1. It may be interfered by security software. Close the game platform and security software and then restart. 2. The second is that the network fluctuates too much. Try restarting the router to see if it works. If the conditions are OK, you can try to use the 5g mobile network to operate. 3. Then there may be more
