cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Moroder (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CLOUDSTACK-8980) CloudStack 4.5.2 not reporting correct total capacities on MariaDB-server 10.1
Date Mon, 26 Oct 2015 12:48:28 GMT

    [ https://issues.apache.org/jira/browse/CLOUDSTACK-8980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14974152#comment-14974152
] 

Thomas Moroder commented on CLOUDSTACK-8980:
--------------------------------------------

As it seems this bug is reproducible and in the Optimizer of MariaDB 10.1 as confirmed here:
https://mariadb.atlassian.net/browse/MDEV-8988

Once a fix will be provided by upstream, I will re-test. But the SQL seems to be correct,
so it is not a CS bug AFAIK.

> CloudStack 4.5.2 not reporting correct total capacities on MariaDB-server 10.1
> ------------------------------------------------------------------------------
>
>                 Key: CLOUDSTACK-8980
>                 URL: https://issues.apache.org/jira/browse/CLOUDSTACK-8980
>             Project: CloudStack
>          Issue Type: Bug
>      Security Level: Public(Anyone can view this level - this is the default.) 
>          Components: Management Server
>    Affects Versions: 4.5.2
>         Environment: CentOS release 6.7 with MariaDB-server-10.1.8-1.el6.x86_64
>            Reporter: Thomas Moroder
>              Labels: cloudstack-management, mariadb
>
> The CPU and memory total capacity as reported by the API and as visible on the CS dashboard
are shown as zero. This is due to the following query failing to get correct results when
the database is run on MariaDB 10.1:
> SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type
when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name=
'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity)
* (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio'
AND cluster_details.cluster_id=capacity.cluster_id))else sum(total_capacity) end),((sum(capacity.used_capacity)
+ sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity)
* (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio'
AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) *
(select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio'
AND cluster_details.cluster_id=capacity.cluster_id)) else sum(total_capacity) end)) percent,capacity.capacity_type,
capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE
 total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled'  AND capacity.data_center_id
= ? AND capacity.cluster_id = ? AND capacity.capacity_type = ? GROUP BY data_center_id, pod_id,
cluster_id, capacity_type
> The output is incorrect and like this:
> +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
> | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type
when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name=
'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity)
* (select value fro | percent | capacity_type | data_center_id | pod_id | cluster_id |
> +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
> |                404750336000 |                               0 |                   
                                                                                         
                                                                                         
                                                     NULL |    NULL |             0 |    
         3 |      3 |          3 |
> +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
> After some analysis this seems due to the value of "capacity.cluster_id" as set in the
final WHERE-clause not being used in the (nested) previous WHERE clauses like in:
> cluster_details.cluster_id=capacity.cluster_id
> If I manually update the query by specifying the cluster_id-number it works correctly,
e.g.:
> SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type
when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name=
'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity)
* (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio'
AND cluster_details.cluster_id=3))else sum(total_capacity) end),((sum(capacity.used_capacity)
+ sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity)
* (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio'
AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from
`cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3))
else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id,
cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE  total_capacity > 0 AND data_center_id
is not null AND capacity_state='Enabled'  AND capacity.data_center_id = 3 AND capacity.cluster_id
= 3 AND capacity.capacity_type = 0 GROUP BY data_center_id, pod_id, cluster_id, capacity_type
> The output then is correct and like this:
> +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
> | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type
when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name=
'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity)
* (select value from `cloud`.`cluster | percent             | capacity_type | data_center_id
| pod_id | cluster_id |
> +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
> |                404750336000 |                               0 |                   
                                                                                         
                                                                                         
                                             810895474688 | 0.49913996147990797 |        
    0 |              3 |      3 |          3 |
> +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
> 1 row in set (0.00 sec)
> I don't know if this is an issue with MariaDB or correct SQL to use; the query works
on older MySQL versions.
> As a workaround an older MySQL-version can be used. If this needs fixing, then the CS
management server could specify the cluster_id during the query in all the required places.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message