[Koha] Koha instance with over 86 000 item records is been slow

[Paul]

At 08:43 PM 10/22/2013 +0200, amutsikiwa at uzlib.uz.ac.zw wrote:
>Hi
>I have a  Koha instance with over 86 000  item records and is reported to
>getting somewhat slower  especially in the circulation function. It is
>running on a  server with 2.7 GHz processor and 2G RAM and running on
>Ubuntu. Is there a way of optimizing Mysql database?

CAUTION: backup your mysql db

Have you ever tried cleanup_database.pl ? (it can be found in 
/usr/share/koha/bin/cronjobs/)

We regularly use (manually, not as a cron) --sessions, --zebraqueue, 
--mail, --logs and --import. We use quite a short time span (often as low 
as one day) and immediately do a complete re-index of zebra (biblios and 
authorities.)  The savings are quite impressive; however I would point out 
that we are a "reference" not a "lending" library so have no experience 
whether patron records would be affected. We have never used --merged nor 
--searchhistory.

Usage: [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] 
[--zebraqueue DAYS] [-m|--mail] [--merged] [--import DAYS] [--logs DAYS] 
[--searchhistory DAYS]

    -h --help          prints this help message, and exits, ignoring all
                       other options
    --sessions         purge the sessions table.  If you use this while users
                       are logged into Koha, they will have to reconnect.
    --sessdays DAYS    purge only sessions older than DAYS days.
    -v --verbose       will cause the script to give you a bit more information
                       about the run.
    --zebraqueue DAYS  purge completed zebraqueue entries older than DAYS days.
                       Defaults to 30 days if no days specified.
    -m --mail DAYS     purge items from the mail queue that are older than 
DAYS days.
                       Defaults to 30 days if no days specified.
    --merged           purged completed entries from need_merge_authorities.
    --import DAYS      purge records from import tables older than DAYS days.
                       Defaults to 60 days if no days specified.
    --logs DAYS        purge entries from action_logs older than DAYS days.
                       Defaults to 180 days if no days specified.
    --searchhistory DAYS  purge entries from search_history older than DAYS 
days.
                          Defaults to 30 days if no days specified

Good luck - Paul