You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cloudstack.apache.org by David Williams <Da...@interoute.com> on 2015/01/06 11:06:08 UTC
How to reduce the size of the cloud_usage database
Hi all,
Is there an "official" procedure on how to reduce the size of the cloud_usage database?
Thanks,
Dave
RE: How to reduce the size of the cloud_usage database
Posted by Somesh Naidu <So...@citrix.com>.
Dave,
Deleting records from cloud_usage is safe and permitted. These records are not used by any CS functionality. This data (raw usage data) is consumed by entities external to CS (billing software, etc). This data is exposed by CS via listUsage API. Deleting records from cloud_usage table will only cause this API to not return raw usage pertaining to those deleted records but not cause the API to break/crash.
I had written a KB article for Citrix Cloudplatform on this subject as well - http://support.citrix.com/article/CTX139043
You cloud replace "truncate table" with "delete from" with a where clause if you wanted to keep records until certain time and purge older, something like below:
delete from cloud_usage where start_date < date_sub(current_date(), interval 30 day);
Hope that helps.
-----Original Message-----
From: David Williams [mailto:David.Williams@interoute.com]
Sent: Tuesday, January 06, 2015 5:06 AM
To: users@cloudstack.apache.org
Subject: How to reduce the size of the cloud_usage database
Hi all,
Is there an "official" procedure on how to reduce the size of the cloud_usage database?
Thanks,
Dave
Re: How to reduce the size of the cloud_usage database
Posted by Rene Moser <ma...@renemoser.net>.
Hi
On 01/06/2015 11:06 AM, David Williams wrote:
> Is there an "official" procedure on how to reduce the size of the cloud_usage database?
We are using pt-archiver [1] which is included in the percona-toolkit
[2]. It may be already in your Linux distro [3] as well.
Yours
René
[1] http://www.percona.com/doc/percona-toolkit/2.2/pt-archiver.html
[2] http://www.percona.com/software/percona-toolkit
[3] http://packages.ubuntu.com/trusty/percona-toolkit
Re: How to reduce the size of the cloud_usage database
Posted by Rohit Yadav <ro...@shapeblue.com>.
> On 23-Jan-2015, at 8:40 pm, Abhinandan Prateek <ab...@shapeblue.com> wrote:
>
> Will this also take care of usage server not regenerating usage data on restart before a certain cut-off date ?
Since this is an API, its usage is at the discretion of the admin. This has nothing to do with server not generating usage data. For more information checkout Somesh’s email on the thread.
This is by default only enabled for the admin only.
Regards,
Rohit Yadav
Software Architect, ShapeBlue
M. +91 88 262 30892 | rohit.yadav@shapeblue.com
Blog: bhaisaab.org | Twitter: @_bhaisaab
Find out more about ShapeBlue and our range of CloudStack related services
IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
CSForge – rapid IaaS deployment framework<http://shapeblue.com/csforge/>
CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.
Re: How to reduce the size of the cloud_usage database
Posted by Abhinandan Prateek <ab...@shapeblue.com>.
Will this also take care of usage server not regenerating usage data on restart before a certain cut-off date ?
> On 23-Jan-2015, at 7:43 pm, Rohit Yadav <ro...@shapeblue.com> wrote:
>
> Hi,
>
> Instead of a automatic thread that would clean up cloud_usage database, I’ve implemented a new API (removeRawUsageRecords) that will be available with 4.6.0 and above. It takes an interval parameter which is an integer greater than 0, and this API when called removes all records from cloud_usage.cloud_usage table that are older than ‘interval’ number of days. It fails in a window of 15mins when using the usage job exec time (also considering the timezone) when the API is called.
>
> For details you may follow it here: https://issues.apache.org/jira/browse/CLOUDSTACK-8151
>
> Regards,
> Rohit Yadav
> Software Architect, ShapeBlue
> M. +91 88 262 30892 | rohit.yadav@shapeblue.com
> Blog: bhaisaab.org | Twitter: @_bhaisaab
>
> Find out more about ShapeBlue and our range of CloudStack related services
>
> IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
> CSForge – rapid IaaS deployment framework<http://shapeblue.com/csforge/>
> CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
> CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
> CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
> CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
>
> This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.
Find out more about ShapeBlue and our range of CloudStack related services
IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
CSForge – rapid IaaS deployment framework<http://shapeblue.com/csforge/>
CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.
Re: How to reduce the size of the cloud_usage database
Posted by Rohit Yadav <ro...@shapeblue.com>.
Hi,
Instead of a automatic thread that would clean up cloud_usage database, I’ve implemented a new API (removeRawUsageRecords) that will be available with 4.6.0 and above. It takes an interval parameter which is an integer greater than 0, and this API when called removes all records from cloud_usage.cloud_usage table that are older than ‘interval’ number of days. It fails in a window of 15mins when using the usage job exec time (also considering the timezone) when the API is called.
For details you may follow it here: https://issues.apache.org/jira/browse/CLOUDSTACK-8151
Regards,
Rohit Yadav
Software Architect, ShapeBlue
M. +91 88 262 30892 | rohit.yadav@shapeblue.com
Blog: bhaisaab.org | Twitter: @_bhaisaab
Find out more about ShapeBlue and our range of CloudStack related services
IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
CSForge – rapid IaaS deployment framework<http://shapeblue.com/csforge/>
CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.
Re: How to reduce the size of the cloud_usage database
Posted by Rohit Yadav <ro...@shapeblue.com>.
Hi David,
Thanks for sharing, I think it would be useful to have CloudStack do the cleaning up in a safer way.
I’ve added a ticket on JIRA and I think I can implement this small feature:
https://issues.apache.org/jira/browse/CLOUDSTACK-8151
> On 06-Jan-2015, at 5:01 pm, David Williams <Da...@interoute.com> wrote:
>
> Hi Rohit,
>
> Our cloud_usage database dumps are getting large and take quite a while. The cloud_usage database is around 25 GB with more than 86 million rows.
>
> I don't know if there are currently performance issues affecting the application.
>
> Having a global configuration option which causes the application to delete usage related records that are older than a specifed value would be great. An API would be great too.
>
> Thanks,
>
> Dave
>
> _____________________________________________
> From: Rohit Yadav [mailto:rohit.yadav@shapeblue.com]
> Sent: Tuesday, January 06, 2015 11:22
> To: users@cloudstack.apache.org
> Subject: Re: How to reduce the size of the cloud_usage database
>
>
> Hi David,
>
> There is no official way to reduce the size of the cloud_usage database
> as it gets populated from the cloud database.
>
> Can you share why you want to reduce it? Are you're facing any MySQL
> performance issues?
>
> If your DB size is causing problems we could work with you to develop a
> new (global) configuration option to limit size or an API to limit
> past/historic data.
>
> On Tuesday 06 January 2015 03:36 PM, David Williams wrote:
>> Hi all,
>>
>> Is there an "official" procedure on how to reduce the size of the cloud_usage database?
>>
>> Thanks,
>>
>> Dave
>
> Regards,
> Rohit Yadav
> Software Architect, ShapeBlue
> M. +91 8826230892 | rohit.yadav@shapeblue.com<ma...@shapeblue.com>
> Blog: bhaisaab.org | Twitter: @_bhaisaab
> PS. If you see any footer below, I did not add it :)
> Find out more about ShapeBlue and our range of CloudStack related services
>
> IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
> CSForge - rapid IaaS deployment framework<http://shapeblue.com/csforge/>
> CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
> CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
> CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
> CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
>
> This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.
>
Regards,
Rohit Yadav
Software Architect, ShapeBlue
M. +91 88 262 30892 | rohit.yadav@shapeblue.com
Blog: bhaisaab.org | Twitter: @_bhaisaab
Find out more about ShapeBlue and our range of CloudStack related services
IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
CSForge – rapid IaaS deployment framework<http://shapeblue.com/csforge/>
CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.
RE: How to reduce the size of the cloud_usage database
Posted by David Williams <Da...@interoute.com>.
Hi Rohit,
Our cloud_usage database dumps are getting large and take quite a while. The cloud_usage database is around 25 GB with more than 86 million rows.
I don't know if there are currently performance issues affecting the application.
Having a global configuration option which causes the application to delete usage related records that are older than a specifed value would be great. An API would be great too.
Thanks,
Dave
_____________________________________________
From: Rohit Yadav [mailto:rohit.yadav@shapeblue.com]
Sent: Tuesday, January 06, 2015 11:22
To: users@cloudstack.apache.org
Subject: Re: How to reduce the size of the cloud_usage database
Hi David,
There is no official way to reduce the size of the cloud_usage database
as it gets populated from the cloud database.
Can you share why you want to reduce it? Are you're facing any MySQL
performance issues?
If your DB size is causing problems we could work with you to develop a
new (global) configuration option to limit size or an API to limit
past/historic data.
On Tuesday 06 January 2015 03:36 PM, David Williams wrote:
> Hi all,
>
> Is there an "official" procedure on how to reduce the size of the cloud_usage database?
>
> Thanks,
>
> Dave
Regards,
Rohit Yadav
Software Architect, ShapeBlue
M. +91 8826230892 | rohit.yadav@shapeblue.com<ma...@shapeblue.com>
Blog: bhaisaab.org | Twitter: @_bhaisaab
PS. If you see any footer below, I did not add it :)
Find out more about ShapeBlue and our range of CloudStack related services
IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
CSForge - rapid IaaS deployment framework<http://shapeblue.com/csforge/>
CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.
Re: How to reduce the size of the cloud_usage database
Posted by Rohit Yadav <ro...@shapeblue.com>.
Hi David,
There is no official way to reduce the size of the cloud_usage database
as it gets populated from the cloud database.
Can you share why you want to reduce it? Are you're facing any MySQL
performance issues?
If your DB size is causing problems we could work with you to develop a
new (global) configuration option to limit size or an API to limit
past/historic data.
On Tuesday 06 January 2015 03:36 PM, David Williams wrote:
> Hi all,
>
> Is there an "official" procedure on how to reduce the size of the cloud_usage database?
>
> Thanks,
>
> Dave
Regards,
Rohit Yadav
Software Architect, ShapeBlue
M. +91 8826230892 | rohit.yadav@shapeblue.com
Blog: bhaisaab.org | Twitter: @_bhaisaab
PS. If you see any footer below, I did not add it :)
Find out more about ShapeBlue and our range of CloudStack related services
IaaS Cloud Design & Build<http://shapeblue.com/iaas-cloud-design-and-build//>
CSForge – rapid IaaS deployment framework<http://shapeblue.com/csforge/>
CloudStack Consulting<http://shapeblue.com/cloudstack-consultancy/>
CloudStack Software Engineering<http://shapeblue.com/cloudstack-software-engineering/>
CloudStack Infrastructure Support<http://shapeblue.com/cloudstack-infrastructure-support/>
CloudStack Bootcamp Training Courses<http://shapeblue.com/cloudstack-training/>
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Shape Blue Ltd or related companies. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. Shape Blue Ltd is a company incorporated in England & Wales. ShapeBlue Services India LLP is a company incorporated in India and is operated under license from Shape Blue Ltd. Shape Blue Brasil Consultoria Ltda is a company incorporated in Brasil and is operated under license from Shape Blue Ltd. ShapeBlue SA Pty Ltd is a company registered by The Republic of South Africa and is traded under license from Shape Blue Ltd. ShapeBlue is a registered trademark.