Need Help for mysql optimization...

[shezi]

Hello friends,

I am here to have your great help about mysql optimization. I have VPS with 4 GB RAM. I request you to please let me know that how may i optimize mysql?

Note: I am not a expert, please try to explain step by step and clearly.

Regards,
Shezi

[cPanelTristan]

Hello Shezi,

You could try running the following script to see the recommendations it provides:

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

Next, I have a presentation that I did on MySQL Optimization at the cPanel conference last year that you may wish to review:

MySQL Optimization | cPanel Video Site

Thanks!

[shezi]

Is there any other better way to optimize mysql queries?

[LinuxTechie]

Hello,

I found the post by Tristan very helpful. May i know the reason why you are en-quiring about another method.

[shezi]

Dear i already told you that i am not expert, That's why i am trying to say that please give me full coding for my VPS. I have 4GB of RAM on my VPS.

Regards,
Shahzaib

[cPanelTristan]

Hello Shahzaib,

Unfortunately, there is no way to provide full coding for a machine without details being provided for the current MySQL usage. Do you have root SSH access? If so, please run the initial command I provided and provide the output here:

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

At that point, we would be happy to provide input on what to change in your /etc/my.cnf file. Also, if you could paste the contents of your /etc/my.cnf file:

cat /etc/my.cnf

Again, we are happy to help, but we cannot provide the details on what is needed without more information.

Finally, the video tutorial I linked is geared toward beginner level knowledge of MySQL.

Thanks.

[shezi]

Hello,

Here is the result of running ./tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.1.54 i686

