These are chat archives for collectiveaccess/support

18th
Mar 2016
gotlandsmuseum
@gotlandsmuseum
Mar 18 2016 08:11
Just a quick question on database size. We have a about 60000 posts (growing to around 100000 in the near future) and the database is around 3gb in size when exported from MySQL. The ca_sql_search_word_index table is by far the biggest one with around 30 million rows, is this normal? It is starting to get a bit slow so i´m wondering if there is something I can do to speed things up?
CollectiveAccess
@collectiveaccess
Mar 18 2016 11:10
You can use ElasticSearch instead of MySQL for search
This will allow you to truncate the casql_search* tables, and should provide improved performance
That said, you can get reasonable search performance with larger datasets and MySQL provided MySQL is configured properly.
gotlandsmuseum
@gotlandsmuseum
Mar 18 2016 11:25
We use it in Providence as well?
Guess we have to tweak the MySQL config. When browsing records we often get the message the caresulttmpxxx is full.
gotlandsmuseum
@gotlandsmuseum
Mar 18 2016 11:32
Sorry, the first sentence got lost in the Giiter app... I wrote that we are planning to use ElasticSearch in Pawtucket, didn't realize we could use it i Providence.
Stefan
@stefankeidel
Mar 18 2016 11:37
Yes, you can use ElasticSearch in Providence. 100.000 records is not crazy though, a decent-sized server should be able to handle that. The temporary table thing is an easy fix by the way:
tmp_table_size=1G
max_heap_table_size=1G
This message was deleted
you can play around with the values -- don't have to go to all the way to 1G to start with
gotlandsmuseum
@gotlandsmuseum
Mar 18 2016 11:42
That was my thoughts as well, it is not a giant db. The plas was to use MySql for search until we in a year or two reach +300000 posts including lots of pdfs.
Stefan
@stefankeidel
Mar 18 2016 11:45
There are a few tricks and hacks you can do to get the SQL search to be faster, but If you're using the stock config that came with your install of MySQL, there are a few basic settings you should tweak first
Most importantly innodb_buffer_pool_size ... but there are a few others
gotlandsmuseum
@gotlandsmuseum
Mar 18 2016 11:47
Ok, I will check into that! Thanks for your prompt anwers! Keep up good work :-)