performance tuning mysql server

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?

it has 375 tables of application and database size 55GB. i did not check the active users during this time,

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.

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

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.

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.

Thank you very much sure will check it and also will monitor the performance behaviour today and will update. thanks once again.

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

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.

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.

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?

simultaneous requests are 75 users and it is private server .

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?

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,

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.

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

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.