MySql usage morethen 700%

[selvar]

Hi i have 24Gb Ram.plesk 11.5.my site is very slow.when i run mysqltuner.pl it shows warning

>> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.33-cll-lve
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 1G (Tables: 1049)
[--] Data in InnoDB tables: 18M (Tables: 801)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 809


-------- Performance Metrics -------------------------------------------------
[--] Up for: 23m 39s (242K q [171.055 qps], 2K conn, TX: 1B, RX: 19M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 3.0G global + 1.0G per thread (500 max threads)
[!!] Maximum possible memory usage: 503.2G (2138% of installed RAM)
[OK] Slow queries: 0% (0/242K)
[OK] Highest usage of available connections: 82% (413/500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/219.6M
[OK] Key buffer hit rate: 99.9% (8M cached / 7K reads)
[!!] Query cache efficiency: 0.0% (0 cached / 161K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 32K sorts)
[!!] Temporary tables created on disk: 33% (4K on disk / 13K total)
[OK] Thread cache hit rate: 63% (741 created / 2K connections)
[OK] Table cache hit rate: 93% (2K open / 2K opened)
[OK] Open file limit used: 49% (2K/4K)
[OK] Table locks acquired immediately: 98% (154K immediate / 156K locks)
[OK] InnoDB buffer pool / data size: 1000.0M/19.0M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 1G, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)



my current my.cnf is

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-host-cache
skip-name-resolve

max_allowed_packet = 256M
sort_buffer_size = 512M
max_connections=500
wait-timeout=1800
interactive-timeout=60
query_cache_size = 1024M
query_cache_limit = 1024M
query-cache-type = 2
#long_query_time=0.3
table_cache = 2000
thread_cache_size=8
key_buffer_size = 1024M
read_buffer_size=256M
read_rnd_buffer_size=256M
myisam_sort_buffer_size=256M
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1000M
innodb_additional_mem_pool_size=20M

user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# Forced OLD_PASSWORD format is turned OFF by Plesk
#old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

 

[MislavO]

Your MySQL need more optimization.

What is running during this high CPU load, can you login next time into mysql and check output of command:
mysql -> SHOW PROCESSLIST;

In addition, you've configured way too many threads, just check the warning

[--] Total buffers: 3.0G global + 1.0G per thread (500 max threads)
[!!] Maximum possible memory usage: 503.2G (2138% of installed RAM)

Please run Tuning Primer:
https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh

and post output from it.

 

[selvar]

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 5.000000 sec.
You have 154519 out of 486841 that take longer than 5.000000 sec. to complete
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 http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

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

MAX CONNECTIONS
Current max_connections = 700
Current threads_connected = 90
Historic max_used_connections = 421
The number of used connections is 60% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 12 M
Current InnoDB data space = 18 M
Current InnoDB buffer pool free = 96 %
Current innodb_buffer_pool_size = 1000 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
Max Memory Ever Allocated : 318.03 G
Configured Max Per-thread Buffers : 525.25 G
Configured Max Global Buffers : 2.12 G
Configured Max Memory Limit : 527.38 G
Physical Memory : 23.53 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 218 M
Current key_buffer_size = 1.00 G
Key cache miss rate is 1 : 3157
Key buffer free ratio = 80 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 17 K
Current query_cache_limit = 32 M
Current Query cache Memory fill ratio = .01 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 256 M
Current read_rnd_buffer_size = 256 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 80 queries where a join could not use an index properly
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 = 4710 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 = 2000 tables
Current table_definition_cache = 400 tables
You have a total of 1893 tables
You have 2000 open tables.
Current table_cache hit rate is 34%
, 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
Current max_heap_table_size = 16 M
Current tmp_table_size = 256 M
Of 30894 temp tables, 21% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 256 M
Current table scan ratio = 6207 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_buffer

TABLE LOCKING
Current Lock Wait ratio = 1 : 81
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=ALWAYS'.

 

[MislavO]

For start, configure slow query logs to something like:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 30

If you have queries that run longer then 30sec, something is wrong. Check slow query log and try to fix all queries you can. After that, move query time to 15-20 sec and try to fix queries you can.

MAX CONNECTIONS - this is fine, although I have a question: is this server with only one big application or is it shared user? If it's shared, try use something in additional like "max_user_connections=25" - that will be enough for all websites and if client on shared server need like 30-35, then those websites are doing impact on the whole server and you should move them on own VPS/dedicated server. If there is only one website on this server, don't set max_user_connections or, if you you're setting it, set the number equal to max_connections.

Increase both to something like
table_definition_cache = 4096
table_open_cache = 8192

TABLE SCANS
Current read_buffer_size = 256 M

There is no need for this, 4-8MB read_buffer size is more then enough.

After that, make sure you insert in [mysqld] part (this is a MUST):
low_priority_updates=1
concurrent_insert=ALWAYS

Change this, restart MySQL, run tuning primer after 48h and post output here.