improving performance for adding col to table

Posted in 2011

Topics: Performance & Tuning, Data Types & Schema Design, Platform-Specific Issues, Versions, Editions & End-of-Life

KARL OLIVER — Thu, 27 Oct 2011 22:33:15 — source: IIUG Forums & Mailing Lists

hi folks any ideas please IBM Informix Dynamic Server Version 11.50.FC6 HP-UX wuasp132 B.11.23 Does anyone know of any specific parameter I can set to get optimum performance when adding col to a table. ADD template_reference varchar(20,0 Table details Row Size 8065 Number of Rows 1923421 Number of Columns 59 Testing on single cpu system Sar Shows high wio implying high disk io rather than using cache Segment Summary: Onstat g seg info Segment Summary: idkey addr size ovhd class blkused blkfree 753669 52564801 c000000014eb1000 1852026880 22140976 R 452153 2 1081354 52564802 c0000000834ec000 245760000 2881656 V 15218 44782 Total: - - 2097786880 - - 467371 44784

↪ replying to KARL OLIVER

Alexandre — Fri, 28 Oct 2011 12:18:37 — source: IIUG Forums & Mailing Lists

Hello. If you are adding a "not null" column (with a default value), you will get several log consumption. Check your table lock level mode (default is page, change it to "row". In case you can change it to allow null values (the default way), and change your query to: SET LOCK MODE TO COMMITTED READ LAST COMMITTED;ALTER TABLE ..... ; Don´t do it inside a transaction, or your running queries accessing that table will be pending during process. You don´t need a transaction (really) to do it. If you still experience some slow performance, you could analyse the possibility to do it changing your table to "raw", and then after back to "standard" types. Hope it helps. Regards. Em 27/10/2011 18:33, KARL OLIVER escreveu: > hi folks any ideas please > IBM Informix Dynamic Server Version 11.50.FC6 > HP-UX wuasp132 B.11.23 > Does anyone know of any specific parameter I can set to get optimum > performance when adding col to a table. ADD template_reference varchar(20,0 > Table details > Row Size 8065 > Number of Rows 1923421 > Number of Columns 59 > > Testing on single cpu system > Sar > Shows high wio implying high disk io rather than using cache > Segment Summary: > Onstat --g seg info > > Segment Summary: > idkey addr size ovhd class blkused blkfree > 753669 52564801 c000000014eb1000 1852026880 22140976 R 452153 2 > 1081354 52564802 c0000000834ec000 245760000 2881656 V 15218 44782 > Total: - - 2097786880 - - 467371 44784 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 / V11.70 IBM Information Management Informix Technical Professional v3

Glossary

onstat