php editor Youzi will introduce you to an important technique today - how to reduce memory consumption when executing large transactions. Memory consumption can become a serious problem when processing large amounts of data or performing complex operations. To solve this problem, we need to take some optimization measures to improve the efficiency and performance of the code. This article will introduce you in detail some methods and techniques to reduce memory consumption and help you use memory resources more efficiently when processing large transactions.
Question content
I followed the examples on the internet and used the following sqlite parameters to improve the speed of insert queries:
pragma journal_mode = off; pragma synchronous = 0; pragma cache_size = 1000000; pragma locking_mode = exclusive; pragma temp_store = memory;
This is my code:
tx, err := db.begin() if err != nil { log.fatal(err) } pr, err := tx.prepare("insert into table (p1, p2, p3, p4, p5) values (?, ?, ?, ?, ?)") if err != nil { log.fatal(err) } defer pr.close() for i := 0; i < maxi; i++ { for j := 0; j < maxj; j++ { ... _, err = pr.exec(param1, param2, param3, param4, param5) if err != nil { log.fatal(err) } } } err = tx.commit() if err != nil { log.fatal(err) }
Now the query runs fast but consumes too much ram. Because the data is stored in ram, it is only saved to the database file at the end of the execution.
I think it is possible to save the data to a database file periodically, which will slightly increase the execution time but reduce memory consumption. With each change of "i", the transaction starts, and when all "j" are completed, the transaction ends:
for i := 0; i < maxI; i++ { tx, err := db.Begin() if err != nil { log.Fatal(err) } pr, err := tx.Prepare("INSERT INTO Table (p1, p2, p3, p4, p5) VALUES (?, ?, ?, ?, ?)") if err != nil { log.Fatal(err) } defer pr.Close() for j := 0; j < maxJ; j++ { ... _, err = pr.Exec(param1, param2, param3, param4, param5) if err != nil { log.Fatal(err) } } err = tx.Commit() if err != nil { log.Fatal(err) } }
I think now the data should be written to the file in chunks and there should be only one data chunk in the ram.
But during execution, the data is not saved to the file, and the ram continues to be filled. That is, there is no difference in the execution of the first and second code options.
I think when "commit" of the transaction is called, the data should be saved to the file and the ram should be cleared. Please tell me what I'm doing wrong.
Solution
The parameter of PRAGMAcache_size
is the number of pages (generally 4k bytes per page).
PRAGMA cache_size = 1000000;
A maximum of 4GB of RAM will be allocated to the page cache.
The page cache is allocated when needed, up to a maximum size, but is not released until the connection is closed.
Since you are inserting a large number of rows, they will end up on different pages, so you will end up keeping in the cache all the pages that have been written to disk until the cache is filled.
If you want to reduce memory consumption, just reduce the value to something like 1000 (equivalent to 4 MB), or simply remove it. The default cache is 2 MB, which is enough if you are just inserting rows.
Also note that the data does get written to disk when you call COMMIT (or even before committing if there is not enough cache). But Sqlite keeps a copy in cache in case it is needed later to avoid re-reading it from disk.
The above is the detailed content of Reduce memory consumption when executing large transactions. For more information, please follow other related articles on the PHP Chinese website!

This article explains Go's package import mechanisms: named imports (e.g., import "fmt") and blank imports (e.g., import _ "fmt"). Named imports make package contents accessible, while blank imports only execute t

This article explains Beego's NewFlash() function for inter-page data transfer in web applications. It focuses on using NewFlash() to display temporary messages (success, error, warning) between controllers, leveraging the session mechanism. Limita

This article details efficient conversion of MySQL query results into Go struct slices. It emphasizes using database/sql's Scan method for optimal performance, avoiding manual parsing. Best practices for struct field mapping using db tags and robus

This article demonstrates creating mocks and stubs in Go for unit testing. It emphasizes using interfaces, provides examples of mock implementations, and discusses best practices like keeping mocks focused and using assertion libraries. The articl

This article explores Go's custom type constraints for generics. It details how interfaces define minimum type requirements for generic functions, improving type safety and code reusability. The article also discusses limitations and best practices

This article details efficient file writing in Go, comparing os.WriteFile (suitable for small files) with os.OpenFile and buffered writes (optimal for large files). It emphasizes robust error handling, using defer, and checking for specific errors.

The article discusses writing unit tests in Go, covering best practices, mocking techniques, and tools for efficient test management.

This article explores using tracing tools to analyze Go application execution flow. It discusses manual and automatic instrumentation techniques, comparing tools like Jaeger, Zipkin, and OpenTelemetry, and highlighting effective data visualization


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

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

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

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Atom editor mac version download
The most popular open source editor

SublimeText3 Linux new version
SublimeText3 Linux latest version
