Hi to all, to mantain a good Koha system is import to use the cron job 'cleanup_database.pl'. This script delete lines from tables message_queue need_merge_authorities sessions zebraqueue The engine of those tables is InnoDB. What do you think about change the engine to MYISAM ? I think that MYISAM is a better engine for table with many write/delete. For thi is an ehancement and I don't insert it in updatedatabase.pl. This a change only for new installations. Or to do by hand for update. Bye -- Dott. Zeno Tajoli tajoliAT_SPAM_no_prendiATcilea.it fax +39 02 2135520 CILEA - Consorzio Interuniversitario http://www.cilea.it/disclaimer
On Tue, Jan 17, 2012 at 03:19:13PM +0100, Zeno Tajoli wrote:
Hi to all,
to mantain a good Koha system is import to use the cron job 'cleanup_database.pl'.
This script delete lines from tables
message_queue need_merge_authorities sessions zebraqueue
The engine of those tables is InnoDB.
What do you think about change the engine to MYISAM ? I think that MYISAM is a better engine for table with many write/delete.
MyISAM locks the table rather than the row for update/insert so its generally less optimal for tables which are getting lots of inserts. I did some benchmarking on an application that was using some mysql tables for high throughput logging and innodb was handling that workload better. C. -- Colin Campbell Chief Software Engineer, PTFS Europe Limited Content Management and Library Solutions +44 (0) 800 756 6803 (phone) +44 (0) 7759 633626 (mobile) colin.campbell@ptfs-europe.com skype: colin_campbell2 http://www.ptfs-europe.com
Le 17/01/2012 15:19, Zeno Tajoli a écrit : > Hi to all, > > to mantain a good Koha system is import to use the cron job > 'cleanup_database.pl'. > > This script delete lines from tables > > message_queue > need_merge_authorities > sessions > zebraqueue I'm for it: * innoDB has a *big* problem with deletion, and space is not retrived. so those tables, that have temporary data only are good candidates to be myISAM * those tables don't have any foreign constraint, that myISAM don't manage, so it's OK * those tables are very small, fast to write, so the lock table issue that Colin rises is not a big deal. We have this on all our customers, without any performance issue. (not sure for merge_authorities, but sure for sessions & zebraqueue) -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
Hi, On 01/17/2012 11:54 AM, Paul Poulain wrote:
* innoDB has a *big* problem with deletion, and space is not retrived. so those tables, that have temporary data only are good candidates to be myISAM
Although as long as one uses the innodb_file_per_table option, recovering space from InnoDB scratch tables is easy.
* those tables don't have any foreign constraint, that myISAM don't manage, so it's OK
message_queue does have constraints, actually, and justifiably so if one uses it as a log of notifications sent to the patron.
* those tables are very small, fast to write, so the lock table issue that Colin rises is not a big deal.
Well, depends on scale. I could imagine very large Koha sites that retain message history having large message_queue tables.
We have this on all our customers, without any performance issue. (not sure for merge_authorities, but sure for sessions& zebraqueue)
Faster yet, of course, is to not store sessions in MySQL at all, and use memcached. Of course, the tradeoff is a little more work if one wishes to run reports on OPAC sessions. All of that said, MyISAM is a reasonable choice for a default storage enginge for sessions, zebraqueue, and need_merge_authorities. Regards, Galen -- Galen Charlton Director of Support and Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
participants (4)
-
Colin Campbell -
Galen Charlton -
Paul Poulain -
Zeno Tajoli