MySQL几个和innodb相关的参数-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MySQL几个和innodb相关的参数

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:07 PM

有同事问了几个MySQL参数设置，这里简单记录一下： innodb_thread_concurrency ，用于限制能够进入innodb层的线程数当进入innod

有同事问了几个MySQL参数设置，这里简单记录一下：

innodb_thread_concurrency ，用于限制能够进入innodb层的线程数

当进入innodb层调用read_row/write_row/update_row/delete_row时，会检查已经进入innodb的线程数：innodb_srv_conc_enter_innodb

如果已经满了，就会等待innodb_thread_sleep_delay毫秒尝试一次

如果再次失败，则进入到一个FIFO队列sleep

当在innodb层完成操作后，会调用innodb_srv_conc_exit_innodb退出innodb层

当线程进入时，获得一段时间片innodb_concurrency_tickets，在时间片范围内，该线程就无需检测，直接进入innodb

理论上讲，我们可以把innodb_thread_concurrency设置为（cpu数+磁盘数）*2，但这需要取决于具体的应用场景

innodb_commit_concurrency ，，用于限制在innodb层commit阶段的线程数，大多数情况下，默认值已经足够。

--------------------------

innodb_write_io_threads

用于写脏页的线程数

innodb_read_io_threads

用于从磁盘读文件块的线程数

thread_cache_size

线程在使用完成后，不是退出，而是存放到list中，这样减小了重复创建线程的开销，

这个值的设置应跟内存相关的：mem = (thread_cache_size) * (thread_stack)

max_connections

MySQL层最大允许的连接请求数，注意如果设置的过大，可能会引起很大的额外开销：线程切换开销。目前比较流行的优化方案是使用线程池来保存请求队列，建立有限的work线程来处理连接，换句话说，就算max_connections设置的再大，那也要受innodb_thread_concurrency的限制。

linux

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

Reduce the use of MySQL memory in DockerMar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How to solve the problem of mysql cannot open shared libraryMar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin)Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overviewMar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Running multiple MySQL versions on MacOS: A step-by-step guideMar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

See all articles