Housekeeper in large enviroments

[dmcken]

Good day,

I'm currently running an instance with 40k items with 801 vps (1 proxy so far). I'm running a partitioned setup on MySQL on a OCZ Vertex 2 (128GB, an old one I used on my desktop machine for a while). Server and web front end are on a quad core VM (Xen) and the database is on a box with the SSD for both OS and data.

What I want to know is if anyone with a large setup has turned the housekeeper back on? The small size of the SSD has forced this question earlier than I would have liked but my problem is we would like to control which of the items we want data kept for a year vs other items we aren't interested in data older than 2-3 weeks. The partitioning method I use has me deleting the entire partition when I need to free space which leaves me with no control over which data I want to keep vs which is too old to be useful.

Disclamer / anti-flame notes:
1) I am well aware of the unorthodox setup I have, allot of this was dictated by budget / performance concerns.
2) Currently I am generating 2-3GB of history data a day (not including trends, events, logs, etc). We don't have everything put into zabbix yet (we are currently at 1k hosts). Rough calculations expect the number of items to increase at least by a factor of 4-8x (number of hosts should grow to around 10k). No matter what happens increasing hard drive will only work so far.

[kloczek]

Good day,

I'm currently running an instance with 40k items with 801 vps (1 proxy so far). I'm running a partitioned setup on MySQL on a OCZ Vertex 2 (128GB, an old one I used on my desktop machine for a while). Server and web front end are on a quad core VM (Xen) and the database is on a box with the SSD for both OS and data.

What I want to know is if anyone with a large setup has turned the housekeeper back on? The small size of the SSD has forced this question earlier than I would have liked but my problem is we would like to control which of the items we want data kept for a year vs other items we aren't interested in data older than 2-3 weeks. The partitioning method I use has me deleting the entire partition when I need to free space which leaves me with no control over which data I want to keep vs which is too old to be useful.

Disclamer / anti-flame notes:
1) I am well aware of the unorthodox setup I have, allot of this was dictated by budget / performance concerns.
2) Currently I am generating 2-3GB of history data a day (not including trends, events, logs, etc). We don't have everything put into zabbix yet (we are currently at 1k hosts). Rough calculations expect the number of items to increase at least by a factor of 4-8x (number of hosts should grow to around 10k). No matter what happens increasing hard drive will only work so far.

I have similar situation like you .. similar amount of daily data on MySQL as DB engine. In such setup problem is with few tables: events, acknowledges and auditlog. These tables are using foreign keys and cannot be partitioned by this (MySQL limitation) so time to time is necessary to restart zabbix srv with enabled HK to remove oldest data from these tables. At the moment is not possible to specify more than 24h period between HK cycles but if I remember correctly zabbix code switching to weeks or month should be matter of single line code modification.

Yur problem with more disk space can be solved by switching to Solaris with ZFS and use hybrid pool (spindle(s) with l2arc ans small zil cache dev on SSD). In case zabbix DB backend it makes sense because:
* monthly size trends* tables data usually is up to 2-3 times smaller than daily size of the history* so you have very high probability that zfs will cache these data in SSD
* summary size of DB your data will need disk space of spindles (not SSD).

Probably like you I'm running zabbix DB backen on Linux. With limited space on disks I'm going in next couple of weeks to switch everything to Soaris with ZFS. Only because mysql data files with enabled even lzjb compression can gain up to 3 compression ratio (enable row compression cannot give you the same ratio). In my case it will mean that I'll be able stay much longer on the the same HW because effectively with the same physical disk space will be possible to store about 3 times more data.
In your case if you will need even more disk space and you can do this by use bigger spindles. Even 128GB SSDs should enough to organize necessary zil and l2arc devs (2-8GB as zil and rest as l2arc).

Separating web frontend and server by xen is IMO pointless. Overhead added by httpd is relatively small (especially after disable access logs). In your case you are wasting a lot of resources by using Xen. If you really need separation better would be use Solaris zone or Linux container (LXC). SIS virtualization (Single Image System) or partitioning is always much more effective in such cases.

Cost of switching to Solaris is about 600$/year (up to two socket server) if you need support. It should be still way below costs of switching to new HW with few times bigger SSD storage (even in scale of 3-4 years of using the same hardware).

[dmcken]

Thanks for the reply kloczek, I will look into Solaris / ZFS for the database to try to sort out the balance between fast and large storage. I've played with OpenIndiana in the past but I was trying to set it up as an iSCSI target for XEN storage. This should be a much easier / simpler deployment than that (we ended up just getting a HP iSCSI SAN).

I would think running the housekeeper frequently would be a good thing (small amount of data updated each run, which should reduce the load from a housekeeper run). I'm sure given what little I leart about the database when setting up the partitioning I'm quite sure I can delete the old data myself (I've already written a python script that monitors and maintains the partitions) but such a script will also have to be kept updated if there is any updates to the database structure which I would like to avoid if possible.

As for the Xen setup, pretty much all our servers run on XEN, The zabbix database is pretty much one of the few exceptions to this rule while we sort out how to deal with it in the long term. Also the frontend and zabbix server are on the same VM, the database is what is separated to a physical box.