[OPEN-ILS-GENERAL] Do you trim database tables?

[Martha Driscoll]

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.hamby at 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 <tjspindler at 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 <driscoll at 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
>>> tjspindler at 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
>
>
>