When you reach the 16M "used pages" you may still work because not all the pages are full and the engine may found some slots to INSERT new rows.
But the best practice would be to trigger the alarm long before you reach the 16M used pages...
If you want to be conservative you can set it to 10M for example. The purpose is go give you plenty of time to prepare the solution when you start risking having a problem, not to warn you when you're about to receive an error as it will not grant you enough time to really avoid it.
As such, yes, the task has been working for a few customers, in the sense that they never faced the error again. One has just took the opportunity to rebuild one table during a planned downtime for upgrade. If you avoid the error it means it works for you. If you're concern that it might warn you too soon, or in situations where eventually you would never hit the problem, then yes, depending on the threshold you use, it can happen. But that infinitely better than hitting the error :) And the threshold may be set closer or further from the real limit, depending on your table's growth rate, the impact that facing the error would have etc...
In summary, the idea of the task was never that it would warn you when you've exhausted the free slots. The engine already tells you that by blowing each INSERT with an error. The idea is to warn you and give you time to plan the rebuild of the table.
Regarding the shrink... it can potentially free pages. But that's not the main concern here. And what problems did you face with it? The one I recall is that I don't like it "online". And for "offline" you can just rebuild the table...
Regards.
------------------------------
FERNANDO NUNES
------------------------------
Original Message:
Sent: Fri November 08, 2019 05:17 AM
From: Stefan Wilfling
Subject: Reviving of an old RFE that should have more votes: 16 millions data pages limitation
Hi!
We also have sometimes troubles with "no more pages" (and it´s hard to monitor).
And i´m still at a long discussion with support: How can we really monitor this issue.
In monit_check_numpages.sql, you use sysmaster:sysptnhdr.npused as threshold.
Does this really work for you?
My last info was, that this column isn´t correct, when you delete rows (and troubles with binary columns).
Example:
- Table with 47 Mio rows -> No more pages
-> npused=16777215 (=nptotal)
- Then i deleted 7Mio rows
-> npused is still at 16777215.
Have you found a solution for that?
(Repack/Shrink isn´t really a solution -> Performance-Troubles).
------------------------------
Stefan Wilfling
Original Message:
Sent: Thu November 07, 2019 06:22 PM
From: FERNANDO NUNES
Subject: Reviving of an old RFE that should have more votes: 16 millions data pages limitation
Regarding the timely warning, in the following link you can find a "framework" for scheduler tasks.
One of them is just for that (monit_check_numpages.sql):
https://github.com/domusonline/InformixScripts/tree/master/monit
Regards.
------------------------------
FERNANDO NUNES
Original Message:
Sent: Thu November 07, 2019 06:02 PM
From: Jacob Salomon
Subject: Reviving of an old RFE that should have more votes: 16 millions data pages limitation
Hi Art.
I am gratified that there is an alternative to a genuine rowid column in partitioned tables. It is a good thing to know and it is a 64 bit number, better than the layout I was thinking of. However...
We have drifted away from the original source of this discussion - Eric's plaint of chafing at the 16-million page limit for a partition. I see that Ben has suggested a timely warning. I implemented just such a nightly job at Barnes & Noble as well as at Onyx Centersource (nee Onyx Business Intelligence) in Dallas. I think I had set an arbitrary threshold of about 15 million pages and any partition exceeding that threshold would be included an an email to the admins. Another threshold (I forget how high) would generate a pager alert, as would a log message about no more pages.
Of course, my repeated requests to to the operations folks - to give us a heads-up when planning a massive load - went unheeded. But I could tell them the exact time their load barfed. But that's another war story altogether. (Harrumph! Not the kind you tell your grandchildren! :-)
Back to the diversion about ifx_row_id: I see from your blobspot entry that the numbers on either side of the : are in decimal. There is an advantage to using hex for the rowid component, like when you need to locate actual corruption and want to locate the logical page number. But that is yet another story. :-)
'Bye now.
------------------------------
Jacob Salomon
Original Message:
Sent: Thu November 07, 2019 10:08 AM
From: Art Kagel
Subject: Reviving of an old RFE that should have more votes: 16 millions data pages limitation
Welcome back into the fold Jacob!
Please note that I have discovered that there is actually an undocumented rowid even on partitioned tables called ifx_row_id (and on non-partitioned tables as well). See my BLOG entry from yesterday for details:
It is not drop in compatible with the normal ROWID on a nonpartitioned table or the WITH ROWID column, but it is available and it does work and it is fast!
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Original Message------
Hi Eric.
I've just rejoined the community and found something familiar about your question.
I had to persuade a client to allow me to move some of their more monstrous tables to DBspaces with 16K pages. And then I set up a fragmentation scheme, so the 16-million page limit became quite the non-issue. It seems to me that once you have a fragmentation scheme you can keep adding fragments to the table as your space fills up. If I were one of the developers I would likely not put high priority on this, mainly owing to the effort but also because the relatively easy workaround. Until you run afoul on a limit to the number of partitions to a table. I think that's a signed 16-bit number; ~32,000+ partitions.
As to the effort:
To reiterate something I think everyone on this forum already knows: The rowid of a row is currently 24-bit logical page offset within a partition (or call it fragment) with the remaining 8-bits being the slot number. If I recall Dave Kosenko's class correctly, this has been the case since Informix-OnLine 4.0. This is *SUCH* an entrenched assumption in all the code; I don't see how they could comb the code for where to change that rowid. Yeah, with 20/20 hindsight (with which we are all gifted), we could have made the rowid a 64-bit combination of:
- Partition number (Assuming 16 bits)
- Logical page offset within partition (and let's raise that to 32 bits, unsigned)
- Slot number within the page (and let's raise that to 16 bits)
It would also eliminate the need for the bogus rowid used in "create table ... {partition options} WITH ROWID"
But that's hindsight for you.
That's my $0.02 (US) on the matter.
-- Jacob S.
------------------------------
Jacob Salomon
------------------------------
#Informix