performance tuning mysql server

Dear Experts

it is observed the  crm application running on LAMP stack often crashes, users unable to use the application webserver is on one server and database mysql 5.5 is on another server did perform the mysql tuner and attached is report, the system has 64G RAM as per the recommendations from mysql tuner can I set my.cnf as per the following, please suggest
total physcial ram capacity is 64G
based on the mysql tuner recommendations should I have to to set as per the below
innodb_buffer_pool_size = 55.2G
innodb_log_file_size= 25% of above i,e 55.2 which is 13.75G
please suggest can I change in my.cnf as above.
mysql-tunerlog.txt
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.

Martyn SpencerManaging DirectorCommented:
I would be a little wary of increasing the RAM that mysql uses to such a high value with that amount of RAM in the server unless the server was only running mysql and I had verified that I was not starving the OS of RAM that it could be using. This is one situation where making a small change, measuring performance and then revising based on observed metrics would be better than just following the recommendation of an automated tuning tool. One of the comments in the log indicates (to me) that you may be starving other processes of RAM and causing them to page. This could have a detrimental effect on the server generally.

It's important to note that an optimisation tool often needs a good amount of runtime to make good recommendations and since your instance has not been up for long, mysqltuner is warning you of this.

Out of interest, what is the actual size of the database tables and how many users are active on the database when you see the issues?
1
D_wathiAuthor Commented:
it has 375 tables of application and database size 55GB. i did not check the active users during this time,
0
Martyn SpencerManaging DirectorCommented:
OK. Naturally if the server were to cache the entire database, read performance would be better than reading off-disk. But, you have a trade-off here. Sometimes, it is not beneficial to starve the OS of RAM at the expense of one service. This is particularly true if your server performs other functions. So, for example if PHP, Apache and a few other services are running, you potentially cause problems with those services if you allocate too much memory to mysql.

In addition to the above, do the usage patterns on your database really show that the entire database is read regularly? What I mean by this is that attempting to cache the entire database introduces an overhead in of itself that may just not be necessary. If for example a lot of the tables are historical in nature, a huge buffer pool may be underused and you end up wasting RAM. Have a read of https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html for more details.

Tuning a database is more complex than most people imagine and it requires a good understanding of how the database functions. For me the rule has always been small changes, measure, and revise as necessary. Give the system time to settle with changes that you make.

I would venture to suggest that some of the problems you have eluded to in other questions may not be simply down to performance tuning.

With regard to the log file size, have a read of this: https://www.percona.com/blog/2017/10/18/chose-mysql-innodb_log_file_size/

The balance you need to strike there is to allow the database a good chance to optimise writes whilst also ensuring that database recovery is not excessively slow. Since your database appears to write far less than it reads, looking to improve read performance would make sense, to me.

Edit: Added a link I referred to.
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
To expand on Martyn's comment about memory.

If you start increasing MySQL memory usage, you change starving memory for other processes.

If swapping begins, then likely your machine will eventually slow to a crawl or crash.

Simple Fix: Start with 128G-256G memory, as memory is cheap.

If you can't add more memory, switch over to MariaDB (which may resolve the entire problem + likely not).

Once you've switched, analyze your tables + set storage engines as follows...

1) InnoDB - heavy read + normal read/write access.

2) MyRocks - heavy write access.

This will likely fix your immediate problem... and... if your database is growing, this is only a temporary fix + you'll eventually adding more memory will be required.
0
D_wathiAuthor Commented:
thank you very much for the inputs, moved the old log file i,e /var/log/mysqld.log to different name
then set innodb_log_file_size=1G to 8GB which is of 25% of innodb_buffer_pool_size=32G but after this when restart mysql it fails with the following error

180913 03:18:26 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180913 03:19:48 mysqld_safe Starting mysqld daemon with databases from /ext1/mysql
180913  3:19:48 [Warning] option 'read_rnd_buffer_size': unsigned value 2147483648 adjusted to 2147483647
180913  3:19:48 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
180913  3:19:48 [Warning] option 'thread_cache_size': unsigned value 32768 adjusted to 16384
180913  3:19:48 [Note] Plugin 'FEDERATED' is disabled.
180913  3:19:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180913  3:19:48 InnoDB: Compressed tables use zlib 1.2.3
180913  3:19:48 InnoDB: Using Linux native AIO
180913  3:19:48 InnoDB: Initializing buffer pool, size = 32.0G
180913  3:19:50 InnoDB: Completed initialization of buffer pool
180913  3:19:51 InnoDB: Error: combined size of log files must be < 4 GB
180913  3:19:51 Plugin 'InnoDB' init function returned error.
180913  3:19:51 Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180913  3:19:51 Unknown/unsupported storage engine: InnoDB
180913  3:19:51 Aborting

