I have a problem with groups, when sending a new post, the page slow down completely, the server stay processing the page for ~40 seconds, the resource usage of cpu increase at least 150% or more on mysql service for a long time.
The entire site is slow down and sometimes this hang the server and needs to restart the service.

Hi Paulo,

this issue at server side, I suggest you to increase some memory limit at mysql config (my.cnf).
and try using load balancer at NginX:
nginx.org/en/docs/http/load_balancing.html

NginX server is too complex.

Regards,
Albert

That only occurs when submit new message in any group.
The NginX is setting correct, cpu and memory usage stay low.
Only mysql cpu is increase too much while post message.
Are you shure that is only mysql configuration, no index missing? Because the table has too many rows, but why so slow.... What parameter I have to change in my.cnf with you suggest?

Thank you very much, Albertus.

Hi Paulo,

I think the problem only with server performance. we must try to increase some MySQL settings:
max_connections
key_buffer_size
query_cache_size
query_cache_limit

try to double it or triple it first.

Let me know the result.

Regards,
Albert

I increase these values on my.cnf 3x, 4x, 5x, Nx.
Is still slow, very slow.

my.cnf



[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

#key_buffer_size = 4122317824
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
#key_buffer_size = 1332M
#key_buffer = 2024M

key_buffer = 3016M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 40M
query_cache_size = 610M


#query_cache_limit = 20M
#query_cache_size = 480M




#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#expire_logs_days = 10
#max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Hi Paulo,

can I make local copy by using Akeeba Backup? I want debugging it at my NginX local environment. Because I cant replicate this issue by using my test site.

seem this issue complicated. that because NginX have a bunch configurations.

sorry for this inconvenience.

Regards,
Albert

ok Albertus, go ahead.

Thank you.

Hi Paulo,

I will back to you again with the result.

Regards,
Albert

Thank you, Albert.

My best regards.

Hi Paulo,

I already investigate this issue. that because the query for notification is too big because amount of group members (more than 2000 members)

Jomsocial already using multiple insert, so only send one insert request for all members. but the request is too big.

if you go to jomsocial backend > group setting. turn off Enable Notification for New Discussions, the issue will be gone.

Jomsocial still need improvement for big site data/users. I will discuss this with developer, but still not sure how to tackle this issue. simple say, maybe need long time for this. MySQL having some limitation too.

please try other solution:
I saw query_cache_limit = 40M and query_cache_size = 610M, please increase it to 1024M.

maybe need restrart MySQL after that.

Regards,
Albert

Ok Albert, for while, turn off the notification solve my problem.

Thank you.