Vilson farias wrote:
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: [EMAIL PROTECTED] Tel.: +55 48 281 7158 ICQ 11866179
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