180913  3:19:51 [Note] /usr/libexec/mysqld: Shutdown complete
0
D_wathiAuthor Commented:
in contd to previous post
set innodb_buffer_pool_size=51G  # 80% of physical memory
innodb_log_file_size=10G
mysql did not start it failed, only changed log file to 1GB then mysql started. hence finally only change I could make is setting up innodb_buffer_pool_size to 51G which is 80% of physical RAM. let me monitor the performance now, hope we can set 80% of physical RAM to innodb_buffer_ppol size, if this found to be incorrect please suggest. thank you.
0
Martyn SpencerManaging DirectorCommented:
The failure would have been, as suggested in my other answers, due to a change in log file size. If you change the size, the log files need to be recreated. I doubt you need log files that big anyway, unless your server is writing huge quantities of data in large transactions.

I omitted a link from my response above. Please take a look as it helps explain choosing log file sizes.
0
D_wathiAuthor Commented:
Thank you very much sure will check it and also will monitor the performance behaviour today and will update. thanks once again.
0
D_wathiAuthor Commented:
again the web application went down later it automatically restored , when checked log can see the following
kernel: Out of memory: Kill process 249606 (httpd) score 32 or sacrifice child
Sep 13 13:19:27 MYSWB kernel: Killed process 249606, UID 48, (httpd) total-vm:2826972kB, anon-rss:1334424kB, file-rss:2044kB
0
Martyn SpencerManaging DirectorCommented:
I am going to suggest that this is because you are allocating far too much server RAM to mysql. Also, consider how much RAM you are allocating to apache. Basically, the kernel is killing off processes and in this case the http daemon. Reduce the mysql memory footprint, or increase the server RAM. Try reducing it by 25% initially and monitor.
0
D_wathiAuthor Commented:
thank you very much, the webserver where this issue that is kernel out of memory and killing httpd process does not have local data base instead this server uses another server for database. this system has 32G ram how do I prioritise maximum RAM for httpd, can you please help where can the value of ram set for this.
0
Martyn SpencerManaging DirectorCommented:
Apache servers can be quite frugal and it's hard to recommend without knowing the sort of usage the server is being put to. What I will say is that I have servers running with 2GB RAM or less that service quite high traffic sites (from a small business perspective). Is this a private web server for your in-house system or is it public-facing? How many simultaneous requests is the server generally receiving at peak times? Assuming it is a private server, how many users are generally active on this server at any one time?
0
D_wathiAuthor Commented:
simultaneous requests are 75 users and it is private server .
0
Martyn SpencerManaging DirectorCommented:
OK, 75 users. How many simultaneous Apache processes do you see running on average? My gut feeling is that you are going to be able to get away with far less RAM than you have allocated to Apache. What led you to allocate such a large chunk in the first place? Was it a result of incrementally increasing it or did you just decide upon an arbitrary figure?
0
D_wathiAuthor Commented:
thanks for the reply, initially until the issue started the server was with 16G RAM and later we increased to 32G, I see between 20 to 15 simultaneous processes running,
0
Martyn SpencerManaging DirectorCommented:
OK. I really would try a lower RAM value than you have. Irrespective of the amount of RAM that you had, if you starve the OS of memory, it is going to start terminating processes.

This article gives a few tuning hints: https://www.tecmint.com/apache-performance-tuning/ and it specifically mentions memory and Multi-Processing Module (MPM) options, which have an effect on memory. This may allow you to calculate a base size and for the number of concurrent sessions you have something tells me it will be much less than you are allocating.
0
D_wathiAuthor Commented:
thank you very much for the inputs, sure will refer to the link that is provided, as of now I have set  prefork MPM settings as following
<IfModule prefork.c>
StartServers       4
MinSpareServers    20
MaxSpareServers    40
# ServerLimit      256
MaxClients        200
MaxRequestsPerChild  4500
0
Martyn SpencerManaging DirectorCommented:
If it's possible, you may want to add a reference to Apache in your question title, since we have now gone from talking about mysql to Apache. Ideally, this should have been a different question.
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
Linux

From novice to tech pro — start learning today.