Index: /tags/ipp-20110622/Nebulous-Server/bin/neb-admin =================================================================== --- /tags/ipp-20110622/Nebulous-Server/bin/neb-admin (revision 31889) +++ /tags/ipp-20110622/Nebulous-Server/bin/neb-admin (revision 31890) @@ -299,68 +299,132 @@ " SELECT * FROM ( - -- U Randomize the list of all possible source/destination matches,then group by so_id to select one at random - SELECT T.*,volume.cab_id AS source_cab_id,volume.name AS source_name,volume.host AS source_host, - destination.vol_id AS destination_vol_id,destination.cab_id AS destination_cab_id, - destination.name AS destination_name,destination.host AS destination_host FROM ( - -- V Ensure that the copy we found on the source volume is the second copy instance - SELECT K.vol_id AS here,K.ins_id,K.so_id,ext_id,value AS user_copies,MAXins_id,instance.vol_id AS there FROM ( - -- W Determine what the second copy instance is - SELECT V.*,MAX(instance.ins_id) AS MAXins_id FROM ( - -- X Determine which hosts have instances of an object - SELECT vol_id,ins_id,so_id,ext_id,value FROM - storage_object JOIN storage_object_xattr USING(so_id) JOIN instance USING(so_id) - WHERE vol_id IN ( - -- Y Extra select for debugging purposes - SELECT vol_id FROM ( - -- Z Ran hosts by R (need to be a source) and return top N - SELECT mountedvol.vol_id,mountedvol.name,mountedvol.available,mountedvol.allocate,total,used, - (used / total) AS D,total * ((used / total) - $average ) AS R, -- average - (used / total) - $average AS delta, - cab_id FROM - mountedvol JOIN volume USING(vol_id) - WHERE mountedvol.available = 1 - ORDER BY delta DESC LIMIT $balance_N_sources -- N - ) AS ranked_sources - -- Z End - ) - -- Y End - AND name = 'user.copies' AND value >= 2 - AND ext_id LIKE '%ota%fits' -- This line is the emergency speedup option - AND so_id >= $so_id_start -- so_id_start - AND so_id < $so_id_end -- so_id_end - ) AS V -- volumes that host a copy - -- X End - LEFT OUTER JOIN instance USING(so_id) GROUP BY so_id - ) AS K -- copies that are the second copy - -- W End - JOIN instance ON MAXins_id = instance.ins_id GROUP BY so_id - ) AS T -- matched copies that I know the second copy is on my source volume - -- V End - JOIN volume on T.here = volume.vol_id RIGHT JOIN ( - -- Y2 Chose a random destination from the list - SELECT vol_id,cab_id,name,host FROM ( - -- Z2 Reverse rank hosts by R and return top M - SELECT mountedvol.vol_id,mountedvol.name,mountedvol.host,mountedvol.available,mountedvol.allocate,total,used, - (used / total) AS D,total * ((used / total) - $average ) AS R, -- average - (used / total) - $average AS delta, - cab_id FROM - mountedvol JOIN volume USING(vol_id) - WHERE mountedvol.available = 1 AND mountedvol.allocate = 1 - ORDER BY delta ASC LIMIT $balance_M_destinations -- M - ) AS ranked_destinations - -- Z2 End - ORDER BY RAND() - ) AS destination - -- Y2 End - ON destination.cab_id != volume.cab_id - WHERE here = there - ORDER BY RAND() +# -- U Randomize the list of all possible source/destination matches,then group by so_id to select one at random + SELECT T.*,volume.cab_id AS source_cab_id, volume.name AS source_name, volume.host AS source_host, + destination.vol_id AS destination_vol_id,destination.cab_id AS destination_cab_id, + destination.name AS destination_name,destination.host AS destination_host FROM ( + -- V Select the volume that matches the instance with the largest ins_id + SELECT S.*,vol_id FROM ( + -- Select objects that are in our so_id range, with two copies (and two requested copies) + SELECT MAX(ins_id) AS MAXins_id,MIN(ins_id) AS MINins_id,so_id,ext_id,value AS user_copies FROM + storage_object JOIN storage_object_xattr USING(so_id) RIGHT JOIN instance USING(so_id) + WHERE name = 'user.copies' AND value >= 2 + AND ext_id LIKE '%ota%fits' -- This line is the emergency speedup option + AND so_id >= $so_id_start -- so_id_start + AND so_id < $so_id_end -- so_id_end + GROUP BY so_id + ) AS S + JOIN instance ON (S.MAXins_id = instance.ins_id) + WHERE vol_id IN ( + -- Y Extra select for debugging purposes + SELECT vol_id FROM ( + -- Z Ran hosts by R (need to be a source) and return top N + SELECT mountedvol.vol_id,mountedvol.name,mountedvol.available,mountedvol.allocate,total,used, + (used / total) AS D,total * ((used / total) - $average ) AS R, -- average + (used / total) - $average AS delta, + cab_id FROM + mountedvol JOIN volume USING(vol_id) + WHERE mountedvol.available = 1 + ORDER BY delta DESC LIMIT $balance_N_sources -- N + ) AS ranked_sources + -- Z End + ) + -- Y End + AND MAXins_id != MINins_id + ) AS T + -- V End + JOIN volume on T.vol_id = volume.vol_id RIGHT JOIN ( + -- Y2 Chose a random destination from the list + SELECT vol_id,cab_id,name,host FROM ( + -- Z2 Reverse rank hosts by R and return top M + SELECT mountedvol.vol_id,mountedvol.name,mountedvol.host,mountedvol.available,mountedvol.allocate,total,used, + (used / total) AS D,total * ((used / total) - $average ) AS R, -- average + (used / total) - $average AS delta, + cab_id FROM + mountedvol JOIN volume USING(vol_id) + WHERE mountedvol.available = 1 AND mountedvol.allocate = 1 + ORDER BY delta ASC LIMIT $balance_M_destinations -- M + ) AS ranked_destinations + -- Z2 End + ORDER BY RAND() + ) AS destination + -- Y2 End + ON destination.cab_id != volume.cab_id + ORDER BY RAND() ) AS results +WHERE so_id IS NOT NULL -- U End GROUP BY so_id - LIMIT $limit -- limit - OFFSET $offset -- offset - -" ); + LIMIT $limit -- limit + OFFSET $offset -- offset + +"); + +# my $query = $dbh->prepare( +# " +# SELECT * FROM ( +# -- U Randomize the list of all possible source/destination matches,then group by so_id to select one at random +# SELECT T.*,volume.cab_id AS source_cab_id,volume.name AS source_name,volume.host AS source_host, +# destination.vol_id AS destination_vol_id,destination.cab_id AS destination_cab_id, +# destination.name AS destination_name,destination.host AS destination_host FROM ( +# -- V Ensure that the copy we found on the source volume is the second copy instance +# SELECT K.vol_id AS here,K.ins_id,K.so_id,ext_id,value AS user_copies,MAXins_id,instance.vol_id AS there FROM ( +# -- W Determine what the second copy instance is +# SELECT V.*,MAX(instance.ins_id) AS MAXins_id FROM ( +# -- X Determine which hosts have instances of an object +# SELECT vol_id,ins_id,so_id,ext_id,value FROM +# storage_object JOIN storage_object_xattr USING(so_id) JOIN instance USING(so_id) +# WHERE vol_id IN ( +# -- Y Extra select for debugging purposes +# SELECT vol_id FROM ( +# -- Z Ran hosts by R (need to be a source) and return top N +# SELECT mountedvol.vol_id,mountedvol.name,mountedvol.available,mountedvol.allocate,total,used, +# (used / total) AS D,total * ((used / total) - $average ) AS R, -- average +# (used / total) - $average AS delta, +# cab_id FROM +# mountedvol JOIN volume USING(vol_id) +# WHERE mountedvol.available = 1 +# ORDER BY delta DESC LIMIT $balance_N_sources -- N +# ) AS ranked_sources +# -- Z End +# ) +# -- Y End +# AND name = 'user.copies' AND value >= 2 +# AND ext_id LIKE '%ota%fits' -- This line is the emergency speedup option +# AND so_id >= $so_id_start -- so_id_start +# AND so_id < $so_id_end -- so_id_end +# ) AS V -- volumes that host a copy +# -- X End +# LEFT OUTER JOIN instance USING(so_id) GROUP BY so_id +# ) AS K -- copies that are the second copy +# -- W End +# JOIN instance ON MAXins_id = instance.ins_id GROUP BY so_id +# ) AS T -- matched copies that I know the second copy is on my source volume +# -- V End +# JOIN volume on T.here = volume.vol_id RIGHT JOIN ( +# -- Y2 Chose a random destination from the list +# SELECT vol_id,cab_id,name,host FROM ( +# -- Z2 Reverse rank hosts by R and return top M +# SELECT mountedvol.vol_id,mountedvol.name,mountedvol.host,mountedvol.available,mountedvol.allocate,total,used, +# (used / total) AS D,total * ((used / total) - $average ) AS R, -- average +# (used / total) - $average AS delta, +# cab_id FROM +# mountedvol JOIN volume USING(vol_id) +# WHERE mountedvol.available = 1 AND mountedvol.allocate = 1 +# ORDER BY delta ASC LIMIT $balance_M_destinations -- M +# ) AS ranked_destinations +# -- Z2 End +# ORDER BY RAND() +# ) AS destination +# -- Y2 End +# ON destination.cab_id != volume.cab_id +# WHERE here = there +# ORDER BY RAND() +# ) AS results +# -- U End +# GROUP BY so_id +# LIMIT $limit -- limit +# OFFSET $offset -- offset + +# " ); $query->execute(); @@ -389,7 +453,7 @@ - my $here = $obj->{here}; - my $there = $obj->{there}; - my $ins_id = $obj->{ins_id}; +# my $here = $obj->{here}; +# my $there = $obj->{there}; +# my $ins_id = $obj->{ins_id}; my $max_ins_id = $obj->{MAXins_id}; @@ -398,5 +462,5 @@ my $copies = $obj->{user_copies}; - my $source_vol_id = $obj->{here}; + my $source_vol_id = $obj->{vol_id}; my $source_cab_id = $obj->{source_cab_id}; my $source_name = $obj->{source_name}; @@ -408,6 +472,6 @@ my $destination_host = $obj->{destination_host}; - unless ($here == $there) { $Npending-- ; next; } - unless ($ins_id == $max_ins_id) { $Npending-- ; next; } +# unless ($here == $there) { $Npending-- ; next; } +# unless ($ins_id == $max_ins_id) { $Npending-- ; next; } unless ($source_cab_id != $destination_cab_id) { $Npending-- ; next; }