Difference in space_occupied by disk_storage and du -s(Linux commond)
Hi Team,
Can anybody tell me why the Disk_storage Table is showing me more space occupied than the Linux command.
SELECT node_name, SUM(disk_space_used_mb)/1024 AS disk_space_used_gb, SUM(disk_space_free_mb)/1024 AS disk_space_free_gb, SUM((disk_space_used_mb+disk_space_free_mb)/1024) AS disk_space_total_gb FROM v_monitor.disk_storage WHERE storage_usage = 'DATA,TEMP' GROUP BY node_name ORDER BY node_name;
node_name,disk_space_used_gb,disk_space_free_gb,disk_space_total_gb
v_vertica_node0001 105.85 1043.81 1149.66
v_vertica_node0002 113.56 1036.09 1149.66
v_vertica_node0003 105.84 1043.81 1149.66
v_vertica_node0004 105.81 1043.84 1149.66
Table is telling me that node is occupied with 105.85 GB where Linux Below telling only 45 GB is occupied.
cd /vertica/data/vertica
du -s
47343816 ~ 45GB only
Regards
--Noor Ahmed
Can anybody tell me why the Disk_storage Table is showing me more space occupied than the Linux command.
SELECT node_name, SUM(disk_space_used_mb)/1024 AS disk_space_used_gb, SUM(disk_space_free_mb)/1024 AS disk_space_free_gb, SUM((disk_space_used_mb+disk_space_free_mb)/1024) AS disk_space_total_gb FROM v_monitor.disk_storage WHERE storage_usage = 'DATA,TEMP' GROUP BY node_name ORDER BY node_name;
node_name,disk_space_used_gb,disk_space_free_gb,disk_space_total_gb
v_vertica_node0001 105.85 1043.81 1149.66
v_vertica_node0002 113.56 1036.09 1149.66
v_vertica_node0003 105.84 1043.81 1149.66
v_vertica_node0004 105.81 1043.84 1149.66
Table is telling me that node is occupied with 105.85 GB where Linux Below telling only 45 GB is occupied.
cd /vertica/data/vertica
du -s
47343816 ~ 45GB only
Regards
--Noor Ahmed
0
Comments
I have varified this. Verica is associated with only one mount point.
SELECT node_name, storage_path, SUM(disk_space_used_mb)/1024 AS disk_space_used_gb,
SUM(disk_space_free_mb)/1024 AS disk_space_free_gb,
SUM((disk_space_used_mb+disk_space_free_mb)/1024) AS disk_space_total_gb
FROM v_monitor.disk_storage
WHERE storage_usage = 'DATA,TEMP'
GROUP BY node_name, storage_path
ORDER BY node_name;
node_name,storage_path,disk_space_used_gb,disk_space_free_gb,disk_space_total_gb
v_vertica_node0001 /vertica/data/vertica/v_vertica_node0001_data 133.62 1016.04 1149.66
v_vertica_node0002 /vertica/data/vertica/v_vertica_node0002_data 439.47 710.19 1149.66
v_vertica_node0003 /vertica/data/vertica/v_vertica_node0003_data 133.61 1016.05 1149.66 v_vertica_node0004 /vertica/data/vertica/v_vertica_node0004_data 133.62 1016.04 1149.66
Linux results (Node 1)
cd /vertica/data/vertica
du -s
78760668 ~75Gb
And when i check the node 2
cd /vertica/data/vertica
du -s
78652260 ~75 GB
But Vetica showing me 439 GB. Its huge huge difference.
Can you let me know why the difference is?
Regards
--Noor Ahmed
But i need to give the estimation of memory occupied by Vertica Database as compared to Other DB, to one of my customer and it is very crucial. Can you try to help me ASAP.
I have checked the Projection Storage. As per my understanding if I sum it ROS_used_Bytes and WOS _Used_Bytes, It should give me the total size in Bytes. I guess This is giving me right result. Means same as that Approximately Linux command(75 GB).
SELECT PROJECTION_SCHEMA, 'AAAA-Total size' anchor_table_name, 'All Projections' as projection_name, node_name, SUM(USED_BYTES)/1024/1024/1024 as Used_bytes_In_GB,
(SUM(ROS_USED_BYTES)+SUM(WOS_USED_BYTES))/1024/1024/1024 AS TOTAL_SIZE_IN_GB
FROM PROJECTION_STORAGE
WHERE ANCHOR_TABLE_SCHEMA= 'arc_dw_ca_etl'
GROUP BY PROJECTION_SCHEMA,node_name;
PROJECTION_SCHEMA,anchor_table_name,projection_name,node_name,Used_bytes_In_GB,TOTAL_SIZE_IN_GB
arc_dw_ca_etl AAAA-Total size All Projections v_vertica_node0001 74.09 74.09
arc_dw_ca_etl AAAA-Total size All Projections v_vertica_node0004 74.09 74.09
arc_dw_ca_etl AAAA-Total size All Projections v_vertica_node0003 74.09 74.09
arc_dw_ca_etl AAAA-Total size All Projections v_vertica_node0002 74.09 74.09
But Still Disk Stogare showing the 113 GB occupied. Which is wrong.
SELECT node_name, storage_path, SUM(disk_space_used_mb)/1024 AS disk_space_used_gb,
SUM(disk_space_free_mb)/1024 AS disk_space_free_gb,
SUM((disk_space_used_mb+disk_space_free_mb)/1024) AS disk_space_total_gb
FROM v_monitor.disk_storage
WHERE storage_usage = 'DATA,TEMP'
GROUP BY node_name, storage_path
ORDER BY node_name;
node_name,storage_path,disk_space_used_gb,disk_space_free_gb,disk_space_total_gb
v_vertica_node0001 /vertica/data/vertica/v_vertica_node0001_data 133.62 1016.04 1149.66
v_vertica_node0002 /vertica/data/vertica/v_vertica_node0002_data 439.47 710.19 1149.66
v_vertica_node0003 /vertica/data/vertica/v_vertica_node0003_data 133.61 1016.05 1149.66 v_vertica_node0004 /vertica/data/vertica/v_vertica_node0004_data 133.62 1016.04 1149.66
And even I have verified with GET_COMPLIANCE_STATUS
select GET_COMPLIANCE_STATUS();
Raw Data Size: 0.59TB +/- 0.02TB
License Size : 5.00TB
Utilization : 12%
Audit Time : 2014-04-11 06:52:22.790877-04
Compliance Status : The database is in compliance with respect to raw data size.
This is matching with Disk_stogare(~133*4) but not with either Linux nor with Projection_storage.
Please, please Can some one look into this?
Regards
--Noor Ahmed
Curtis -- hope you don't mind if I cut in briefly... Noor -- just curious, you have posted the output of "du" on the partition in question; what is the output of "df"?
Thanks,
Adam
df is also showing me 76 G is used.
df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 7.9G 2.4G 5.2G 32% /
tmpfs 30G 0 30G 0% /dev/shm
/dev/md127 1.2T 76G 1017G 7% /vertica/data
Regards
--Noor Ahmed