Re: [OPEN-ILS-GENERAL] Do you trim database tables?
Martha, Not that I have anything to add but I would like to know what are some good practices also. We also have similar issues where tables are getting quite large. Tim On Thu, Mar 27, 2014 at 9:33 AM, Martha Driscoll drisc...@noblenet.orgwrote: We have been on Evergreen for nearly 2 years. Recently it has been taking significantly longer to load patron records than it used to. We load files just about weekly for our college libraries and the files contain thousands of records. I asked a few people at the conference about trimming tables and found that some delete data out of auditor tables. Our actor_usr_history table is around 14 million rows and asset_copy_history is over 26 million rows. We don't currently age the circulations and the actor.circulation table is up around 67 million rows. I'm wondering what system administrators do periodically to keep the database running efficiently and what the potential trade-offs are. -- Martha Driscoll Systems Manager North of Boston Library Exchange Danvers, Massachusetts www.noblenet.org -- Tim Spindler tjspind...@gmail.com *P** Go Green - **Save a tree! Please don't print this e-mail unless it's really necessary.*
Re: [OPEN-ILS-GENERAL] Do you trim database tables?
Martha, Other than storage, the auditor tables won't impact performance -- they are insert-only in practice. What process are you using for loading patrons? If you're using the staging tables, it may just be a matter of creating some indexes. On Thu, Mar 27, 2014 at 9:40 AM, Rogan Hamby rogan.ha...@yclibrary.net wrote: I've contemplated doing this but have been hesitant for a number of reasons. Moving storage to SSDs eliminated a lot of bottlenecks for us though. On Thu, Mar 27, 2014 at 9:35 AM, Tim Spindler tjspind...@gmail.com wrote: Martha, Not that I have anything to add but I would like to know what are some good practices also. We also have similar issues where tables are getting quite large. Tim On Thu, Mar 27, 2014 at 9:33 AM, Martha Driscoll drisc...@noblenet.org wrote: We have been on Evergreen for nearly 2 years. Recently it has been taking significantly longer to load patron records than it used to. We load files just about weekly for our college libraries and the files contain thousands of records. I asked a few people at the conference about trimming tables and found that some delete data out of auditor tables. Our actor_usr_history table is around 14 million rows and asset_copy_history is over 26 million rows. We don't currently age the circulations and the actor.circulation table is up around 67 million rows. I'm wondering what system administrators do periodically to keep the database running efficiently and what the potential trade-offs are. -- Martha Driscoll Systems Manager North of Boston Library Exchange Danvers, Massachusetts www.noblenet.org -- Tim Spindler tjspind...@gmail.com P Go Green - Save a tree! Please don't print this e-mail unless it's really necessary. -- Rogan Hamby, MLS, CCNP, MIA Managers Headquarters Library and Reference Services, York County Library System You don't have to burn books to destroy a culture. Just get people to stop reading them. -- Ray Bradbury You can never get a cup of tea large enough or a book long enough to suit me. -- C.S. Lewis -- Mike Rylander | Director of Research and Development | Equinox Software, Inc. / Your Library's Guide to Open Source | phone: 1-877-OPEN-ILS (673-6457) | email: mi...@esilibrary.com | web: http://www.esilibrary.com
Re: [OPEN-ILS-GENERAL] Do you trim database tables?
We age circulations after 7 days. Not all circs get aged at that time since patron settings to keep circulation history can affect that, as does the setting to keep previous circulations on copies. (We keep 2.) We do delete data from the auditor tables. We keep 1 year for most tables, but for the user and copy tables we only keep 30 days' worth. As Mike Rylander pointed out, the sizes of the auditor tables don't usually affect performance. They do, however, affect the time it takes to a database dump and restore, which is important if you're copying your database for development or training purposes on a somewhat regular basis. -- Jason Stephenson Assistant Director for Technology Services Merrimack Valley Library Consortium 1600 Osgood ST, Suite 2094 North Andover, MA 01845 Phone: 978-557-5891 Email: jstephen...@mvlc.org
Re: [OPEN-ILS-GENERAL] Do you trim database tables?
I am using staging tables to load the patron records. Once they are in staging, I update the staging records with the actor.usr.id and then run a bunch of update statements for name, address, phone, etc. Those records not already in the database are added as new. This has worked fine but over the last month or two a large file (10,000 records) will cause the db load average to climb. I have started breaking up the files into smaller chunks. I'm not particularly worried about slow loading, but am concerned about the load average spiking. Martha Driscoll Systems Manager North of Boston Library Exchange Danvers, Massachusetts www.noblenet.org On 3/27/2014 9:43 AM, Mike Rylander wrote: Martha, Other than storage, the auditor tables won't impact performance -- they are insert-only in practice. What process are you using for loading patrons? If you're using the staging tables, it may just be a matter of creating some indexes. On Thu, Mar 27, 2014 at 9:40 AM, Rogan Hamby rogan.ha...@yclibrary.net wrote: I've contemplated doing this but have been hesitant for a number of reasons. Moving storage to SSDs eliminated a lot of bottlenecks for us though. On Thu, Mar 27, 2014 at 9:35 AM, Tim Spindler tjspind...@gmail.com wrote: Martha, Not that I have anything to add but I would like to know what are some good practices also. We also have similar issues where tables are getting quite large. Tim On Thu, Mar 27, 2014 at 9:33 AM, Martha Driscoll drisc...@noblenet.org wrote: We have been on Evergreen for nearly 2 years. Recently it has been taking significantly longer to load patron records than it used to. We load files just about weekly for our college libraries and the files contain thousands of records. I asked a few people at the conference about trimming tables and found that some delete data out of auditor tables. Our actor_usr_history table is around 14 million rows and asset_copy_history is over 26 million rows. We don't currently age the circulations and the actor.circulation table is up around 67 million rows. I'm wondering what system administrators do periodically to keep the database running efficiently and what the potential trade-offs are. -- Martha Driscoll Systems Manager North of Boston Library Exchange Danvers, Massachusetts www.noblenet.org -- Tim Spindler tjspind...@gmail.com P Go Green - Save a tree! Please don't print this e-mail unless it's really necessary. -- Rogan Hamby, MLS, CCNP, MIA Managers Headquarters Library and Reference Services, York County Library System You don't have to burn books to destroy a culture. Just get people to stop reading them. -- Ray Bradbury You can never get a cup of tea large enough or a book long enough to suit me. -- C.S. Lewis
Re: [OPEN-ILS-GENERAL] Do you trim database tables?
On 03/27/2014 09:33 AM, Martha Driscoll wrote: We have been on Evergreen for nearly 2 years. Recently it has been taking significantly longer to load patron records than it used to. We load files just about weekly for our college libraries and the files contain thousands of records. I asked a few people at the conference about trimming tables and found that some delete data out of auditor tables. Our actor_usr_history table is around 14 million rows and asset_copy_history is over 26 million rows. We don't currently age the circulations and the actor.circulation table is up around 67 million rows. I'm wondering what system administrators do periodically to keep the database running efficiently and what the potential trade-offs are. We trim tables to the extreme. We only keep auditor data for 6 months and age circulations after 3*. We also remove discarded items and unused bib records semi-irregularly. The pro is, come upgrade time, the DB is lean and processes run quickly. The con, that we occasionally come across the question we can't answer because the data has been discarded. * - we do not age circs for patrons that have requested circ history, we maintain any open transaction, and any transaction involving a bill for 1 year after it's finished.
Re: [OPEN-ILS-GENERAL] Do you trim database tables?
Mike, I haven't checked the quantity or compared it to our entire database size but we probably have a lot deleted bibs and copies in the database just do to normal workflows. Is there ever an advantage in wiping those from the database (not just changing the deleted flag)? I know this would have to be carefully done because removing those entirely could break other things but I could see maybe in another couple years it might be advantageous to remove the oldest ones from completely from the database. Tim On Thu, Mar 27, 2014 at 9:43 AM, Mike Rylander mrylan...@gmail.com wrote: Martha, Other than storage, the auditor tables won't impact performance -- they are insert-only in practice. What process are you using for loading patrons? If you're using the staging tables, it may just be a matter of creating some indexes. On Thu, Mar 27, 2014 at 9:40 AM, Rogan Hamby rogan.ha...@yclibrary.net wrote: I've contemplated doing this but have been hesitant for a number of reasons. Moving storage to SSDs eliminated a lot of bottlenecks for us though. On Thu, Mar 27, 2014 at 9:35 AM, Tim Spindler tjspind...@gmail.com wrote: Martha, Not that I have anything to add but I would like to know what are some good practices also. We also have similar issues where tables are getting quite large. Tim On Thu, Mar 27, 2014 at 9:33 AM, Martha Driscoll drisc...@noblenet.org wrote: We have been on Evergreen for nearly 2 years. Recently it has been taking significantly longer to load patron records than it used to. We load files just about weekly for our college libraries and the files contain thousands of records. I asked a few people at the conference about trimming tables and found that some delete data out of auditor tables. Our actor_usr_history table is around 14 million rows and asset_copy_history is over 26 million rows. We don't currently age the circulations and the actor.circulation table is up around 67 million rows. I'm wondering what system administrators do periodically to keep the database running efficiently and what the potential trade-offs are. -- Martha Driscoll Systems Manager North of Boston Library Exchange Danvers, Massachusetts www.noblenet.org -- Tim Spindler tjspind...@gmail.com P Go Green - Save a tree! Please don't print this e-mail unless it's really necessary. -- Rogan Hamby, MLS, CCNP, MIA Managers Headquarters Library and Reference Services, York County Library System You don't have to burn books to destroy a culture. Just get people to stop reading them. -- Ray Bradbury You can never get a cup of tea large enough or a book long enough to suit me. -- C.S. Lewis -- Mike Rylander | Director of Research and Development | Equinox Software, Inc. / Your Library's Guide to Open Source | phone: 1-877-OPEN-ILS (673-6457) | email: mi...@esilibrary.com | web: http://www.esilibrary.com -- Tim Spindler tjspind...@gmail.com *P** Go Green - **Save a tree! Please don't print this e-mail unless it's really necessary.*
