Temporary space usage / Uso de espaço temporário
How do you find which sessions are consuming temporary space? (original version here: http://informix-technology.blogspot.com/2016/09/temporary-space-usage-uso-de-espaco.html)
English version
Introduction
Informix has a few rocks in it's shoes, or skeletons in the closet as we prefer. In my opinion the ones I consider as such are lack of specific functionality, undesirable behavior or inability to provide information to the DBA. I believe I've opened RFEs (request for enhancements) for most if not all of them, and my favorites are probably:- Inability to easily see who is consuming temporary space (RFE 77879 and RFE 43877)
- Inability to generate a query plan for a running query (RFE 33800)
- Default behavior on non-ANSI databases regarding privileges on new objects (or lack of NODEFDAC $ONCONFIG equivalent - RFE 34407 and RFE 60849)
From these, the first doesn't seem to have any progress and I believe it's a serious limitation for the DBA's daily work. And even if R&D decides to implement it, it would probably not be back ported to currently existing versions, which are to be supported for several years. So it means customers would not benefit from it, even if it was implemented today. And considering IBM closed my first attempt on the basis that we were working on the session limit configurations, I doubt this will be considered a priority. As a side note, the possibility to limit the use of temporary space is a very welcomed feature. But the fact that you can configure something, doesn't mean you won't need to monitor it. I believe this is common sense.
Script solution and usage
Well, considering all this, and after learning a few more things about the engine I noticed it would be possible to create something that can help customers with existing versions regarding the first two issues above (temporary space usage and running query plan). At this moment all I have in a "sharable" state is a script to report on temporary space usage. it's not entirely new as it was possible to find at least one script with overlapping functionality in the IUG repository, but I believe it's the first one that can do all of these:- Show the session owning most (if not all) temporary obkjects which can be:
- Explicit (CREATE TEMP TABLE....) and inplicit (SELECT .. INTO TEMP...) temporary tables
- Hash structures used by hash joins
- Sort structures used by ORDER BY and GROUP BY clauses
- Temporary structures resulting from view materialization, inline views and some OLAP functions
- Show the temporary table structures (columns and respective datatypes and indexes, both explicit and system created)
- Find the objects created by the currently running query and other opened cursors
- Ability to work on secondary read-only servers
galileo@solstice.onlinedomus.net:informix-> ixtempuse -h
ixtempuse: -V | -h | [-s <session> [-t temp_table]] [-u <user>] [-d <database>] [-D <dbspace> [-I max_iterators]
-V shows script version
-h shows this help
-s <session list> : Show information for a session or list of sessions (separated by commas)
-t <temp_table> : Show temporary table structure for a single session/table (ALL for all)
-u <user list> : Show information for a user or list of users (separated by commmas)
-d <database list> : Show information for sessions in a database or list of databases (separated by commas)
-D <dbspace list> : Show information for a dbspace or list of dbspaces (separated by commas - must be temp dbspaces)
-I <max_iterators list> : The script can generate an infinite recursion if something goes wrong.
To prevent this there's a limit of 100 iterators that can be overriden with this parameter
But be advices that in most cases if the limit is reached, it means the script as a flaw.
Apart a script bug, this situation should only happen on very complex queries which are unlikely
to appear frequently. Overriding the limit may cause a FORK BOMB!!!
Ex: ixtempuse -s 43,35,4
galileo@solstice.onlinedomus.net:informix->
Without arguments it shows an overview of tthe temporary space usage:
galileo@solstice.onlinedomus.net:informix-> ixtempuse
+----------+------------------+----------+-------------------+--------------------------+-------------------+----------+----------+
|Sessionid |Username |PID |Conn Time |DBSpace | # (H/G/S/T/V) |DBSpace MB|Session MB|
+----------+------------------+----------+-------------------+--------------------------+-------------------+----------+----------+
|55 informix 22522 2016-09-21 12:36:58|dbtemp1 |11 /0 /0 /0 /0 |22.5 | |
| |dbtemp2 |11 /0 /0 /0 /0 |22.0 | |
| | | | |44.5 |
+------------------------------------------------------------+--------------------------+-------------------+----------+----------+
|52 informix 22348 2016-09-21 12:28:42|dbtemp2 |0 /0 /0 /0 /1 |10.62 | |
| | | | |10.62 |
+------------------------------------------------------------+--------------------------+-------------------+----------+----------+
|99 informix 29367 2016-09-21 16:17:00|dbtemp1 |0 /0 /0 /1 /0 |0.01 | |
| |dbtemp2 |0 /0 /0 /1 /0 |0.01 | |
| |rootdbs |0 /0 /0 /1 /0 |0.01 | |
| | | | |0.04 |
+------------------------------------------------------------+--------------------------+-------------------+----------+----------+
galileo@solstice.onlinedomus.net:informix->
Using the "-s" option we can get the detail for one or more sessions:
galileo@solstice.onlinedomus.net:informix-> ixtempuse -s 55
-----------------------------------------------------------------------------------------------------------------------------------
SID: 55 User: informix Client: solstice.onlinedomus.net PID: 22522
Connected at: 2016-09-21 12:36:58
-----------------------------------------------------------------------------------------------------------------------------------
DBSPACE: dbtemp1
H: th_build_ffffffffffffffff (2097154/0x00200002) 2MB
H: th_build_ffffffffffffffff (2097155/0x00200003) 2.25MB
H: th_build_ffffffffffffffff (2097156/0x00200004) 2MB
H: th_build_ffffffffffffffff (2097157/0x00200005) 2MB
H: th_build_ffffffffffffffff (2097158/0x00200006) 2MB
H: th_build_ffffffffffffffff (2097159/0x00200007) 2MB
H: th_probe_ffffffffffffffff (2097160/0x00200008) 2.25MB
H: th_probe_ffffffffffffffff (2097161/0x00200009) 2MB
H: th_probe_ffffffffffffffff (2097162/0x0020000a) 2MB
H: th_probe_ffffffffffffffff (2097163/0x0020000b) 2MB
H: th_probe_ffffffffffffffff (2097164/0x0020000c) 2MB
Total space for object of type H in this dbspace: 22.5MB
Total space for DBSPACE dbtemp1 is 22.5MB:
DBSPACE: dbtemp2
H: th_build_ffffffffffffffff (3145730/0x00300002) 2MB
H: th_build_ffffffffffffffff (3145731/0x00300003) 2MB
H: th_build_ffffffffffffffff (3145732/0x00300004) 2MB
H: th_build_ffffffffffffffff (3145733/0x00300005) 2MB
H: th_build_ffffffffffffffff (3145734/0x00300006) 2MB
H: th_probe_ffffffffffffffff (3145735/0x00300007) 2MB
H: th_probe_ffffffffffffffff (3145736/0x00300008) 2MB
H: th_probe_ffffffffffffffff (3145737/0x00300009) 2MB
H: th_probe_ffffffffffffffff (3145738/0x0030000a) 2MB
H: th_probe_ffffffffffffffff (3145739/0x0030000b) 2MB
H: th_probe_ffffffffffffffff (3145740/0x0030000c) 2MB
Total space for object of type H in this dbspace: 22MB
Total space for DBSPACE dbtemp2 is 22MB:
Summary for space usage for session 55 is:
Hash : 44.5 MB Groups BY : 0MB
Sort : 0 MB Temp Tables: 0MB
Views : 0 MB Others : 0MB
Total space for session 55 is 44.5MB
galileo@solstice.onlinedomus.net:informix->
and with the "-s" and "-t" option we can get the structure of the temporary tables within one session:
galileo@solstice.onlinedomus.net:informix-> ixtempuse -s 99 -t all
{ TABLE "informix"."fnunes_teste" row size = 85 number of columns = 1 index size = 31 }
CREATE TEMP TABLE "informix"."fnunes_teste"
(
col1_decimal_14_not_null DECIMAL(14) NOT NULL,
col2_decimal_12_5_unique DECIMAL(12, 5),
col3_dt_year_to_second DATETIME YEAR TO SECOND,
col4_dt_year_to_month DATETIME YEAR TO MONTH,
col5_dt_year_to_day DATETIME YEAR TO DAY,
col6_dt_year_to_hour DATETIME YEAR TO HOUR,
col7_dt_year_to_minute DATETIME YEAR TO MINUTE,
col8_dt_year_to_second DATETIME YEAR TO SECOND,
col9_dt_day_to_day DATETIME DAY TO DAY,
col10_dt_houwr_to_hour DATETIME HOUR TO HOUR,
col11_dt_minute_to_minute DATETIME MINUTE TO MINUTE,
col12_dt_second_to_second DATETIME SECOND TO SECOND,
col13_dt_varchar_10 VARCHAR(10),
col14_dt_varchar_10_5 VARCHAR(10)
);
CREATE INDEX ix_2 ON fnunes_teste (col14_dt_varchar_10_5, col1_decimal_14_not_null DESC);
CREATE INDEX ix_1 ON fnunes_teste (col13_dt_varchar_10);
CREATE UNIQUE INDEX 0_2 ON fnunes_teste (col2_decimal_12_5_unique);
{ TABLE "informix"."t_2" row size = 24 number of columns = 2 index size = 0 }
CREATE TEMP TABLE "informix"."t_2"
(
col1 INTEGER,
col3 CHAR(20)
);
{ TABLE "informix"."t_1" row size = 4 number of columns = 1 index size = 0 }
CREATE TEMP TABLE "informix"."t_1"
(
col1 INTEGER
);
galileo@solstice.onlinedomus.net:informix-> Getting the script
The script can be downloaded from http://github.com/domusonline/InformixScripts (browse through scripts/ix)It's licensed under GPL 2.0, so feel free to use it and even change it if you want. I welcome any feedback, requests and bug reports.
Warnings and disclaimer
I have to be very clear on this section. First of all, this is my best effort to provide something useful and that can help customers (DBAs) in their daily routine, and hopefully help to reduce the number of PMRs that are opened around this topic. The script was tested in several combinations of OS (RHEL, HP-UX, Solaris, AIX) and different engine versions (11.50 and 12.10) and several use cases. But anyone using it will have to assume full responsibility for it's results and behavior. Having said this, I'd be very glad if users provide feedback and bug reports. Assuming I can reproduce it, I'd gladly fix it.A few very important points:
- The script is SLOW. There isn't much I can do about it because the way it works (and that's apparently the only way to get the info) means I must go through all the sessions and several onstat commands. I have plans to try to use a single execution of onstat (with -i) but I'm not sure if that's possible. Current workaround it to reduce the scope of the sessions it has to go through. It has options for session id (-s), user (-u) and database (-d). Naturally the slowness is proportional to the number of session and also to the complexity of what they're doing and what cursors they have opened
- The script requires recursion and the only way I've found to make it work in KSH is to run the recursive function as a background process. As with any thing that is recursive there's always the possibility of an infinite loop. I had this during development and I created some measures to prevent it. The main one is that the script will only go up to 100 levels of recursion. This should be more than enough for most avergae day to day queries. A query that requires more than this means that it has more than 100 "iterators", where an iterator is a table scan, a nested loop join, a sort, a merge, an hash join etc. So as you can imagine, 100 should be enough. When the limit is reached the script stops. There's an option to increase the limit, but you should think twice (or more) before using it. An high value can lead to a FORK BOMB. Also, because of the recursion, the script may go over the limit of user processes you have defined. Currently I have no way around this.
Acknowledgements
I must publicly thank several people who somehow contributed to this script. A special thanks to Jacques Renaut from R&D who provided invaluable technical guidance, and allowed me to proceed when I was stuck trying to understand internal structures. Frank Arias, a technical support engineer was crucial in getting it to work on Solaris. Adelino Silva, another technical support engineer was the first beside me to test it and provided valuable input. Jonathan Leffler provided some important feedback. Finally two customers, one in Portugal who provided feedback on HP-UX and another in the US who was fundamental for solving Solaris specific issue.Versão Portuguesa
Introducão
O Informix tem algumas pedras no sapato ou esqueletos no armário conforme preferirmos um termo ou outro.. Na minha opinião o que eu considero como tal pode ser falta de alguma funcionalidade, comportamento indesejado ou impossibilidade do DBA em obter informação necessária à sua atividade. Penso que abri RFEs (request for enhancements) para a maioria se não todos os casos, e os meus favoritos serão provavelmente:- Impossibilidade de facilmente verificar quem está a consumir espaço temporário (RFE 77879 e RFE 43877)
- Impossibilidade de gerar um plano de execução de uma query que esteja a correr (RFE 33800)
- Comportamento por omissão em bases de dados não ANSI, no que diz respeito à atribuição automática de privilégios (ou a falta de um equivalente à variável NODEFDAC mas configurável no $ONCONFIG - RFE 34407 e RFE 60849)
Mas destes, o primeiro não parece ter tido nenhum progresso e penso que é uma limitação séria ao trabalho diário de um DBA. E mesmo que as equipas de I&D resolvessem implementar a funcionalidade hoje, provavelmente não seria portado para as versões anteriores. que serão suportadas por alguns anos ainda. Por outras palavras, mesmo que o problema fosse resolvido hoje, os clientes com versões anteriores continuariam a sofrer com o tema por muitos anos. E considerando que a IBM fechou a minha primeira tentativa de RFE, com base no fato de estarmos a trabalhar na limitação de utilização de recursos por sessão (incluindo o espaço temporário), duvido que este pedido venha a ser considerado prioritário. Como nota acessória, a funcionalidade de limitar os recursos foi muito bem vinda. Mas o facto de podermos configurar algo não elimina a necessidade de monitorizar. Diria que isto é senso comum
Solução via script e sua utilização
Bom, considerando tudo isto e depois de aprender mais algumas coisas sobre o motor, percebi que seria possível criar algo que ajudasse os clientes das versões existentes relativamente aos primeiros dois problemas acima referidos (uso de espaço temporário e plano de execução de uma query já a correr). De momento o que tenho numa forma que possa ser partilhada é um script que reporta a utilização de espaço temporário. O conceito não é totalmente novo e é possível encontrar pelo menos um script no repositório do IIUG que tem funcionalidade algo coincidente. Mas penso que este será o primeiro conseguir fazer tudo isto:- Mostrar a sessão que criou a maioria (se não todos) os tipos de estruturas temporárias, que podem ser:
- Tabelas temporárias explícitas (CREATE TEMP TABLE....) e implícitas (SELECT .. INTO TEMP...)
- Estruturas de Hash usadas pelos HASH joins
- Estruturas de ordenação usadas pelos ORDER BY e GROUP BY
- Estruturas temporárias resultantes da materialização de views, inline views, e algumas funções OLAP
- Mostrar a estrutura das tabelas temporárias (colunas, respetivos tipos de dados e índices, quer os criados pelos utilizadores quer os criados pelo sistema para suporte de constraints)
- Encontrar os objetos criados pela query em execução e por outros cursores abertos
- Possibilidade de trabalhar em servidores secundários em modo de leitura apenas
galileo@solstice.onlinedomus.net:informix-> ixtempuse -h
ixtempuse: -V | -h | [-s <session> [-t temp_table]] [-u <user>] [-d <database>] [-D <dbspace> [-I max_iterators]
-V shows script version
-h shows this help
-s <session list> : Show information for a session or list of sessions (separated by commas)
-t <temp_table> : Show temporary table structure for a single session/table (ALL for all)
-u <user list> : Show information for a user or list of users (separated by commmas)
-d <database list> : Show information for sessions in a database or list of databases (separated by commas)
-D <dbspace list> : Show information for a dbspace or list of dbspaces (separated by commas - must be temp dbspaces)
-I <max_iterators list> : The script can generate an infinite recursion if something goes wrong.
To prevent this there's a limit of 100 iterators that can be overriden with this parameter
But be advices that in most cases if the limit is reached, it means the script as a flaw.
Apart a script bug, this situation should only happen on very complex queries which are unlikely
to appear frequently. Overriding the limit may cause a FORK BOMB!!!
Ex: ixtempuse -s 43,35,4
galileo@solstice.onlinedomus.net:informix->
Sem argumentos mostra o panorama geral da utilização de espaço temporário:
galileo@solstice.onlinedomus.net:informix-> ixtempuse
+----------+------------------+----------+-------------------+--------------------------+-------------------+----------+----------+
|Sessionid |Username |PID |Conn Time |DBSpace | # (H/G/S/T/V) |DBSpace MB|Session MB|
+----------+------------------+----------+-------------------+--------------------------+-------------------+----------+----------+
|55 informix 22522 2016-09-21 12:36:58|dbtemp1 |11 /0 /0 /0 /0 |22.5 | |
| |dbtemp2 |11 /0 /0 /0 /0 |22.0 | |
| | | | |44.5 |
+------------------------------------------------------------+--------------------------+-------------------+----------+----------+
|52 informix 22348 2016-09-21 12:28:42|dbtemp2 |0 /0 /0 /0 /1 |10.62 | |
| | | | |10.62 |
+------------------------------------------------------------+--------------------------+-------------------+----------+----------+
|99 informix 29367 2016-09-21 16:17:00|dbtemp1 |0 /0 /0 /1 /0 |0.01 | |
| |dbtemp2 |0 /0 /0 /1 /0 |0.01 | |
| |rootdbs |0 /0 /0 /1 /0 |0.01 | |
| | | | |0.04 |
+------------------------------------------------------------+--------------------------+-------------------+----------+----------+
galileo@solstice.onlinedomus.net:informix->
Usando a opção "-s" obtemos o detalhe de uma ou mais sessões:
galileo@solstice.onlinedomus.net:informix-> ixtempuse -s 55
-----------------------------------------------------------------------------------------------------------------------------------
SID: 55 User: informix Client: solstice.onlinedomus.net PID: 22522
Connected at: 2016-09-21 12:36:58
-----------------------------------------------------------------------------------------------------------------------------------
DBSPACE: dbtemp1
H: th_build_ffffffffffffffff (2097154/0x00200002) 2MB
H: th_build_ffffffffffffffff (2097155/0x00200003) 2.25MB
H: th_build_ffffffffffffffff (2097156/0x00200004) 2MB
H: th_build_ffffffffffffffff (2097157/0x00200005) 2MB
H: th_build_ffffffffffffffff (2097158/0x00200006) 2MB
H: th_build_ffffffffffffffff (2097159/0x00200007) 2MB
H: th_probe_ffffffffffffffff (2097160/0x00200008) 2.25MB
H: th_probe_ffffffffffffffff (2097161/0x00200009) 2MB
H: th_probe_ffffffffffffffff (2097162/0x0020000a) 2MB
H: th_probe_ffffffffffffffff (2097163/0x0020000b) 2MB
H: th_probe_ffffffffffffffff (2097164/0x0020000c) 2MB
Total space for object of type H in this dbspace: 22.5MB
Total space for DBSPACE dbtemp1 is 22.5MB:
DBSPACE: dbtemp2
H: th_build_ffffffffffffffff (3145730/0x00300002) 2MB
H: th_build_ffffffffffffffff (3145731/0x00300003) 2MB
H: th_build_ffffffffffffffff (3145732/0x00300004) 2MB
H: th_build_ffffffffffffffff (3145733/0x00300005) 2MB
H: th_build_ffffffffffffffff (3145734/0x00300006) 2MB
H: th_probe_ffffffffffffffff (3145735/0x00300007) 2MB
H: th_probe_ffffffffffffffff (3145736/0x00300008) 2MB
H: th_probe_ffffffffffffffff (3145737/0x00300009) 2MB
H: th_probe_ffffffffffffffff (3145738/0x0030000a) 2MB
H: th_probe_ffffffffffffffff (3145739/0x0030000b) 2MB
H: th_probe_ffffffffffffffff (3145740/0x0030000c) 2MB
Total space for object of type H in this dbspace: 22MB
Total space for DBSPACE dbtemp2 is 22MB:
Summary for space usage for session 55 is:
Hash : 44.5 MB Groups BY : 0MB
Sort : 0 MB Temp Tables: 0MB
Views : 0 MB Others : 0MB
Total space for session 55 is 44.5MB
galileo@solstice.onlinedomus.net:informix->
E com as opções "-s" e "-t" podemos obter a estrutura das tabelas temporárias criadas por uma sessão:
galileo@solstice.onlinedomus.net:informix-> ixtempuse -s 99 -t all
{ TABLE "informix"."fnunes_teste" row size = 85 number of columns = 1 index size = 31 }
CREATE TEMP TABLE "informix"."fnunes_teste"
(
col1_decimal_14_not_null DECIMAL(14) NOT NULL,
col2_decimal_12_5_unique DECIMAL(12, 5),
col3_dt_year_to_second DATETIME YEAR TO SECOND,
col4_dt_year_to_month DATETIME YEAR TO MONTH,
col5_dt_year_to_day DATETIME YEAR TO DAY,
col6_dt_year_to_hour DATETIME YEAR TO HOUR,
col7_dt_year_to_minute DATETIME YEAR TO MINUTE,
col8_dt_year_to_second DATETIME YEAR TO SECOND,
col9_dt_day_to_day DATETIME DAY TO DAY,
col10_dt_houwr_to_hour DATETIME HOUR TO HOUR,
col11_dt_minute_to_minute DATETIME MINUTE TO MINUTE,
col12_dt_second_to_second DATETIME SECOND TO SECOND,
col13_dt_varchar_10 VARCHAR(10),
col14_dt_varchar_10_5 VARCHAR(10)
);
CREATE INDEX ix_2 ON fnunes_teste (col14_dt_varchar_10_5, col1_decimal_14_not_null DESC);
CREATE INDEX ix_1 ON fnunes_teste (col13_dt_varchar_10);
CREATE UNIQUE INDEX 0_2 ON fnunes_teste (col2_decimal_12_5_unique);
{ TABLE "informix"."t_2" row size = 24 number of columns = 2 index size = 0 }
CREATE TEMP TABLE "informix"."t_2"
(
col1 INTEGER,
col3 CHAR(20)
);
{ TABLE "informix"."t_1" row size = 4 number of columns = 1 index size = 0 }
CREATE TEMP TABLE "informix"."t_1"
(
col1 INTEGER
);
galileo@solstice.onlinedomus.net:informix-> Obtenção do script
O script pode ser descarregado de http://github.com/domusonline/InformixScripts (navegue por scripts/ix) Está licenciado como GPL 2.0, pelo que é livre de utilizá-lo e até de o modificar caso deseje. Qualquer comentário, sugestão ou reporte de bug será muito bem vindo.Avisos e termo de não responsabilização
Tenho de ser muito claro nesta secção. Antes de mais, isto resulta do meu melhor esforço em fornecer algo que possa ser útil aos clientes (DBAs) na sua rotina, e que eventualmente ajude a reduzir ou resolver os PMRs que vão sendo abertos em torno deste problema. O script foi testado em várias combinações de sistema operativo ((RHEL, HP-UX, Solaris, AIX) e diferentes versões do Informix (11.50 e 12.10) bem como em vários casos de uso. Mas qualquer pessoa que deseje utilizá-lo terá de assumir a responsabilidade pelo seu resultado e comportamneto. Tendo deixado isto claro estarei totalmente disponível para corrigir qualquer problema que consiga reproduzir e que me seja indicado pelos seus utilizadores.Alguns pontos adicionais muito importantes:
- O script é LENTO. Não há muito que possa fazer, porque a forma como funciona (e penso que é a única de obter a informação) requer que varra todas as sessões e execute uma série de comandos onstat para cada uma. Tenho planos de tentar fazer apenas uma execução do onstat (com a opção -i) mas não estou seguro que seja possível. A forma de minimizar a lentidão atualmente será reduzir o número de seeões a examinar. Tem opções para indicar uma lista de sessões (-s), utilizadores (-u) e bases de dados (-d). Naturalmente a lentidão será proporcional ao número de sessões e complexidade do que estejam a executar e dos cursores que tenham aberto
- O script requer recursividade e a única forma que encontrei de o conseguir fazer em KSH é correr a função recursiva como um processo em background. Como em tudo o que envolva recursividade existe a possibilidade de entrarmos num ciclo infinito. Aconteceu-me durante o desenvolvimento e consequentemente crirei alguns mecanismos para o evitar. O principal é que por omissão o script só entrará em 100 níveis de recursão. Isto deverá ser mais que suficiente para a maioria das nossas queries do dia a dia. Uma query que necessite mais que isto terá de ter mais de 100 "iteradores", sendo um iterador um acesso a uma tabela, um nested loop join, um hasj join, um sort, um merge etc. Por isso, como se compreenderá, 100 deverá ser suficiente para a maioria dos casos. Quando o limite é atingido o script aborta Existe uma opção para aumentar o limite, mas deverá pensar várias vezes antes de a usar. Um valor muito alto pode causar um FORK BOMB!!!Adicionalmente, devido à recursividade, o script pode exceder o limite de processos definidos para o seu ambiente. Atualmente não há forma de evitar isto.
Posts
No comments:
Post a Comment