Disconnect users during work partitionmanage split

Valeriy,

First, partition split is a very expensive operation, because it involves a lot of data scanning, deletion, creation, index build etc. from both source partition and multiple target partitions. For many database vendors, this operation is actually offline, or will leave the whole table usable till indexes are built later. Although we have tried very hard to improve this feature, we still cannot make partitions involved fully online, for example, to allow full CRUD access on them while split is in process. Because of these, it needs more serious considerations to decide when and how to split partitions, which is different with other "lighter" operations such as idxcheck.

Second, the "waiting on lock" messages indicate there are users working on the partitions that are being split at the same time. Both clients and split process are competing to lock down the partitions involved. Split needs an EXCL partition lock to work properly, but it will release the lock from time to time, for example, after "recs" of records have been split, or the partition is processed. So it does allow some chances for clients to access the partition even though it's working on it.

Third, if a client is not working on the table or partition that is being split, it will not be affected.

Hope this will help.

Dapeng

Tables average size is ~600 Гб. Busines should be work 24x7x365. Users +/- 10 000.

So, it is not possible to implement Table Partitioning in such large system. But for small databases it is not needed.  

I want to point out that it is not necessary (or should not be necessary) to set the split-target flag on all partitions in a table at the same time. You can therefore limit each partition-split operation to just one, or a small number of partitions. By selective splitting and scheduling of the split operations it may be possible to minimize the impact on the majority of users.

Maybe in the theory is good, but in practice it is a big risk.

Interesting somebody already implemented Table Partitioning in their production databases?

Is the risk that the data will not be available to users until split section ends.

Simple example -  table size 600 GB. We have created six sections (List), each section is about 100 GB. What do you think, how much time you will need to split one section? 1 hour? 6 hours? 1 day? more? All this time, the data in this partition will not be available to users, so the business application will not work, because the table interrelated.

I am wondering to know the experience of those who alredy applied TP for tables which size from 500 GB to 2 TB, and with +/- 2000 users in online.

And one more question. When a split partition we know that the data is not available, but how does it look? These data will not be included in the request, or the user will receive a warning?

P.S>

I apologize for not answering at once.

The notification from the Community do not work for me (

Besides queries using GLOBAL indexes, there are many cases that we do allow access to data that is being split silently. In addition, once a target partition is being split out, its data will also be available right away.

The actual time to finish the split depends on the amount of data to move and the speed of your system. Its major operations involve the followings:

1. read data that needs to be split;

2. create data and indexes in target partition;

3. delete data and indexes from source partition.

These steps are similar to what tablemove does, so that might give you some idea how much time it will take.

Another common way to move data is dump/load/build index. Because this solution needs extra I/O on dump files, split should be faster compared to it.

Dapeng

The current state of partitioning in OpenEdge isn't the best for large databases/tables. Either you have to dump and reload (outages) or you just don't implement it for large databases.

Having to kick users out combined with the amount of time required to actually split partitions and the vast amounts of AI generated (with the resulting issues for hot spares) make splitting online an unrealistic option for most large installs.

Most of the people that can realistically implement partitioning (today) are not the ones who actually need it.

Still... It is a good starting point and I have hopes that PSC will continue making improvements on this and all of the online utilities.

We understand all the difficulties that customers with large db are facing during TP migration. While we’ll continue working on improvements in this area, customers with smaller databases should actually start to consider TP, even if they don’t need it now --- the data can only grow bigger!

Dapeng

Rich: I understand completely why you (PSC) did what you did for the first release. Given how long a few of us had wanted partitioning it is indeed appreciated.  I just hope that in the next few years we look back on the first release of partitioning like we do on ancient versions of idxbuild,online schema changes,etc. now.

If the partition split could happen with users online and make it multi-threaded that would help quite a bit... especially if the AI apply was multi-threaded as well. Something along the lines of locking a record or even all of the records in a block and then release them when moved. So we could realistically split the partitions over time without having to take an extended outage or series of outages.

The other option would be to come up with a multi-threaded offline split utility that would split the partitions much faster than the time it takes to dump, reload and idxbuild. Even if it didn't rebuild the indexes we could manage that by rebuilding per partition at least (with all the cool idxbuild parameters we have now).

Asking a 24/7 or even a 24/6 shop for either a large outage or a number of smaller outages isn't easy especially since most of what we (customers in general) are getting out of TP version 1 is ease of maintenance and not drastic performance improvements.

Just about the need for a multithreading. Here is a small part of my experiments:

$ time proutil largedb3 -C partitionmanage split table theTable composite initial
OpenEdge Release 11.5.1 as of Tue Mar 31 19:00:25 EDT 2015

BEGIN: Split Operation For Table theTable (17384)

             Source Partition initial[0]
             Target Partition IntField-1[3]
             Target Partition IntField-2[4]
             Target Partition IntField-3[5]

Index IntField has been identified as the scanning index (selected).

A non-unique index has been selected as the useindex index.
Additional locking is required with the use of this index IntField.

Number of Records per Transaction (recs): 100

Do you want to continue (y/n)?
y
1000000 records processed. (15165)
2000000 records processed. (15165)
3000000 records processed. (15165)
4000000 records processed. (15165)
5000000 records processed. (15165)
6000000 records processed. (15165)
7000000 records processed. (15165)
8000000 records processed. (15165)
       Target partition: IntField-1[3], records moved: 8184959.
9000000 records processed. (15165)
10000000 records processed. (15165)
11000000 records processed. (15165)
12000000 records processed. (15165)
13000000 records processed. (15165)
14000000 records processed. (15165)
15000000 records processed. (15165)
16000000 records processed. (15165)
       Target partition: IntField-2[4], records moved: 8183704.
17000000 records processed. (15165)
18000000 records processed. (15165)
19000000 records processed. (15165)
20000000 records processed. (15165)
21000000 records processed. (15165)
22000000 records processed. (15165)
23000000 records processed. (15165)
24000000 records processed. (15165)
       Target partition: IntField-3[5], records moved: 8181016.
       Source partition: initial[0], still contains records.
       Total records processed: 24549679.

END: Split Operation For Table theTable[0]
Split Operation finished successfully. (17359)

real    1654m33.155s
user    376m48.248s
sys     16m30.662s