PostgreSQL performance optimization plug-in-pgfincore

From: http://blog.csdn.net/lengzijian/article/details/8252270

 

I don't want to explain the installation and use of pgfincore too much. I just want to record my usage experience. If you want to read more, please refer to the following content:

Use posix_fadvise pre-Cache frequency data

Http://blog.163.com/digoal@126/blog/static/163877040201062944945126/

A powerful upgrade from pgfincore 1.0

Http://blog.163.com/digoal@126/blog/static/1638770402011630102117658/

Actual use

Http://blog.163.com/digoal@126/blog/static/163877040201221382150858/

 

As De Ge said, when the database is started, the database is cold data, and each query at the beginning is read to the disk, and then written to the database cache. When the database is started, a large number of database accesses may cause the database to be blocked.

At this time, we need a tool that can directly fl the disk content to the memory without occupying the buffer of PG. pgfincore just achieves this, it does not rely on the buffer restrictions in the PG configuration file because it uses OS memory.

 

I have read manyArticleIt is said that this tool has improved the performance of the entire system by dozens of times, including the query speed reduced from 12.96 milliseconds to 0.45 milliseconds, according to dege's blog.

 

You may be excited here, so do I;

 

I encountered a problem in my system, and the query speed of the statement was optimized to around 50 ms (requirement problem). I thought the problem could be solved, but I discovered it later, I have misunderstood many of them.

 

So far, I have never understood this problem. Do I write all the data to the memory? Are there any similar index queries or sequential searches in the memory?

In my system, I added tables and indexes to the memory through the pgfadvise_willneed function, but the execution efficiency was not improved.

Maybe it is because of my usage error. Later I thought about it. Most of my statements may be caused by >=and <=.

 

So I created a table with only one ID and one date, with 1 million data;

When no index is created, the first query time is 460 Ms. When pgfincore is used, the first query time is 182 Ms.

When an index is created, 11 ms is queried for the first time, and 11 ms is used for pgfincore.

Leave it blank. Wait for the time to test the multi-process

 

First, press the database with pgbench.

When pgfincore is used or not applicable, the query speed is around 11 ms.

 

Later I thought about the reason, probably because the information cached by the database itself, so I restarted the database and went on to test it again:

When pgfincore is not used, the query speed is 38 ms;

When pgfincore is used, the query speed is 11 ms;

 

This further confirms my conjecture.

 

It can be noted that pgfincore does not improve the database read performance in all circumstances. Currently, I understand that when the database is restarted or there is no large amount of cache data in the database memory, using pgfincore can improve the performance by several times. Do not use it blindly.

 

Another point is that the memory written to pgfincore is not permanently written. If the read/write frequency is high, the OS memory will also be replaced. (Pgfincore is not suitable for some scenarios)

If you want to permanently write data into the memory, please refer to the dege article:Filecache, a tool that permanently caches files to the memory

 

 

Http://blog.163.com/digoal@126/blog/static/16387704020122198214650/