Aggregate tables are provided to boost query performance dramatically when fetching summary data. There's a stored function to maintain this data. It watches for kills added or changed since it last ran and takes care of dealing with DST changes.
It's recommended that you runn the aggregation process once a minute. It will ensure that only a single instance is running so it's fine if multiple attempts are made to run the function, the subsequent ones will exit gracefully if another is already executing.
Here's how to set this up. Edit your crontab and add the following entry, changing the directories to match where your source is and where you'd like the log file to be written into if desired as well as where your config is located - here assumed to be in /etc/pykb.
crontab -e
* * * * * /home/ddrouin/pykb-code/pykb/aggregate.py -l /var/log/pykb/aggregate.log -c /etc/pykb/pykb.yml
Save and exit. Your aggregates will now be maintained automatically. You can also run this at any time manually, if even one is already running from cron. No harm done. Just run this in a sql editor:
~~~~~~ select pykb.aggregate(); ~~~~~~
Status messages will appear letting you know how far it's progress and a response will be emitted.
You can reset the aggregation process so that it'll redo the summarized data from some point in the past forward - including all of the data - just edit this table and change the date to whatever you want.
~~~~~~ update pykb.aggregate_updates set last_ran = '1970-01-01'; ~~~~~~
The above will cause everything to be redone, ensure that you don't have an aggregate function in process though as instead that'll end up overwriting your change.
Before adding kill mails and aggregating them, you'll certainly want to have some prices for item types defined - see [Updating Prices] for instructions on how to set that up.
The initial aggregation run may take a while depending on how many kills you have stored on your database, which database engine you're using, what its settings are and how many CPUs, how much RAM and how fast your disk drives are.
So for example on my rather old dual core computer with 2GB of RAM, I've allocated about 800MB to MySQL and 512MB to PostgreSQL. I have about 170,000 kills in both database. It takes 20 minutes to update the entire set from scratch on PostgreSQL and an hour and a half on MySQL. I'll post my settings here - perhaps things can be improved for MySQL.
Wiki: Home
Wiki: MySQL Database Setup
Wiki: PostgreSQL Database Setup
Wiki: Updating Prices