Thank you all the way once again.
my.cnf.txt (1.83 KB)
Thanks for your root password.
You could run [url]MySQL Tools and Management Software to Perform System Tasks by Percona and check for differences.
Hello gmouse,
Very true, i did not hash the password as that is a private server and not facing the internet.
Secondly, Yes, i am aware of the link you have given, but ist slightly intimidating and not quite what the admins on board prefer, my apologies for that.
That is the reason, have posted the full configuration file for any further changes that can be done to optimize the performance further.
Thank you once again.
You already have the few “easy win” configuration settings for increasing performance where possible:
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
sync_binlog=0
innodb_buffer_pool_size = 25G
Keep in mind that the innodb_flush_log_at_trx_commit = 0 setting makes you non-ACID compliant. This may not be an issue with your client's application, but data loss is possible if MySQL crashes.
That aside, have you done the query optimization that we talked about previously? A large number of unoptimized queries will kill any server, so that is still the place to start.
Have you checked to see if you are running out of memory and swapping to disk? With a 25GB buffer pool (out of 32GB), and 2GB temp tables, you will easily blow through the free memory with unoptimized queries that are likely throwing out tons of temp tables.
Also have you done any tests with disabling your query cache? Often applications perform better with it off (but not always), so it is something worth checking out. The query cache can be a point of contention, which can back up the server, which can then cause heavy load.
All of these things highly depend on your specific setup, so make sure you thoroughly test and monitor and changes you make.
Hello Scott,
Thank you for your revert as always 
So, do you suggest i change "innodb_flush_log_at_trx_commit = 1 " instead of “zero” ?
Secondly, with regards to query optimization, It is my boss who works on the Db server and says, they are optimized
Thirdly, when i run " free -m" during business or non business hours, I do see the server running out of memory, What do you suggest i change these values to ? ( innodb_buffer_pool_size & and ‘temp tables’ )
[B][root@Newdbsrv ~]# free -m[/B]
[B] total used free shared buffers cached[/B]
[B]Mem: 32059 31510 549 0 18 15[/B]
[B]-/+ buffers/cache: 31476 583[/B]
[B]Swap: 52099 262 51837[/B]
Nope, I have never ever disabled “query cache”, I think I will do this when i have no further option these days the server is performing quite good.
Thank you once again for your time and awaiting your suggestions.
Hello Scott,
I was going through an article on the Internet,
[URL=“mysql - Is it safe to use innodb_flush_log_at_trx_commit = 2 - Database Administrators Stack Exchange”/URL]
It also states that i change, both these values to 1
[mysqld]
innodb_flush_log_at_trx_commit=1
sync_binlog=1
Now, My query is, will it add any kind of load CPU or RAM ?
Also, Please clarify my doubts on “innodb_buffer_pool_size”.
Thank you
Your choice for innodb_flush_log_at_trx_commit is up to the business that owns the database. If they want full ACID compliance, then it needs to be set to 1, but performance will likely decrease. If the business is okay with the risk of potentially losing around 1 second worth of data, then you may use a setting of 0 or 2. 0 is the most risky, as data loss can occur if MySQL itself crashes. 2 is less risky, as it requires an OS crash to potentially lose data.
Tuning the temp table size is a little more involved. What you would need to do is modify the settings (tmp_table_size and max_heap_table_size), and then monitor the temp table related status variables to see if the lower size value is causing more tables to be written to disk or not. If you see no increase in disk temp tables, then you should be good to go (run "show global status like “%tmp%” and review Created_tmp_disk_tables and Created_tmp_tables).
Your innodb_buffer_pool_size is about 80% of your total memory, which is standard for a dedicated database server. If you think you are in fact running out of memory, you could try decreasing it by 1G and see if it helps any.
I would definitely do some tests with the query cache. Unless there is data showing that performance is better with the query cache on, it is often better to turn it off (not always, but often enough to make sure to test it out).
Another thing to look into is the transaction isolation level. I do not see it in your config file, so you are likely using the default value of REPEATABLE-READ, which is restrictive. This goes back to the ACID compliance issue though. If your client’s application will handle it, and if the company does not need full ACID compliance, then you may want to test an isolation level of READ-COMMITTED. This setting works great when you have innodb_flush_log_at_trx_commit set to 0 or 2. But be warned that not all applications will work correctly with a different isolation level, so do not change this until you confirm with the developers that the application will handle it and with the business in regards to the ACID compliance issue.
Keep in mind that all of these things may have a drastic effect on the database for better or worse, and that you need to be very careful with what you change and be sure to monitor the results after the fact. What works for one database may not work for another, so what you choose to do needs to be up to you and what you feel comfortable with (i.e. don’t just do what I or anyone else suggests without really looking into it). =)
Scott,
Firstly, accept my ‘Thank’s’ for taking out time and explaining the intricacies of ‘Percona’ in such great depth, I am highly indebted.
With regards to first point ‘innodb_flush_log_at_trx_commit’, i did a bit of goggling and found that, Yes it also helps in maintaining data consistency when we have ‘replication’ setup in our business environment, Hence i am going to have these 2 variables set to one.
innodb_flush_log_at_trx_commit=1
sync_binlog=1
With regards to “temp table size” & “query cache”, I am not quite confident how can i go about editing it, Can you please point me to some URL which can assist me doing the changes.
With regards to “innodb_buffer_pool_size”, I’ll touch this later , may be in the end as a final touch.
Once again, Thank you, Thank you, Thank you sooo much
Glad you are making progress! Yes those two settings you changed will give you the safest setup, so if that is what the client is interested in then that is the way to go. =)
Testing a different temp table size would be as follows:
-
Run "show global status like “%tmp%” and record the Created_tmp_disk_tables and Created_tmp_tables values for a starting point (or FLUSH STATUS if you want to reset everything back to zero).
-
Modify the two temp table related variables (example here is setting them to 1GB):
set global tmp_table_size = 1073741824;
set global max_heap_table_size = 1073741824;
-
Monitor the change in status variables from step #1. Mainly what you want to watch for is to see if Created_tmp_disk_tables increases, as that means that your smaller temp table size is causing more tables to be written to disk, which is not ideal. So if that number goes up faster than before (a bit harder to tell without adequate trending data), you should increase the values in step #2 until you are happy with the results.
-
If you decide to keep the changes, make sure to add them to your configuration file so they’ll be picked up on the next server restart.
Testing the query cache is a bit more subjective, as you have to monitor your overall system performance and number of slow queries with it on and off. To turn off the query cache, set global query_cache_type = 0 and set global query_cache_size = 0. Depending on your workload, this could make a big impact on the system (either good or bad), so do not do this unless you are very comfortable with testing it.
Thank you for the head’s up Scott
And also for a detailed walk thorough for setting up the “temp table size”.
Since, I had to approach the management for approval of the changes, But they were apprehensive to make any changes
on the server during month end’s.
Hence, I’ll have to wait fro the first week of June to do any further changes.
All i can do at the moment is to monitor the server …
Thank you once again, i shall update and get back …if i need any further assistance in the interim
Have a great time till then