Move tables to another dbspace

Greetings again I have some performance issues in a productive 24x7x365 environment; I realize that almost all tables are in only one dbspace, so, I plan to do a reorganization in others dbspaces. My idea is use ALTER FRAGMENT ONLINE ON TABLE <tablename> INIT IN <new_dbspace>; ¿ this do not affect to my users in the productive database ? ¿ Is the best strategy to do this reorg ? My version in Informix 11.50 FC9 Thanks a lot

Any indication your performance problem has anything to do with location=20 of your tables? You'd have to be i/o bound for this, that is your performance problem=20 would have to be due to disk i/o waiting. And even if this is the case, you'd first determine whether being i/o=20 bound isn't the real problem (which wouldn't go away from changing table=20 location.) In other words: would your system run from buffer cache (which an OLTP=20 should), then it would make any difference which dbspace a particular=20 table resides in (apart maybe from dbspace page size effects.) Maybe only your bufferpool configuration needs tweaking? HTH, Andreas From: "HUGO PADILLA" <hugo=5Fpadilla@terra.com.mx> To: ids@iiug.org Date: 20.01.2017 20:30 Subject: Move tables to another dbspace [38528] Sent by: ids-bounces@iiug.org Greetings again=20 I have some performance issues in a productive 24x7x365 environment; I=20 realize=20 that almost all tables are in only one dbspace, so, I plan to do a=20 reorganization in others dbspaces.=20 My idea is use=20 ALTER FRAGMENT ONLINE ON TABLE <tablename> INIT IN <new=5Fdbspace>;=20 =BF this do not affect to my users in the productive database ?=20 =BF Is the best strategy to do this reorg ?=20 My version in Informix 11.50 FC9=20 Thanks a lot=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Thank's We had performance issues in the same hour two times a day almost everyday, when is the most users sessions. The cpu goes to top, and we identify with onstat -u | egrep B--P a lot of sessions waiting for a resource, all in thesame address, like this 402f086d8 B--PX-- 256777 masiste - 1440e1440 0 0 455512 13229 I tried to locate a latch o a lock in 1440e1440, with onstat -m, onstat -s, even onstat -a but no have any kind of data on this memory address The database is about 500Gb, with hundred of users, and the database "masiste" works in only one dbspace for data. We do not see another symptom to blame for the performance issues, so, the reorganization of tables was the first target to attack.

B(uffer) waits and high cpu usage -> typical for some hot buffer(s) which=20 everybody is competing for. Monitor this over a series of 'onstat -u' outputs and find out the various = (or single) buffer addresses (here: 1440e1440) of buffers frequently being = waited on. Then grep 'onstat -B' for these addresses to find out page address=20 (chunk:offset) which in turn you'd use to determine partition/table and=20 logical page utilizing 'oncheck -pe <dbsname>', with the dbspace chunk=20 belongs to. More often than not it's an index root node (index entry point) that so=20 many queries need to access. Maybe it's being held by some operations for longer than expected. You'd typically also see such buffer in 'onstat -b' ('in use' buffers) ->=20 see whether certain owner(s) occur more often than others ... continue=20 research from there (onstat -u -> session id -> onstat -g ses <sid>). ... re-locating an index or table wouldn't help this. HTH, Andreas From: "HUGO PADILLA" <hugo=5Fpadilla@terra.com.mx> To: ids@iiug.org Date: 23.01.2017 17:38 Subject: Re: Move tables to another dbspace [38538] Sent by: ids-bounces@iiug.org Thank's=20 We had performance issues in the same hour two times a day almost=20 everyday,=20 when is the most users sessions. The cpu goes to top, and we identify with = onstat -u | egrep B--P a lot of sessions waiting for a resource, all in=20 the=20same address, like this=20 402f086d8 B--PX-- 256777 masiste - 1440e1440 0 0 455512 13229=20 I tried to locate a latch o a lock in 1440e1440, with onstat -m, onstat=20 -s,=20 even onstat -a but no have any kind of data on this memory address=20 The database is about 500Gb, with hundred of users, and the database=20 "masiste"=20 works in only one dbspace for data.=20 We do not see another symptom to blame for the performance issues, so, the = reorganization of tables was the first target to attack.=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20