You have an unresolved naming conflict.
You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting.
Here you declare a variable named measurement_id:
DECLARE
measurement_id INTEGER;
It's a folly to use ambiguous variable names to begin with. If you do it anyway, you must know what you are doing. I make it a habbit to prepend variable names with an underscore unlike column names, like _measurement_id.
The later SELECT statement is ambiguous:
ORDER BY measurement_id
This would raise an error message in modern PostgreSQL with default configuration. Per the documentation:
By default, PL/pgSQL will report an error if a name in a SQL statement
could refer to either a variable or a table column.
And:
To change this behavior on a system-wide basis, set the configuration
parameter plpgsql.variable_conflict to one of error, use_variable, or
use_column (where error is the factory default). This parameter
affects subsequent compilations of statements in PL/pgSQL functions,
but not statements already compiled in the current session. Because
changing this setting can cause unexpected changes in the behavior of
PL/pgSQL functions, it can only be changed by a superuser.
In Postgres older than 9.0 this would be resolved to mean the variable. Per the documentation
In such cases you can specify that PL/pgSQL should resolve ambiguous
references as the variable (which is compatible with PL/pgSQL's
behavior before PostgreSQL 9.0)
Bold emphasis mine.
This would result in arbitrary results, since the sort order is now undetermined.
Audited Function
CREATE OR REPLACE FUNCTION insaft_function()
RETURNS TRIGGER AS
$func$
DECLARE
_measurement_id integer;
_file_header_index_start integer := TG_ARGV[0]::int;
_file_header_index_end integer := TG_ARGV[1]::int;
BEGIN
SELECT a.measurement_id INTO _measurement_id
FROM measurements a
ORDER BY a.measurement_id DESC -- you had ambiguity here!
LIMIT 1;
IF TG_OP = 'INSERT' THEN -- noise if only used in AFTER INSERT trigger
INSERT INTO file_headers (measurement_id, file_header_index_start
, file_header_index_end)
VALUES (_measurement_id, _file_header_index_start, _file_header_index_end);
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;
Note how I named it insaft_function(), since this is only to be used in an AFTER INSERT trigger.
Trigger:
CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function(1, 666);
But for the provided setup, you can radically simplify the function:
CREATE OR REPLACE FUNCTION insaft_function()
RETURNS TRIGGER AS
$func$
BEGIN
INSERT INTO file_headers (measurement_id, file_header_index_start
, file_header_index_end)
VALUES (NEW.measurement_id, TG_ARGV[0]::int, TG_ARGV[1]::int);
RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;
What you see is completely normal and expected.
Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of
values which appears in the column, even if no rows are ever deleted.
A value allocated from the sequence is still "used up" even if a row
containing that value is never successfully inserted into the table
column. This may happen, for example, if the inserting transaction
rolls back.
Unless you really need a gapless series of values, you don't have to worry about it. This is how most (well over 99%, I guess) database tables work.
If you need it, be careful if you have concurrent inserts on the table - you will need some locking to exclude the possibility of choosing the same value by two or more sessions running in parallel.
Best Answer
Keeping things cached with PostgreSQL is a balance between what you can have in
shared_buffersand what you have in the filesystem cache, and avoiding both of them being pushed out by other processes.One of the first things you can do on PostgreSQL 9.4 is set
huge_pages=onin yourpostgresql.conf.This also involves doing some OS changes to allow PostgreSQL to start with this enabled. The appropriate sysctl variable is
vm.nr_hugepagesYou can do a quick and dirty calculation of what you should set
vm.nr_hugepagesto with the following bit of shell scripting.echo $(grep ^VmPeak /proc/$(cat /var/run/postgresql/9.4-main.pid)/status | sed -e 's/VmPeak://' | sed -e 's/kB//' | sed -e 's/[ \t]//g')/$(grep Hugepagesize /proc/meminfo | sed -e 's/Hugepagesize://' | sed -e 's/kB//' | sed -e 's/[ \t]//g') | bc -l | awk '{print int($1+1)}'This line grabs the VmPeak from the running PostgreSQL process in
/proc/$pid/status, which tells you the peak virtual memory size for the PostgreSQL process. This number is in kB. It cleans up the spacing and extra output, then gets the measurement ofHugepagesizefrom/proc/memstat(which in my case was 2048 kB), then does the appropriate division, rounds the output to the nearest integer, then adds one for a safety margin.In my case, it was 2147.
Make it permanent by doing the following:
echo "vm.nr_hugepages=2147" >>/etc/sysctl.d/postgresql.confThis is important for the following reasons, excerpted from the documentation:
So, pages grabbed by the postgres process for shared_buffers won't be swapped out under memory pressure. And you'll have less stress on the TLB for your processor too.
Following that, we adjust the OOMKiller, because something randomly murdering postgres processes is not a good citizen. These settings strongly encourage it to not fire.
echo "vm.overcommit_memory=2" >>/etc/sysctl.d/postgresql.confecho "vm.overcommit_ratio=100" >>/etc/sysctl.d/postgresql.confNext we set a couple of kernel scheduler sysctls:
echo "kernel.sched_autogroup_enabled=0" >>/etc/sysctl.d/postgresql.confecho "kernel.sched_migration_cost=5000000" >>/etc/sysctl.d/postgresql.confThe migration cost is the total time the scheduler will consider a migrated process "cache hot" and thus less likely to be re-migrated.
Autogroup enabled basically groups tasks by setsid() so perceived responsiveness is improved. But on server systems, large daemons like PostgreSQL are going to be launched from the same setsid(), and be effectively choked out of CPU cycles in favor of less important tasks.
After that, we can adjust the swappiness, which is a knob that controls the degree to which a system swaps processes out of physical memory. A higher number more aggressively swaps out, and a lower number keeps things in the filesystem cache longer. Setting this to zero will increase the chance of OOM killing under memory and I/O pressure.
echo "vm.swappiness=10" >>/etc/sysctl.d/postgresql.confThese control how often dirty data is synced back to disk from the filesystem cache. Since dirty objects in the pagecache can't be freed, more aggressively writing back can keep more pages available for use, and help avoid large bursts of disk IO.
echo "vm.dirty_ratio=2" >>/etc/sysctl.d/postgresql.confecho "vm.dirty_background_ratio=1" >>/etc/sysctl.d/postgresql.confSince your PostgreSQL version output shows GCC 4.9, I'm assuming that you're running Debian Jessie or a derivative, so the 3.16 kernel will avoid some truly dreadful I/O issues that the 3.2 kernel had, so that's most certainly good news.
Once you've adjusted all these settings, reboot the system. This will help make sure that the hugepages aren't fragmented when allocated. The best and most reliable way though is to put
hugepages=2147(the number I needed on my server), in/etc/default/grubunderGRUB_CMDLINE_LINUX, and runupdate-grub, then reboot. But I would test the necessity of this, as it is much harder to change than the sysctl.These should give you a reasonable amount of pages in the filesystem cache and in
shared_buffers. After this, making sure the fewest number of processes are running on your server (because they all compete for resources), should give you much of the page cache miss minimization you're looking for.Further reading on huge pages and their effects are in the hugetlbpage documentation.
The vm settings and their effects are in the vm documentation.
This is a very approachable treatment of the Linux Memory system Understanding and Optimizing Memory Utilization
The PostgreSQL documentation goes into detail on the effects of huge pages at the bottom of the kernel resources page
Hope that helps. =)