ePO MWG and McAfee Content Security Reporter Database size issue
Recently we moved to product with new MWGs and tied the reporting to our ePO via MCSR.
Initially we had the retention size set to 6 months but only after a week we realized that the DB growth would group to nearly 1 TB per month worth of data. We quickly reduced the retention to 1 month, but the DB will end up being around 600 - 800 GB with the main search (csr_fct_exact_access) table having around 1 billion records.
This means that when reports are run against say a user or IP address for one month of data, causes the CPU and Memory consumption on the DB server (separate from the ePO and MCSR servers) to spike and stay pegged until the report finishes or my SQL engineer kills the query. In testing we have found that the GUI will time out long before the report completes. Setting the report to run on a schedule to send an email or store to ePO server seem to take up to 48 or more hours to complete.
What are others doing to deal with these crazy huge Databases and table sizes? I have been working with Support and they are at a loss what to do other than tell me that they don't do SQL.
I hear where you're coming from. For a comparison, with 60 days (and change) of data, our database is 5.7 TB. Our csr_fct_web (Web Summary) table is 2.8 billion rows, and the exact_access is about 9.4 billino rows.
We had issues with this environment when it was about 1/3-1/2 the size it is now. We were running on a MS SQL server database in a VM and a dedicated VM CSR Report Server. The DB server was 16 cpu/132 GB RAM server, and the report server was 24 CPU/32 GB RAM server. In addition to CPU issues, we noticed that if two "big" queries were executed at the same time, CSR would just stop inserting data into the table and you'd have to wait. Between that, and the required maintenance, we were always 2-4 hrs behind in processing data.
Our solution was to move to AWS and mySQL. We created a ePO/CSR server that's 8x32 and and RDS mySQL server that's 16x128. We've never looked back. To say we hammer this environment is an understatement. We can run multiple reports. we can stay caught pretty much all the time. There's gotchas with mySQL and empty partitions that you need to be mindful of, but it's a great and works for us. I'd also put both the ec2 and rds in the same AZ to eliminate small networking gotchas.
If you can't use a cloud provider, perhaps looking at dedicated hardware and mySQL if you can. I think that's the trick. McAfee sizing guides aren't great, but I'd start with 16 CPU and 128 GB of RAM to start with on the SQL server. The processing server relies more on CPU as the logs are processed (typically 80-90%), so size that as required.
Hope this helps. You're not the only one feeling the pain of large CSR databases
Don't forget, when your helpful posts earn a kudos or get accepted as a solution you can unlock perks and badges. Those aren't the only badges, either. How many can you collect? Click here to learn more.
Community Help Hub
New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.