catch.saravana
Member
Version: 11.6
OS: Linux CentOS 7
CPU: 4 cpu with 2 core each
I have 3 questions here, sorry if I should have put it as separate threads.
Question 1:
How do I come up with optimal values for -datascanthreads and -mergescanthreads?
Question 2:
Does -B have any effect on index build? - the reason why I asked this question is even if I give -B as 40GB it consumes almost all free memory space (i.e., around 110 GB) to run this index build [was monitoring using htop].
Question 3:
I created a DB on local disk 3 weeks back. It roughly took 12 hours for completing the D&L process of 1.5 TB database and 5.8 hours for completing Index Build. I was able to login and did some smoke testing on the application and felt all good [sudo /opt/dlc/bin/proutil /localdisk/xyzdb/xyz.db -C idxbuild all -i -TB 64 -TM 32 -TMB 512 -SG 64 -thread 1 -TF 80 -datascanthreads 12 -mergethreads 8 -T /localdisk/tmpForidx –B 5000000].
Couple of days back I created a new DB on localdisk with just one large table of size 350 GB and ran index rebuild multiple times with different DST & MST (say first run DST 16 MST 8, second run DST 20 MST 12, third run DST 32 MST 20 etc...). When I ran with DST 16 MST 8 it took around 72 minutes for index build to complete whereas when I ran with DST 32 and MST 20 it gave me better results where it took around 48 minutes to complete). Then I loaded one more table, our second largest table which will be another 200 GB and tested the same combinations and found better results for DST 32 and MST 20.
Looking at the results I ran index rebuild on the db that I created 3 weeks back with DST as 32, MST as 20 and rest of the parameters are same as mentioned above. It took 32 hours to get this completed (for a sample I can say the largest table that was taking 45 minutes when I ran on the 2 table db test takes around 7 hrs on this 1.5 TB db). IO Wait Time was less than 5 to 7%, CPU was free most of the times, si & so on VMSTAT was 0. Looking at the results I thought of going back to the original values to re-test the same with DST and 12 and MST as 8; it took 43 hours to get this completed. What could be the issue in this case?
Only couple of things were bit suspicious;
1. Swap memory (2 GB) shows full when I run htop for the current runs but 3 weeks back it was showing as completely free. But even with swap memory full the 2 table db test that I ran 2 days back and today gives me same results around 65 minutes which is good. It screws up only when I run it for the large db.
2. Process state is mostly in 'D' (uninterruptible sleep) but as I said IO Wait Time is around 5 to 7%.
What could be the reason? What more should I monitor to get this issue resolved?
OS: Linux CentOS 7
CPU: 4 cpu with 2 core each
I have 3 questions here, sorry if I should have put it as separate threads.
Question 1:
How do I come up with optimal values for -datascanthreads and -mergescanthreads?
Question 2:
Does -B have any effect on index build? - the reason why I asked this question is even if I give -B as 40GB it consumes almost all free memory space (i.e., around 110 GB) to run this index build [was monitoring using htop].
Question 3:
I created a DB on local disk 3 weeks back. It roughly took 12 hours for completing the D&L process of 1.5 TB database and 5.8 hours for completing Index Build. I was able to login and did some smoke testing on the application and felt all good [sudo /opt/dlc/bin/proutil /localdisk/xyzdb/xyz.db -C idxbuild all -i -TB 64 -TM 32 -TMB 512 -SG 64 -thread 1 -TF 80 -datascanthreads 12 -mergethreads 8 -T /localdisk/tmpForidx –B 5000000].
Couple of days back I created a new DB on localdisk with just one large table of size 350 GB and ran index rebuild multiple times with different DST & MST (say first run DST 16 MST 8, second run DST 20 MST 12, third run DST 32 MST 20 etc...). When I ran with DST 16 MST 8 it took around 72 minutes for index build to complete whereas when I ran with DST 32 and MST 20 it gave me better results where it took around 48 minutes to complete). Then I loaded one more table, our second largest table which will be another 200 GB and tested the same combinations and found better results for DST 32 and MST 20.
Looking at the results I ran index rebuild on the db that I created 3 weeks back with DST as 32, MST as 20 and rest of the parameters are same as mentioned above. It took 32 hours to get this completed (for a sample I can say the largest table that was taking 45 minutes when I ran on the 2 table db test takes around 7 hrs on this 1.5 TB db). IO Wait Time was less than 5 to 7%, CPU was free most of the times, si & so on VMSTAT was 0. Looking at the results I thought of going back to the original values to re-test the same with DST and 12 and MST as 8; it took 43 hours to get this completed. What could be the issue in this case?
Only couple of things were bit suspicious;
1. Swap memory (2 GB) shows full when I run htop for the current runs but 3 weeks back it was showing as completely free. But even with swap memory full the 2 table db test that I ran 2 days back and today gives me same results around 65 minutes which is good. It screws up only when I run it for the large db.
2. Process state is mostly in 'D' (uninterruptible sleep) but as I said IO Wait Time is around 5 to 7%.
What could be the reason? What more should I monitor to get this issue resolved?