We have been migrating one big MySQL table (about 10 TB) into Oracle 11g R2. The table contains biometric data in 18 BLOB columns - it's a kind of storage table for all biometric information in the form of BLOB. The avg. row length is about 140KB. We had a rough estimation - it might take about 14 TB of disk spaces after migrating the thing into Oracle. We planned to put this table in a 32K bock size tablespace. The table contains about 85 million records.
After we finished loading the data, we discovered that about 30 millions of records already consumed more than 11 TB of disk space! In this fashion, we might end up with consuming 33TB of disk spaces after loading the 85 million of records!
When I came back form a nice vacation, I had been assigned to solve the mystery. Ahh.. the situation was not good! I was kind of clueless at the beginning and looking for right approach to debug the problem.
After playing with some real data I found that, out of 18 BLOBs -
* Some columns are < 4K
* Some of the BLOBs > 4K but < 7K
* Some are > 8K but less than < 14K
* Some are > 20K
Then, I reorganized the table and moved BLOBs in different block size (8K, 16K, 32K) tablespaces based on their data size. This saved space wastage for data which were > 4K. Earlier, entire table was on 32K block size tablespace. Before the reorganization, the size was over 11.5 TB, which is now 6.6 TB! It took about 7 days (nights as well :-) ) to move all LOB columns to proper tablespaces.
Now, the total size of the database would be within 19TB - not 33 TB!
Some important scripts I used -
SELECT l.column_name, s.bytes/1024 KB, s.blocks, s.extents
FROM user_segments s, user_lobs l
WHERE l.segment_name = s.segment_name
AND l.table_name = 'BIOMETRIC';
ALTER TABLE biometric MOVE LOB (wsq) STORE AS (tablespace BMDATA_16K CHUNK 16K pctversion 3);
Good lessons here -
* We should have tested with some real data before the actual data loading
* We should have observed disk usage after a day from initiating the data loading
---
No comments:
Post a Comment