windows mysql 配置socket mysql server配置

转载

蓝色忧郁花 2023-10-17 20:20:19

文章标签 操作系统运维数据库 mysql 缓存 文章分类 MySQL 数据库

查看Mysql server 当前参数

1. 查看服务器参数默认值：mysqld --verbose --help

2. 查看服务器参数实际值：mysql> SHOW VARIABLES;

3. 查看服务器运行状态值：mysql>SHOW STATUS；

影响Mysql 性能的重要参数

When dealing with the above mentioned per-session system variables it should always be considered that those have to be multiplied by max_connections to estimate the maximal memory consumption. Failing to do so can easily lead to server crashes at times of load peaks when more than usual clients connect to the server! A quick and dirty estimation can be made with the following formular:

min_memory_needed = global_buffers + (thread_buffers * max_connections)

global_buffers:
       key_buffer
       innodb_buffer_pool
       innodb_log_buffer
       innodb_additional_mem_pool
       net_buffer

thread_buffers:
       sort_buffer
       myisam_sort_buffer
       read_buffer
       join_buffer
       read_rnd_buffer

Note: Especially when dealing with server settings, all information should be verified in the respective chapters of the official documentation as these are subject of change and the authors of this text lack confirmed knowledge about how the server works internally.

max_connections：

允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。

innodb_buffer_pool_size ：

缓存InnoDB 数据和索引的内存缓冲区的大小。你把这个值设得越高，访问表中数据需要得磁盘I/O 越少。在一个专用的数据库服务器上，你可以设置这个参数达机器物理内存大小的80%。尽管如此，还是不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。

innodb_log_buffer_size:

默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存-- 它每秒都会刷新一次，因此无需设置超过1 秒所需的内存空间。通常8-16MB 就足够了。越小的系统它的值越小。

innodb_additional_mem_pool_size：

InnoDB 用来存储数据目录信息和其它内部数据结构的内存池的大小。默认值是1MB。应用程序里的表越多，你需要在这里分配越多的内存。如果InnoDB 用光了这个池内的内存，InnoDB 开始从操作系统分配内存，并且往MySQL 错误日志写警告信息。没有必要给这个缓冲池分配非常大的空间，在应用相对稳定的情况下，这个缓冲池的大小也相对稳定。

record_buffer_size：

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)。

sort_buffer_size：

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)。

read_rnd_buffer_size：

如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。

1. 索引参数

key_buffer_size：MyISAM表的索引块分配了缓冲区，由所有线程共享。key_buffer_size是索引块缓冲区的大小。键值缓冲区即为键值缓存。此参数只适用于myisam 存储引擎。key_buffer_size的最大允许设定值为4GB。有效最大值可以更小，取决于可用物理RAM和操作系统或硬件平台强加的每个进程的RAM限制。

增加该值，达到你可以提供的更好的索引处理(所有读和多个写操作)。通常为主要运行MySQL的机器内存的25%。但是，如果你将该值设得过大(例如，大于总内存的50%)，系统将转换为页并变得极慢。MySQL依赖操作系统来执行数据读取时的文件系统缓存，因此你必须为文件系统缓存留一些空间。同时写多行时要想速度更快，应使用LOCK TABLES。

你可以通过执行SHOW STATUS语句并检查Key_read_requests、Key_reads、Key_write_requests和Key_writes状态变量来检查键值缓冲区的性能。Key_reads/Key_read_requests比例一般应小于0.01。如果你使用更新和删除，Key_writes/ Key_write_requests 比例通常接近1，但如果你更新时会同时影响到多行或如果你正使用DELAY_KEY_WRITE表选项，可能小得多。

用key_buffer_size结合Key_blocks_unused状态变量和缓冲区块大小，可以确定使用的键值缓冲区的比例。从key_cache_block_size服务器变量可以获得缓冲区块大小。使用的缓冲区的比例为：1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size) 该值为约数，因为键值缓冲区的部分空间被分配用作内部管理结构。

可以创建多个MyISAM键值缓存。4GB限制可以适合每个缓存，而不是一个组。

