search
HomeDatabaseMysql Tutorialmysql数据库插入速度和读取速度的调整记录

由于项目变态需求;需要在一个比较短时间段急剧增加数据库记录(两三天内,由于0增加至4亿)。在整个过程调优过程非常艰辛

(1)提高数据库插入性能中心思想:尽量将数据一次性写入到Data File和减少数据库的checkpoint 操作。这次修改了下面四个配置项:
1)将 innodb_flush_log_at_trx_commit 配置设定为0;按过往经验设定为0,插入速度会有很大提高。

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.
1:the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file
2:the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it
2)将 innodb_autoextend_increment 配置由于默认8M 调整到 128M

此配置项作用主要是当tablespace 空间已经满了后,需要MySQL系统需要自动扩展多少空间,每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。

3)将 innodb_log_buffer_size 配置由于默认1M 调整到 16M

此配置项作用设定innodb 数据库引擎写日志缓存区;将此缓存段增大可以减少数据库写数据文件次数。

4)将 innodb_log_file_size 配置由于默认 8M 调整到 128M

此配置项作用设定innodb 数据库引擎UNDO日志的大小;从而减少数据库checkpoint操作。

经过以上调整,系统插入速度由于原来10分钟几万条提升至1秒1W左右;注:以上参数调整,需要根据不同机器来进行实际调整。特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整;因为涉及MySQL本身的容灾处理。

(2)提升数据库读取速度,重数据库层面上读取速度提升主要由于几点:简化SQL、加索引和分区; 经过检查程序SQL已经是最简单,查询条件上已经增加索引。我们只能用武器:表分区。

数据库 MySQL分区前准备:在MySQL中,表空间就是存储数据和索引的数据文件。
将S11数据库由于同享tablespace 修改为支持多个tablespace;

将wb_user_info_sina 和 wb_user_info_tx 两个表修改为各自独立表空间;(Sina:1700W数据,2.6G 大数据文件,Tencent 1400W,2.3G大数据文件);
分区操作:
将现有的主键和索引先删除
重现建立id,uid 的联合主键
再以 uid 为键值进行分区。这时候到/var/data/mysql 查看数据文件,可以看到两个大表各自独立表空间已经分割成若干个较少独立分区空间。(这时候若以uid 为检索条件进行查询,并不提升速度;因为键值只是安排数据存储的分区并不会建立分区索引。我非常郁闷这点比Oracle 差得不是一点半点。)
再以 uid 字段上进行建立索引。再次到/var/data/mysql 文件夹查看数据文件,非常郁闷地发现各个分区Size竟然大了。MySQL还是老样子将索引与数据存储在同一个tablespace里面。若能index 与 数据分离能够更加好管理。

经过以上调整,暂时没能体现出系统读取速度提升;基本都是在 2~3秒完成5K数据更新。

MySQL数据库插入速度调整补充资料:

MySQL 从最开始的时候 1000条/分钟的插入速度调高至 10000条/秒。 相信大家都已经等急了相关介绍,下面我做调优时候的整个过程。提高数据库插入性能中心思想:
1、尽量使数据库一次性写入Data File
2、减少数据库的checkpoint 操作
3、程序上尽量缓冲数据,进行批量式插入与提交
4、减少系统的IO冲突

根据以上四点内容,作为一个业余DBA对MySQL服务进行了下面调整:
修改负责收录记录MySQL服务器配置,提升MySQL整体写速度;具体为下面三个数据库变量值:innodb_autoextend_increment、innodb_log_buffer_size、innodb_log_file_size;此三个变量默认值分别为 5M、8M、8M,根据服务器内存大小与具体使用情况,将此三只分别修改为:128M、16M、128M。同时,也将原来2个 Log File 变更为 8 个Log File。此次修改主要满足第一和第二点,如:增加innodb_autoextend_increment就是为了避免由于频繁自动扩展Data File而导致 MySQL 的checkpoint 操作;
将大表转变为独立表空并且进行分区,然后将不同分区下挂在多个不同硬盘阵列中。

完成了以上修改操作后;我看到下面幸福结果:

获取测试结果:
Query OK, 2500000 rows affected (4 min 4.85 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (4 min 58.89 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 25.91 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 22.32 sec)

Records: 2500000 Duplicates: 0 Warnings: 0
最后表的数据量:
+------------+
| count(*) |
+------------+
| 10000000|
+------------+
从上面结果来看,数据量增加会对插入性能有一定影响。不过,整体速度还是非常面议。一天不到时间,就可以完成4亿数据正常处理。预计数据库瓶颈已经被巧妙解决,结果变成程序“猿”苦逼地向我埋怨,大哥不用这么狠啊。

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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment