模拟序列 [英] Simulating sequences
问题描述
问候，
我遇到了很大的性能问题，我想问你一下
是什么原因，但首先我需要解释它是如何发生的。
让我们假设我不能使用序列（它接缝不可能，但我的老板
不会像这样的特定数据库功能。
对于序列模拟，我创建了一个名为cnfg_key_generation
的表，每个元组都保存了我的一个表的信息（ tablename，
current_sequencial_number）。让我们检查：
CREATE TABLE cnfg_key_generation（
部门整数NOT NULL，
table_name varchar（20）NOT NULL，
current_key整数NOT NULL，
CONSTRAINT XPKcnfg_key_generation PRIMARY KEY（部门，
table_name）
）;
每个例子，对于一个名为''my_test''的表，我会得到以下值
：
department = 1
table_name =''my_test''
current_key = 1432
每次我想在my_test主键中使用新密钥我只是增加
current_key值。对于这项工作，我创建了一个简单的存储过程
名为key_generation
CREATE FUNCTION key_generation（integer，varchar（20））RETURNS integer AS''
DECLARE
the_department ALIAS FOR $ 1;
the_table_name ALIAS FOR $ 2;
new_key_value整数;
err_num整数;
BEGIN
new_value：= 0;
LOCK TABLE cnfg_key_generation在ROW EXCLUSIVE MODE;
SELECT current_value INTO new_value
FROM cnfg_key_generation
WHERE the_department = department AND the_table_name = table_name;
如果没有找到那么
new_key_value：= 1;
INSERT INTO cnfg_key_generation VALUES（the_department，the_table_name，
new_key_value）;
ELSE
new_key_value：= new_key_value + 1;
UPDATE cnfg_key_generation
SET current_key_value = new_key_value
WHERE department = the_部门和
table_name = the_table_name;
结束IF;
返回new_key_value;
END;
''
LANGUAGE''plpgsql'';
数据插入通过以下方式完成：
INSERT INTO my_test VALUES（key_generation（1，''my_test''），...其他
字段......）;
好的，这可以，但有一个巨大的性能问题。我在pg文档中读到
每次我进行更新，创建一个新元组并将旧标记为
无效。对于包含顺序密钥生成的元组，每次生成新的
键时，都会在cfg_key_generation中创建一个新元组。这意味着在相同表的200万代密钥后，
，性能将完全降低因为将有200万个相同元组的旧版本。对于
实例，我有一个名为''cham_chamada''的表实际上拥有1.5亿美元的元组。 key_>
此表的key_generation执行响应时间超过5秒。在同样的情况下，如果我为一个只有很少值的表执行
key_generation（当前键= 5每个例子），响应时间只有几毫秒（30到50ms）。
我试图用VACUUM解决这个问题，而且完全无效。
执行后问题仍然存在。之后，执行了我知道的每一个
类型的真空（有和没有ANALYZE，特别是那个表，
来自shell的vacuumdb ...）我放弃了并且试了a COPY TO / DROP / CREATE / COPY
FROM。此时，性能问题已得到解决。
如果没有表重建，我该怎么办才能解决这个问题？ PostgreSQL中是否存在无法解决的性能下降？如果有一个巨大的b
表，数百万的数据不断更新，如果真空不是100％，我该怎么做才能保持良好的性能？
PostgreSQL序列是否处理这些性能问题？它是干净，快速和使用序列的汞吗？也许我仍然可以弥补我的老板
介意这个问题如果我在这里得到好的论据来证明使用
序列。
我做了一些愚蠢的事吗？
祝你好运，
------------- -------------------------------------------------- -
$ b $bJoséVilsonde Mello de Farias
软件工程师
$ b $bDígitroTecnologiaLtda - www.digitro.com.br
APC - 面向客户的应用程序
电子邮件： vi***********@digitro.com。 br
电话：+55 48 281 7158
ICQ 11866179
----------- ----------------（广播结束）---------------------------
提示8：解释分析是你的朋友
Greetings,
I''m getting a big performance problem and I would like to ask you what
would be the reason, but first I need to explain how it happens.
Let''s suppose I can''t use sequences (it seams impossible but my boss
doesn''t like specific database features like this one).
For sequence simulation I had created a table called cnfg_key_generation
and each tuple holds information for one of my tables (tablename,
current_sequencial_number). Lets check :
CREATE TABLE cnfg_key_generation (
department integer NOT NULL,
table_name varchar(20) NOT NULL,
current_key integer NOT NULL,
CONSTRAINT XPKcnfg_key_generation PRIMARY KEY (department,
table_name)
);
Per example, for a table called ''my_test'' I would have the following values
:
department = 1
table_name = ''my_test''
current_key = 1432
Everytime I want a new key to use in my_test primary-key I just increment
current_key value. For this job, I''ve created a simple stored procedure
called key_generation
CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer AS''
DECLARE
the_department ALIAS FOR $1;
the_table_name ALIAS FOR $2;
new_key_value integer;
err_num integer;
BEGIN
new_value := 0;
LOCK TABLE cnfg_key_generation IN ROW EXCLUSIVE MODE;
SELECT current_value INTO new_value
FROM cnfg_key_generation
WHERE the_department = department AND the_table_name = table_name;
IF NOT FOUND THEN
new_key_value := 1;
INSERT INTO cnfg_key_generation VALUES (the_department,the_table_name,
new_key_value);
ELSE
new_key_value := new_key_value + 1;
UPDATE cnfg_key_generation
SET current_key_value = new_key_value
WHERE department = the_department AND
table_name = the_table_name;
END IF;
RETURN new_key_value;
END;
''
LANGUAGE ''plpgsql'';
Data insertion is done by the following way :
INSERT INTO my_test VALUES (key_generation(1, ''my_test''), ...other
fields...);
Ok, this works, but has a huge performance problem. I read in pg docs that
everytime I make an UPDATE, a new tuple is created and the old is marked as
invalid. For a tuple that holds sequencial key generation, everytime a new
key is generated, a new tuple is created inside cfg_key_generation. It means
after 2million key generations for same table, performance will be completly
degradated because there will be 2million of old versions of same tuple. For
instance, I have a table called ''cham_chamada'' that actually holds
1.5Million of tuples. The response time for key_generation execution for
this table is more than 5seconds. In this same case if I execute
key_generation for a table that has just few values (current key = 5 per
example), response time is just some miliseconds (30 to 50ms).
I tryied to fix this problem with a VACUUM and it was completly ineffective.
After execution the problem was still there. Later, after execution of every
kind of vacuum I knew (with and without ANALYZE, especific for that table,
vacuumdb from shell ...) I gave up and tryied a COPY TO/DROP/CREATE/COPY
FROM. At this time, the performance problem was fixed.
What can I do to solve this problem without table reconstruction? Is there a
performance degradation in PostgreSQL that can''t be solved? If a have a huge
table with millions of data being update constantly, what can I do to keep a
good performance if vacuum isn''t 100%?
Does PostgreSQL sequences deal with these kinds performance questions? Is it
clean, fast and secury to use sequences? Maybe I still can make up my boss
mind about this subject if I get good arguments here to justify the use of
sequences.
Am I doing some stupid thing?
Best regards,
-----------------------------------------------------------------
José Vilson de Mello de Farias
Software Engineer
Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vi***********@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
推荐答案
1;
the_table_name ALIAS FOR
1;
the_table_name ALIAS FOR
2;
new_k ey_value整数;
err_num整数;
BEGIN
new_value：= 0;
LOCK TABLE cnfg_key_generation在行独占模式中;
SELECT current_value INTO new_value
FROM cnfg_key_generation
WHERE the_department = department AND the_table_name = table_name;
如果没有找到那么
new_key_value：= 1;
INSERT INTO cnfg_key_generation VALUES（the_department，the_table_name，
new_key_value）;
ELSE
new_key_value：= new_key_value + 1;
UPDATE cnfg_key_generation
SET current_key_value = new_key_value
WHERE department = the_department AND
table_name = the_table_name;
END IF;
RETURN new_key_value;
END;
''
LANGUAGE''plpgsql'';
数据插入通过以下方式完成：
INSERT INTO my_test VALU ES（key_generation（1，''my_test''），...其他
字段......）;
好的，这有效，但有一个巨大的性能问题。我在pg文档中读到
每次我进行更新，创建一个新元组并将旧标记为
无效。对于包含顺序密钥生成的元组，每次生成新的
键时，都会在cfg_key_generation中创建一个新元组。这意味着在相同表的200万代密钥后，
，性能将完全降低因为将有200万个相同元组的旧版本。对于
实例，我有一个名为''cham_chamada''的表实际上拥有1.5亿美元的元组。 key_>
此表的key_generation执行响应时间超过5秒。在同样的情况下，如果我为一个只有很少值的表执行
key_generation（当前键= 5每个例子），响应时间只有几毫秒（30到50ms）。
我试图用VACUUM解决这个问题，而且完全无效。
执行后问题仍然存在。之后，执行了我知道的每一个
类型的真空（有和没有ANALYZE，特别是那个表，
来自shell的vacuumdb ...）我放弃了并且试了a COPY TO / DROP / CREATE / COPY
FROM。此时，性能问题已得到解决。
如果没有表重建，我该怎么办才能解决这个问题？ PostgreSQL中是否存在无法解决的性能下降？如果有一个巨大的b
表，数百万的数据不断更新，如果真空不是100％，我该怎么做才能保持良好的性能？
PostgreSQL序列是否处理这些性能问题？它是干净，快速和使用序列的汞吗？也许我仍然可以弥补我的老板
介意这个问题如果我在这里得到好的论据来证明使用
序列。
我做了一些愚蠢的事吗？
祝你好运，
------------- -------------------------------------------------- -
$ b $bJoséVilsonde Mello de Farias
软件工程师
$ b $bDígitroTecnologiaLtda - www.digitro.com.br
APC - 面向客户的应用程序
电子邮件： vi***********@digitro.com。 br
电话：+55 48 281 7158
ICQ 11866179
----------- ----------------（广播结束）---------------------------
提示8：解释分析是你的朋友
2;
new_key_value integer;
err_num integer;
BEGIN
new_value := 0;
LOCK TABLE cnfg_key_generation IN ROW EXCLUSIVE MODE;
SELECT current_value INTO new_value
FROM cnfg_key_generation
WHERE the_department = department AND the_table_name = table_name;
IF NOT FOUND THEN
new_key_value := 1;
INSERT INTO cnfg_key_generation VALUES (the_department,the_table_name,
new_key_value);
ELSE
new_key_value := new_key_value + 1;
UPDATE cnfg_key_generation
SET current_key_value = new_key_value
WHERE department = the_department AND
table_name = the_table_name;
END IF;
RETURN new_key_value;
END;
''
LANGUAGE ''plpgsql'';
Data insertion is done by the following way :
INSERT INTO my_test VALUES (key_generation(1, ''my_test''), ...other
fields...);
Ok, this works, but has a huge performance problem. I read in pg docs that
everytime I make an UPDATE, a new tuple is created and the old is marked as
invalid. For a tuple that holds sequencial key generation, everytime a new
key is generated, a new tuple is created inside cfg_key_generation. It means
after 2million key generations for same table, performance will be completly
degradated because there will be 2million of old versions of same tuple. For
instance, I have a table called ''cham_chamada'' that actually holds
1.5Million of tuples. The response time for key_generation execution for
this table is more than 5seconds. In this same case if I execute
key_generation for a table that has just few values (current key = 5 per
example), response time is just some miliseconds (30 to 50ms).
I tryied to fix this problem with a VACUUM and it was completly ineffective.
After execution the problem was still there. Later, after execution of every
kind of vacuum I knew (with and without ANALYZE, especific for that table,
vacuumdb from shell ...) I gave up and tryied a COPY TO/DROP/CREATE/COPY
FROM. At this time, the performance problem was fixed.
What can I do to solve this problem without table reconstruction? Is there a
performance degradation in PostgreSQL that can''t be solved? If a have a huge
table with millions of data being update constantly, what can I do to keep a
good performance if vacuum isn''t 100%?
Does PostgreSQL sequences deal with these kinds performance questions? Is it
clean, fast and secury to use sequences? Maybe I still can make up my boss
mind about this subject if I get good arguments here to justify the use of
sequences.
Am I doing some stupid thing?
Best regards,
-----------------------------------------------------------------
José Vilson de Mello de Farias
Software Engineer
Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vi***********@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
我会收集5-10个顶级数据库处理唯一ID的方式列和公司的（序列）把它们放在一张小纸上。告诉你的老板，序列是相当标准的，他应该到处走。
Vilson farias写道：
I would collect the way that 5-10 of the top databases handle unique id''s (sequences) for columns and compare them in a small paper. Show your boss that sequences are fairly standard and he should come around.
Vilson farias wrote:
问候，
我'我遇到了很大的性能问题，我想问你原因是什么，但首先我需要解释一下它是怎么发生的。
让'我想我可以'不使用序列（它接缝不可能，但我的老板不喜欢像这样的特定数据库功能）。
对于序列模拟，我创建了一个名为cnfg_key_generation的表
每个元组都保存一个表的信息（tablename，
current_sequencial_number）。让我们检查：
CREATE TABLE cnfg_key_generation（
部门整数NOT NULL，
table_name varchar（20）NOT NULL，
current_key整数NOT NULL，
CONSTRAINT XPKcnfg_key_generation PRIMARY KEY（部门，
table_name）
）;
每个例子，对于一个名为''my_test''的表，我会得到以下值：
：
department = 1
table_name =''my_test''
current_key = 1432
每当我想在my_test主键中使用新密钥时，我只需增加
current_key值。对于这项工作，我创建了一个简单的存储过程
名为key_generation
CREATE FUNCTION key_generation（整数，varchar（20））RETURNS整数AS''
DECLARE
the_department ALIAS FOR
Greetings,
I''m getting a big performance problem and I would like to ask you what
would be the reason, but first I need to explain how it happens.
Let''s suppose I can''t use sequences (it seams impossible but my boss
doesn''t like specific database features like this one).
For sequence simulation I had created a table called cnfg_key_generation
and each tuple holds information for one of my tables (tablename,
current_sequencial_number). Lets check :
CREATE TABLE cnfg_key_generation (
department integer NOT NULL,
table_name varchar(20) NOT NULL,
current_key integer NOT NULL,
CONSTRAINT XPKcnfg_key_generation PRIMARY KEY (department,
table_name)
);
Per example, for a table called ''my_test'' I would have the following values
:
department = 1
table_name = ''my_test''
current_key = 1432
Everytime I want a new key to use in my_test primary-key I just increment
current_key value. For this job, I''ve created a simple stored procedure
called key_generation
CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer AS''
DECLARE
the_department ALIAS FOR
这篇关于模拟序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
