postgresql - Postgresql 内存表空间中的插入速度慢

Question

我有一个要求，我需要以每秒 10,000 条记录的速度将记录存储到数据库中(在几个字段上建立索引)。一条记录中的列数是 25。我正在一个事务 block 中批量插入 100,000 条记录。为了提高插入率，我将表空间从磁盘更改为 RAM。这样一来，我每秒只能实现 5,000 次插入。

我还在 postgres 配置中做了以下调整:

• 索引:无
• 同步:假
• 日志记录:禁用

其他信息:

• 表空间:RAM
• 一行中的列数:25(主要是整数)
• CPU:4 核，2.5 GHz
• 内存:48GB

我想知道为什么当数据库没有在磁盘上写入任何内容时(因为我使用的是基于 RAM 的表空间)，单个插入查询平均花费大约 0.2 毫秒。我做错了什么吗？

感谢帮助。

普拉尚

Answer 1

快速数据加载

1. 将您的数据转换为 CSV。
2. 创建一个临时表(如您所述，没有索引)。
3. 执行 COPY 命令:\COPY schema.temp_table FROM/tmp/data.csv WITH CSV
4. 将数据插入非临时表。
5. 创建索引。
6. 设置适当的统计信息。

进一步的建议

对于大量数据:

1. 将数据拆分到子表中。
2. 按照大多数 SELECT 语句将使用的列的顺序插入它。换句话说，尝试使物理模型与逻辑模型保持一致。
3. 调整您的配置设置。
4. 创建一个CLUSTER 索引(最重要的列在左边)。例如:

    CREATE UNIQUE INDEX measurement_001_stc_index      ON climate.measurement_001      USING btree      (station_id, taken, category_id);    ALTER TABLE climate.measurement_001 CLUSTER ON measurement_001_stc_index;

Configuration Settings

On a machine with 4GB of RAM, I did the following...

Kernel Configuration

Tell the Kernel that it's okay for programs to use gobs of shared memory:

sysctl -w kernel.shmmax=536870912
sysctl -p /etc/sysctl.conf

PostgreSQL 配置

1. 编辑/etc/postgresql/8.4/main/postgresql.conf 并设置:

   shared_buffers = 1GBtemp_buffers = 32MBwork_mem = 32MBmaintenance_work_mem = 64MBseq_page_cost = 1.0random_page_cost = 2.0cpu_index_tuple_cost = 0.001effective_cache_size = 512MBcheckpoint_segments = 10

2. Tweak the values as necessary and suitable to your environment. You will probably have to change them for suitable read/write optimization later.
3. Restart PostgreSQL.

Child Tables

For example, let's say you have data based on weather, divided into different categories. Rather than having a single monstrous table, divide it into several tables (one per category).

Master Table

CREATE TABLE climate.measurement
(
  id bigserial NOT NULL,
  taken date NOT NULL,
  station_id integer NOT NULL,
  amount numeric(8,2) NOT NULL,
  flag character varying(1) NOT NULL,
  category_id smallint NOT NULL,
  CONSTRAINT measurement_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

子表

CREATE TABLE climate.measurement_001
(
-- Inherited from table climate.measurement_001:  id bigint NOT NULL DEFAULT nextval('climate.measurement_id_seq'::regclass),
-- Inherited from table climate.measurement_001:  taken date NOT NULL,
-- Inherited from table climate.measurement_001:  station_id integer NOT NULL,
-- Inherited from table climate.measurement_001:  amount numeric(8,2) NOT NULL,
-- Inherited from table climate.measurement_001:  flag character varying(1) NOT NULL,
-- Inherited from table climate.measurement_001:  category_id smallint NOT NULL,
  CONSTRAINT measurement_001_pkey PRIMARY KEY (id),
  CONSTRAINT measurement_001_category_id_ck CHECK (category_id = 1)
)
INHERITS (climate.measurement)
WITH (
  OIDS=FALSE
);

表统计

增加重要列的表格统计数据:

ALTER TABLE climate.measurement_001 ALTER COLUMN taken SET STATISTICS 1000;
ALTER TABLE climate.measurement_001 ALTER COLUMN station_id SET STATISTICS 1000;

不要忘记 VACUUM 和 ANALYSE 之后。