key_cache_age_threshold：该值控制将缓冲区从键值缓存热子链(sub-chain)降级到温子链(sub-chain)。如果值更低，则降级更快。最小值为100。默认值是300。

key_cache_block_size：键值缓存内块的字节大小。默认值是1024。

key_cache_division_limit：键值缓存缓冲区链热子链和温子链的划分点。该值为缓冲区链用于温子链的百分比。允许的值的范围为1到100。默认值是100。

索引缓存使用方法：

mysql5.1 以前只允许使用一个系统默认的key_buffer。mysql5.1 以后提供了多个key_buffer，可以将指定的表索引缓存入指定的key_buffer，这样可以更小的降低线程之间的竞争。

例如，下面的语句将表t1、t2 和t3 的索引分配给名为hot_cache 的键高速缓冲：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

可以用SET GLOBAL 参数设置语句或使用服务器启动选项设置在CACHE INDEX 语句中引用的键高速缓冲的大小来创建键高速缓冲。例如：mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

要想删除键高速缓冲，将其大小设置为零： mysql> SET GLOBAL keycache1.key_buffer_size=0;

请注意不能删除默认键高速缓冲。删除默认键高速缓冲的尝试将被忽略CACHE INDEX 在一个表和键高速缓冲之间建立一种联系，但每次服务器重启时该联系被丢失。如果你想要每次服务器重启时该联系生效，一个发办法是使用选项文件：包括配置键高速缓冲的变量设定值，和一个init-file 选项用来命名包含待执行的CACHE INDEX 语句的一个文件。

例如：

key_buffer_size = 4G 
hot_cache.key_buffer_size = 2G 
cold_cache.key_buffer_size = 2G 
init_file=/path/to/data-directory/mysqld_init.sql

每次服务器启动时执行mysqld_init.sql 中的语句。该文件每行应包含一个SQL 语句。

下面的例子分配几个表，分别对应hot_cache 和cold_cache：

CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache

CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache

要想将索引预装到缓存中，使用LOAD INDEX INTO CACHE 语句。

例如，下面的语句可以预装表t1 和t2 索引的非叶节点(索引块)：

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

键高速缓冲可以通过更新其参数值随时重新构建。例如：

mysql> SET GLOBAL cold_cache。key_buffer_size=4*1024*1024；

如果你很少使用MyISAM 表，那么也保留低于16-32MB 的key_buffer_size给予磁盘的临时表索引所需。

2. Query Cache

The Query Cache can be configured with the query_cache_% variables. Most important here are the global query_cache_size and query_cache_limit which prevents single queries with unusual big results larger than this size to use up the whole cache.

Note that the Query Cache blocks have a variable size whose minimum size is query_cache_min_res_unit, so after a complete cache flush the number of free blocks is ideally just one. A large value of Qcache_free_blocks just indicates a high fragmentation.

Worth monitoring are the following variables:

Qcache_free_blocks: If this value is high it indicates a high fragmentation which does not need to be a bad thing though.
If this value is high there are either much uncachable queries (e.g. because they use functions like now()) or the value for query_cache_limit is too low.
This is the amount of old results that have been purged because the cache was full and not because their underlying tables have been modified. query_cache_size must be increased to lower this variable.

3. 表锁

The Table_locks_% variables show the number of queries that had to wait because the tables they tried to access where currently locked by other queries. These situations can be caused by "LOCK TABLE" statements and also by e.g. simultaneous write accesses to the same table.

innodb_table_locks：

InnoDB 重视LOCK TABLES，直到所有其它线程已经释放他们所有对表的锁定，MySQL 才从LOCK TABLE .. WRITE 返回。默认值是1，这意为LOCK TABLES 让InnoDB内部锁定一个表。在使用AUTOCOMMIT=1 的应用里，InnoDB 的内部表锁定会导致死锁。可以通过设置innodb_table_locks=0 来消除这个问题。

innodb_lock_wait_timeout：

Mysql 可以自动的监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用来在出现类似情况的时候对锁定进行的后续处理。默认值是50秒，根据应用的需要进行调整。

4. 表缓存

table_cache的设置：