./tuning-primer.sh: line 497: bc: command not found
./tuning-primer.sh: line 498: bc: command not found
./tuning-primer.sh: line 499: bc: command not found
./tuning-primer.sh: line 500: bc: command not found
./tuning-primer.sh: line 501: bc: command not found
./tuning-primer.sh: line 502: bc: command not found
Uptime = days hrs min sec
Avg. qps = 16
Total Questions = 16212968
Threads Connected = 3

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
MySQL :: MySQL 5.1 Reference Manual :: 5.1.4 Server System Variables
Visit MySQL :: MySQL Enterprise Advisors
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 139 out of 16212989 that take longer than 10.000000 sec. to complete
./tuning-primer.sh: line 403: bc: command not found
./tuning-primer.sh: line 606: [: -gt: unary operator expected
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See MySQL :: MySQL 5.1 Reference Manual :: 6.5 Point-in-Time (Incremental) Recovery Using the Binary Log

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 3
Historic max_used_connections = 28
The number of used connections is 18% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
./tuning-primer.sh: line 440: bc: command not found
Current InnoDB index space = M
./tuning-primer.sh: line 440: bc: command not found
Current InnoDB data space = M
Current InnoDB buffer pool free = 0 %
./tuning-primer.sh: line 440: bc: command not found
Current innodb_buffer_pool_size = M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
./tuning-primer.sh: line 1321: bc: command not found
./tuning-primer.sh: line 1322: bc: command not found
./tuning-primer.sh: line 1346: bc: command not found
./tuning-primer.sh: line 1349: bc: command not found
./tuning-primer.sh: line 1350: bc: command not found
./tuning-primer.sh: line 1352: bc: command not found
./tuning-primer.sh: line 1354: [: -gt: unary operator expected
./tuning-primer.sh: line 459: [: max_memoryHR: integer expression expected
./tuning-primer.sh: line 465: [: max_memoryHR: integer expression expected
./tuning-primer.sh: line 471: [: max_memoryHR: integer expression expected
./tuning-primer.sh: line 478: export: `=max_memoryHR': not a valid identifier
Max Memory Ever Allocated : bytes
./tuning-primer.sh: line 459: [: per_thread_buffersHR: integer expression expected
./tuning-primer.sh: line 465: [: per_thread_buffersHR: integer expression expected
./tuning-primer.sh: line 471: [: per_thread_buffersHR: integer expression expected
./tuning-primer.sh: line 478: export: `=per_thread_buffersHR': not a valid identifier
Configured Max Per-thread Buffers : bytes
./tuning-primer.sh: line 459: [: global_buffersHR: integer expression expected
./tuning-primer.sh: line 465: [: global_buffersHR: integer expression expected
./tuning-primer.sh: line 471: [: global_buffersHR: integer expression expected
./tuning-primer.sh: line 478: export: `=global_buffersHR': not a valid identifier
Configured Max Global Buffers : bytes
./tuning-primer.sh: line 459: [: total_memoryHR: integer expression expected
./tuning-primer.sh: line 465: [: total_memoryHR: integer expression expected
./tuning-primer.sh: line 471: [: total_memoryHR: integer expression expected
./tuning-primer.sh: line 478: export: `=total_memoryHR': not a valid identifier
Configured Max Memory Limit : bytes
./tuning-primer.sh: line 440: bc: command not found
Physical Memory : G
Max memory limit seem to be within acceptable norms

KEY BUFFER
./tuning-primer.sh: line 754: bc: command not found
./tuning-primer.sh: line 755: bc: command not found
./tuning-primer.sh: line 440: bc: command not found
Current MyISAM index space = M
./tuning-primer.sh: line 440: bc: command not found
Current key_buffer_size = M
Key cache miss rate is 1 : 36
Key buffer free ratio = %
./tuning-primer.sh: line 788: [: -le: unary operator expected
./tuning-primer.sh: line 792: [: -le: unary operator expected
./tuning-primer.sh: line 796: [: -le: unary operator expected
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
./tuning-primer.sh: line 440: bc: command not found
Current sort_buffer_size = M
./tuning-primer.sh: line 440: bc: command not found
Current read_rnd_buffer_size = K
Sort buffer seems to be fine

JOINS
./tuning-primer.sh: line 440: bc: command not found
Current join_buffer_size = K
You have had 3974 queries where a join could not use an index properly
You have had 11 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 4096 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 4245 tables
You have 64 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

TEMP TABLES
./tuning-primer.sh: line 440: bc: command not found
Current max_heap_table_size = M
./tuning-primer.sh: line 440: bc: command not found
Current tmp_table_size = M
Of 668179 temp tables, 17% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
./tuning-primer.sh: line 440: bc: command not found
Current read_buffer_size = K
Current table scan ratio = 204 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 3636
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

[shezi]

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

>> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at MySQLTuner
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.54
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 864M (Tables: 3362)
[--] Data in InnoDB tables: 27M (Tables: 801)
[--] Data in MEMORY tables: 3M (Tables: 59)
[!!] Total fragmented tables: 1032

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 6h 16m 30s (16M q [16.668 qps], 825K conn, TX: 970B, RX: 4B)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 439.8M (10% of installed RAM)
[OK] Slow queries: 0% (139/16M)
[OK] Highest usage of available connections: 18% (28/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/155.5M
[OK] Key buffer hit rate: 97.2% (503M cached / 13M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (7K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 3985
[OK] Temporary tables created on disk: 17% (137K on disk / 805K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 3M opened)
[OK] Open file limit used: 2% (122/4K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
[!!] InnoDB data size / buffer pool: 27.6M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_cache (> 64)
innodb_buffer_pool_size (>= 27M)

cat /etc/my.cnf

[mysqld]

[coderiser]

yes i have used this method as well and found it to be successful

[cPanelTristan]

Hello,

Since your server is running MySQL 5.1, please place the following into your /etc/my.cnf file so it looks like the following (basically adding those variables after the [mysqld] tag):

[mysqld]
query_cache_size = 8M
thread_cache_size = 4
table_open_cache = 80
innodb_buffer_pool_size = 27M
slow_query_log

Next, you can optimize all databases with this command:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Restart MySQL after you've done the above:

/etc/init.d/mysql restart

Please wait 24 hours and then re-run mysqltuner.pl again on the machine to see any other variable changes to make.

[shezi]

Please let me know one thing more clearly that my VPS is shared. Should i run mysqltuner.pl after every 24 hours?

[cPanelTristan]

You wouldn't need to run it every 24 hours, but should probably run it for 2-3 days every 24 hours to see the results. After you are satisfied with the changes, then you can simply re-run mysqltuner.pl again once or twice a month. I recommend consistently running it periodically just due to the fact that your environment can periodically change due to adding / removing accounts, so running that optimization script periodically would be good to do long-term.

[shezi]

Here is the result of mysqltuner.pl

>> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at MySQLTuner
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.54-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 904M (Tables: 3847)
[--] Data in InnoDB tables: 27M (Tables: 813)
[--] Data in MEMORY tables: 1M (Tables: 63)
[!!] Total fragmented tables: 913

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 10h 43m 9s (1M q [13.686 qps], 107K conn, TX: 65B, RX: 440M)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 61.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 466.8M (11% of installed RAM)
[OK] Slow queries: 0% (36/1M)
[OK] Highest usage of available connections: 12% (19/151)
[!!] Key buffer size / total MyISAM indexes: 8.0M/156.3M
[!!] Key buffer hit rate: 92.9% (16M cached / 1M reads)
[OK] Query cache efficiency: 42.2% (468K cached / 1M selects)
[!!] Query cache prunes per day: 260988
[OK] Sorts requiring temporary tables: 0% (260 temp sorts / 204K sorts)
[OK] Temporary tables created on disk: 21% (10K on disk / 48K total)
[OK] Thread cache hit rate: 99% (545 created / 107K connections)
[!!] Table cache hit rate: 0% (80 open / 249K opened)
[OK] Open file limit used: 3% (141/4K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 27.8M/27.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
key_buffer_size (> 156.3M)
query_cache_size (> 8M)
table_cache (> 80)
innodb_buffer_pool_size (>= 27M)

[cPanelTristan]

Hello,

Please post the /etc/my.cnf file contents every time after running the script as well. It isn't possible to know if the values were changed from those I initially suggested. I cannot note what to change unless I know what is in there currently.

Thanks!

[shezi]

Hello Dear,

etc/my.cnf After Running mysqltuner.pl today.

[mysqld]
query_cache_size = 8M
thread_cache_size = 4
table_open_cache = 80
innodb_buffer_pool_size = 27M
slow_query_log
local-infile=0