MySQL configuration/settings for optimum use with OpenKM

[Alexires]

Hi all,

Migrated OpenKM over to MySQL with no problems a while back, and I've recently moved to a new server (Hetzner Ex40, running i7-4770, 32Gb RAM, 4TB SATA HDD). I've configured OpenKM via ./setenv.sh to use 20Gb ram (-Xmx20g) and I've also configured it to use parallel garbage collection (-XX:+UseParallelGC) because of some "OutOfMemory: Heap Space" problems I was getting.

MySQL has been configured to use around 10-Gb ram, since the database for OpenKM is 9Gb in my install. My question is this: what kind of settings should I be using in my MySQL install? I'm only running OpenKM, so I don't need to worry about other programs or memory hogs. mysqltuner is reporting the following issues:

Total fragmented tables: 107
Query cache efficiency: 0.0% (1K cached / 2M selects)
Query cache prunes per date: 194724140
Temporary tables created on disk: 49% (10K on disk / 20K total)
Thread cache hit rate: 41% (14 created / 24 connections)
Connections aborted: 20%

My query_cache_limit is already at 2G, query_cache_size is 128M and thread_cache_size is 8. I'm pretty new with MySQL, so I'm not entirely sure what to alter or even what reasonable settings are. Any ideas or support from either the guys at OpenKM or just general MySQL knowledgey people?

For what it may be worth, the repository is approximately 500Gb in size, containing just under a million files.

[jllort]

About JVM you can increate these default values at sentenv.sh to get higger heap (max 512 seems enought )

Code: Select all

-XX:PermSize=128m -XX:MaxPermSize=256m

Depending the number of concurrent users and what doing is better more memory in tomcat or mysql. About optimizing mysql I suggest go to mysql forum than OpenKM. For a huge repository - which I must remember OpenKM community version is not optimized for it - is always good idea cache queries and values in memory that makes good look & feel to end user.

Note: I will repeat OpenKM community version is not optimized for this huge of files, that not means you can not get it running. Professional version comes with controlled program cache system oriented to solve large size repositories. Obviously some optimizations can be done with memory usage and mysql cache.

Finally as a suggestion. Typical problems in large repositories ( prevent ):
- security change can take several minutes or hours depending the deep of the folders and files you're changing recursivelly. I suggest when you import data try to set security ( if it's possible ) and not after uploaded ( specially if we're talking about 1 milion files ).
- other operation which can be problematic is purge trash which can take several minutes or hours depending how much files you delete ( take in mind here generates log and removes files from file system ).
- control activity log table grown

[Alexires]

Ended up purchasing OpenKM Professional...