Bad performance and Slow LDAP response

[vielktus]

Hi there,

I installed OpenKM Community Ver. Using AJP to connect Apache and Tomcat. I get HTTPs for Apache.

The problem is that i get bad performance while my repository is not large (about < 100MB, 50 folders). What can i do to improve this?

By the way, im using LDAPs authen. Sometimes its able to authen but sometimes it get internal error after timeout about 30 sec. The browser tops at spring_security_check.jsp page. I added my LDAP server into /etc/hosts but no luck.

Thanks for any helps.

[pavila]

I can't understant the relation between LDAP and slow repository. With so little documents and folders it your work very fast. Take a look to the server load and any possible network problem.

Anyway I don't have idea about which OpenKM version are you using nor the server hardware configuration and operating system.

[vielktus]

I can't understant the relation between LDAP and slow repository. With so little documents and folders it your work very fast. Take a look to the server load and any possible network problem.

Anyway I don't have idea about which OpenKM version are you using nor the server hardware configuration and operating system.

Ah my bad. The info is:
Software
OpenKM Community 6.2.4
LDAPs with Windows AD, using advanced config on the wiki.openkm
AJP connect for Apache and Tomcat
Web Traffic using HTTPs
Database changed to MySQL
CentOS 6.3 64bit

Hardware
VMWare VSphere 5.0
4 vCPU
8GB Ram (using 2GB, free 6GB)
HDD 240GB --> OpenKM is in the seperated partition

I mean i get the 2 problems: LDAP slow and bad performance. I didnt mean those 2 related each other (or even if they have i dont know )

I see that when i restart Tomcat, LDAP gets better. Im not sure. But i cant restart it frequently i need the stability

Is there anyway to get better performance ? I checked the Hardware in VMWare, everything is normal.

My boss is complaining about this performance, i think this tool is great. if i can get its performance better, we will consider upgrade to Pro version

[jllort]

4CPU and 8GB ram is enought to get good performance. Do you have a lot of users in your ldap ? I suggest swich to database mode and try with users from database to discard ldap problem.There's a few differences between ldap in community and professional version. In professional there's a caching system which allows fast speed and less agresive connection to get data from ldap which not comes in community. If you got a large number of users which are listed in openkm from ldap that can be a problem, because each time users login the list is refreshed ( not cached like professional version ). To discard this is your problem swhich to DBMS authentication for a while.

[vielktus]

Yup, i have about 2000 users in our AD.

But, i filtered it. Now in the LDAP users database, only users in my Department are listed (16 users). I checked this by using Administration Tab-->User.

I cant change user database mode I must use the AD to authen, no way else

Okay, can we talk about the performance ? is there any way to boost the loading (loading taxonomy, mail, trash...) ? I know im using the Community version, but my repository is just small. It shouldnt slow like that, right ?
Particularly, when i clicked in a folder (15 word files in there), the loading icon showed up and spinned for about 6 sec. Is that OK ?

[jllort]

16 ldap users are few, should be fast getting it, the problem not comes from there.

About 6 seconds to change folder is so much time 1-3 will be more normal. Which browser and hardware are you using, take in mind some speed depends directly with computer and browser you're using ( I recommend firefox or chrome, basically I do not recommend IE )

[noxious]

I hope you are not using MySQL with default configuration...If true, you have to tweak your MySQL server.

[jllort]

Hi noxious, if you want to share with forum your expertice on configuring mysql this is wellcome. We can add specific wiki section for mysql perfomance.

[noxious]

Using MySQL server InnoDB engine with the default configuration parameters in my.ini file can cause a serious performance bottleneck in applications with high database read write activity. MySQL should have enough RAM to be able to cache the repository and avoid the high Disk I/O activity.
The most important parameters are: innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_file_size and innodb_log_buffer_size.
According to database experts, innodb_buffer_pool_size should occupy the 70 percent of RAM of a dedicated database server!!!

But, before changing these values, please read MySQL documentation carefully. Before making any changes you should make sure that mysql server has been stopped correctly (check server log), otherwise the innodb will become corrupted and mysql server won't restart...

Myself, personally, by changing these values I experienced an impressive performance boost in OpenKM.

Here are some usefull links:
http://dev.mysql.com/doc/refman/5.0/en/ ... uning.html
http://dba.stackexchange.com/questions/ ... b-logfile0

