sugarcrm application issue due to mysql performance

Dear Experts

We are having SugarCRM enterprise with good hardware very frequently users are getting httpd 500 server error this happens to all users at the same time, once restart the mysql server then works for some time like 30 mins to one hour approx and again the same issue httpd 500 server error just before this occurs very slow experience and then finally ends up with httpd 500 server error,  some times if users reload it works some times I have to restart the mysql service, I am running mysql 5.6.
my.cnf below posted
/etc/opt/rh/rh-mysql56/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#
#
#[mysqld]
max_connections=5000
# include all files from the config directory
#
!includedir /etc/opt/rh/rh-mysql56/my.cnf.d
-----------------------------------------------------------------------------------------------end--------------
just added max_connections=5000 as mentioned above but not sure this will resolve can you please suggest best practice performance steps setting up for mysql server , thanks in advance
D_wathiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

D_wathiAuthor Commented:
logged in through phpmyadmin and navigated to STATUS and under this advisor section and found the list of comments attached, can you please suggest which are the most critical in the attached to be done immediately and how and where this to be done, this will be a great help. thank you.
Possible-performance-issues.docx
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
The key here is to know what's wrong, rather than guessing.

To know, means enabling enough logging + using correct tools to determine the problem.

Here's a snippet from one of my client's my.cnf file, which sets up log files.

skip-external-locking
performance_schema = ON

# Logging

log_error           = /var/log/mysql/mariadb-error.log
log_warnings        = 2

general_log_file    = /var/log/mysql/mariadb-general.log
general_log         = OFF

slow_query_log_file = /var/log/mysql/mariadb-slow.log
slow_query_log      = ON
long_query_time     = 10
log_slow_rate_limit = 1
log_slow_verbosity  = query_plan,innodb,explain
log_slow_admin_statements

Open in new window


I also only use MariaDB, rather than MySQL. Even Debian has switched to defaulting to MariaDB, because MariaDB runs faster/better (more consistently) than MySQL.

You'll have to change the above to match your version of MySQL, likely changing log_slow_verbosity value or just commenting it out to take your version's default.

That said, I've never seen a MySQL problem cause a 500 Apache error.

Normally if you have database problems, you'll see Webpages with Database connection error or similar + these pages will still return a 200 at the Apache level.

500s usually occur when PHP throws/raises Fatal exceptions + SugarCRM is written in PHP.

Restarting your Apache stack likely fixes some oddball PHP error temporarily or you have Apache setup to have all threads persistent, rather than recycling them (killing them off after some number of requests are processed).

Configure your Apache + PHP logging to log enough detail to find + fix the problem.

Sigh... Setting up this type of debug config can be complex, unless you do it every day.

You might be better served to hire someone to setup logging for you + debug this one problem + show you how they did their debugging, so you can do it in the future yourself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
One last database item. Be sure you do use mysqltuner on a regular basis + clear up all diagnostics it emits.

Also make sure you storage engines + indexes are all setup correctly.

I only use InnoDB, as MyISAM has to many challenges to use in almost any application.
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Marking my debug setup as best solution.

Poster seems to have stopped posting.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SugarCRM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.