说明：数据库中打开表的缓存数量。table_cache 与max_connections 有关。例如，对于200 个并行运行的连接，应该让表的缓存至少有200 * N，这里N 是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。

设置技巧：

可以通过检查mysqld 的状态变量Opened_tables 确定表缓存是否太小：

mysql> SHOW STATUS LIKE 'Opened_tables';

如果值很大，即使你没有发出许多FLUSH TABLES 语句，也应增加表缓存的大小。

MySQL needs a certain time just to "open" a table and read its meta data like column names etc. If many threads are trying to access the same table, it is opened multiple times. To speed this up the meta data can be cached in the table_cache. A good value for this setting is the number of max_connections multiplied with the number of usually used tables per SELECT.

Using mysqlreport or by looking at the currently Open_tables and ever since Opened_tables as well as the Uptime the number of necessary table opens per second can be calculated (consider the off-peak times like nights though).

5. 链接和线程

thread_cache_size：

可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。

For every client connection (aka session) MySQL creates a separated thread under the main mysqld process. For big sites with several hundred new connections per second, creating the threads itself can consume a significant amount of time. To speed things up, idle threads can be cached after their client disconnected. As a rule of thumb not more than one thread per second should be newly created. Clients that send several queries to the server should use persistent connections like with PHPs mysql_pconnect() function.

This cache can be configured by thread_cache_size and monitored with the threads_% variables.

To avoid overloads MySQL blocks new connections if more than max_connections are currently in use. Start with max_used_connections and monitor the number of connection that were rejected in Aborted_clients and the ones that timed out in Aborted_connections. Forgotten disconnects from clients that use persistent connections can easily lead to a denial of service situation so be aware! Normally connections are closed after wait_timeout seconds of being idle.

Temporary tables: It is perfectly normal that MySQL creates temporary tables while sorting or grouping results. Those tables are either be held in memory or if too large be written to disk which is naturally much slower. The number of disk tables among the Created_tmp_% variables should be neglectible or else the settings in max_heap_table_size and tmp_table_sizebe reconsidered.

Delayed writes: In situations like writing webserver access log files to a database, with many subsequent INSERT queries for rather unimportant data into the same table, the performance can be improved by advising the server to cache the write requests a little while and then send a whole batch of data to disk.

Be aware though that all mentioned methods contradicts ACID compliance because INSERT queries are acknowledged with OK to the client before the data has actually be written to disk and thus can still get lost in case of an power outage or server crash. Additionally the side effects mentioned in the documentation often reads like a patient information leaflet of a modern medicament...

MyISAM tables can be given the DELAY_KEY_WRITE option using CREATE or ALTER TABLE. The drawback is that after a crash the table is automatically marked as corrupt and has to be checked/repaired which can take some time.

InnoDB can be told with innodb_flush_log_at_trx_commit to delay writing the data a bit. In case of a server crash the data itself is supposed to be still consistent, just the indices have to be rebuilt.

INSERT DELAYED works on main Storage Engines on a per query base.

其他参数：

innodb_flush_log_at_trx_commit 的设置：

0：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

1：在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。

2：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。对日志文件每秒刷新一次。

默认值是1，也是最安全的设置，即每个事务提交的时候都会从log buffer 写到日志文件，而且会实际刷新磁盘，但是这样性能有一定的损失。如果可以容忍在数据库崩溃的时候损失一部分数据，那么设置成0 或者2 都会有所改善。设置成0，则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失1 秒钟的事务，这种方式是最不安全的，也是效率最高的。设置成2 的时候，因为只是没有刷新到磁盘，但是已经写入日志文件，所以只要操作系统没有崩溃，那么并没有丢失数据，比设置成0 更安全一些。

在mysql 的手册中，为了确保事务的持久性和复制设置的耐受性、一致性，都是建议将这个参数设置为1 的。

innodb_doublewrite：

默认地，InnoDB 存储所有数据两次，第一次存储到doublewrite 缓冲，然后存储到确实的数据文件。如果对性能的要求高于对数据完整性的要求，那么可以通过--skip-innodb-doublewrite 关闭这个设置。

innodb_log_file_size：

在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。

back_log：

要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。

back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。

interactive_timeout：

服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。