[jllort]

you got mysql on dedicated server or shared with openkm ( I ask it about your indications 70% of available server ram ).

If you can share your hardware configuration and my.ini changes will be great I will share this information in our wiki as example of MySQL tunning

[noxious]

I don't have a dedicated database server, jboss and mysql are in the same machine, so I am not following the 70 percent policy.
Anyway, I am using a virtualized Windows Server 2008 with this configuration:
CPU: Intel Xeon X3470 @2,93GHz
RAM: 7GB
JVM settings for JBoss: -Xms 3072 -Xmx 3072 -XX:PermSize=256m -XX:MaxPermSize=512m
my.ini settings for mysql:

Code: Select all

innodb_buffer_pool_size=1792M
innodb_additional_mem_pool_size=16M
innodb_log_file_size=256M
innodb_log_buffer_size=8M

My repository is 1GB so 1792M is enough RAM for innodb_buffer_pool_size.
The larger your repository is the more memory you need to allocate to innodb_buffer_pool_size.
If you are using x86 mysql server you cannot allocate RAM larger than 2GB.

[pavila]

Thanks for sharing!

[jllort]

I've shared this information in our wiki documentation at http://wiki.openkm.com/index.php/MySQL_Tunning

Only one question, when you refer to repository size, you're talking about OpenKM repository size is 1GB or database is 1GB ?

Thanks for your support

[Alexires]

jllort - He is referring to the database size, if I'm not mistaken.

When I was running Community, my database was 9gig, while the repository itself was about 600Gb. Having InnoDB data size larger than your repository cuts down MASSIVELY on I/O operations.

For users who aren't sure how big it is, you can get a program on Ubuntu (and I'm assuming other linux distros as well) called "MySQLTuner"

Code: Select all

sudo aptitude install mysqltuner

Running it will have the tool as you for the username/password of the MySQL admin, and then will give an output similar to the following:

Code: Select all

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-0ubuntu0.12.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 1G (Tables: 111)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 111

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 16h 54m 50s (48M q [328.002 qps], 62 conn, TX: 6B, RX: 7B)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 11.1G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 11.5G (36% of installed RAM)
[OK] Slow queries: 0% (0/48M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
[!!] Key buffer hit rate: 93.9% (47M cached / 2M reads)
[!!] Query cache efficiency: 7.8% (2M cached / 32M selects)
[!!] Query cache prunes per day: 3980839
[OK] Sorts requiring temporary tables: 0% (189 temp sorts / 946K sorts)
[!!] Temporary tables created on disk: 49% (22K on disk / 44K total)
[OK] Thread cache hit rate: 79% (13 created / 62 connections)
[OK] Table cache hit rate: 27% (176 open / 639 opened)
[OK] Open file limit used: 4% (48/1K)
[OK] Table locks acquired immediately: 100% (20M immediate / 20M locks)
[OK] InnoDB data size / buffer pool: 1.1G/11.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    query_cache_size (> 16M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)

As you can see, it tells you your current DB data size as the last entry before "Recommendations", which can help diagnose if your MySQL has enough size; if it doesn't, then you'll get a suggestion like the ones I have.

Edit: I also want to ask a question here, since we are talking about MySQL settings: is there much use in having a large query cache?

[noxious]

My repository size is 1GB, not the database. I haven't checked the database size yet but it's generally small. What I was trying to say is that if you have a really large repository, the configuration I proposed may not be sufficient. However, even with a small repository like mine, changing the mysql configuration parameters offered me an observable performance boost.
As far as it concerns the theory behind query caching, this is beneficial when an application executes multiple times some high disk I/O consuming queries in a relatively short time interval. If these queries refer to data that do not change very often, then caching the query results will improve the app's performance. However, if you cache queries that refer to tables with continuously changing content, then you are in danger of sending stale data to your clients. This is the main concept of query caching. MySQL has a query cache which is by default disabled. If you enable query cache, you have to set it's size and then mysql decides what queries to cache, depending on the database activity.
The most interesting thing is that the hibernate framework supports second level - query level caching where you can explicitely cache the queries you desire.
MySQL Query Cache:
http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
Hibernate Caching:
http://www.javalobby.org/java/forums/t48846.html

p.s. I am not aware of which is the best practise in setting the query cache size in mysql. dev.mysql.com says:

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default.
Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache.

If someone else knows something more please share...