Mysql Eating a lot of memory and CPU

Hello I am having issues with MYSQL and CPU+RAM usage my server is Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz 4 cores/8 threads 32 GB ram 2x2TB raid0 my mysql configurations are:

[mysqld]
local-infile=0
default-storage-engine=InnoDB
max_connections = 300

# MyISAM #

key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP
sort_buffer_size = 1M
join_buffer_size = 2M

# CACHES AND LIMITS #

query_cache_limit = 10M
query_cache_size = 1024M
query_cache_type = 1

tmp-table-size                 = 512M
max-heap-table-size            = 512M
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 8192
table-open-cache               = 10240

# INNODB CONFIGURATION #

innodb_buffer_pool_size = 9G
innodb_log_file_size = 512M
innodb_log_buffer_size = 7M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances = 9
innodb_file_per_table=1

[mysqldump]
quick
max_allowed_packet = 16M

And Mysqtuner.pl reports are :

root@s10 [~]# ./mysqltuner.pl
>>  MySQLTuner 1.6.7 - Major Hayden 
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29
[OK] Operating on 64-bit architecture

-------- System Linux Recommendations  ---------------------------------------
Look for related Linux system recommandations
[--] There is 43 listening port(s) on this server.
[!!] There is too many listening ports: 43 > 10
[!!] There is Apache like server running on 80 or 443 port.
[OK] No Application server runing on 8080 or 8443 port.

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 11M (Tables: 34)
[--] Data in InnoDB tables: 8G (Tables: 7229)
[--] Data in CSV tables: 0B (Tables: 4)
[--] Data in MEMORY tables: 0B (Tables: 12)
[!!] Total fragmented tables: 547

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'munin@localhost' has user name as password.
[!!] User 'dataisla_checkin@%' hasn't specific host restriction.
[!!] User 'dataislamabad@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 0h 21m 25s (66M q [380.327 qps], 1M conn, TX: 491G, RX: 9G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Total buffers: 10.5G global + 2.6M per thread (300 max threads)
[OK] Maximum reached memory usage: 10.7G (34.25% of installed RAM)
[OK] Maximum possible memory usage: 11.3G (36.31% of installed RAM)
[OK] Slow queries: 0% (43/66M)
[OK] Highest usage of available connections: 16% (49/300)
[OK] Aborted connections: 0.00%  (11/1560518)
[!!] Query cache should be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (281 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 9648
[!!] Temporary tables created on disk: 52% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (49 created / 1M connections)
[OK] Table cache hit rate: 98% (8K open / 8K opened)
[OK] Open file limit used: 0% (131/65K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema --------------------------------------------------
[--] Performance schema is enabled.

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 24.3% (8M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/3.5M
[OK] Read Key buffer hit rate: 99.9% (2M cached / 1K reads)
[OK] Write Key buffer hit rate: 97.8% (195K cached / 4K writes)

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 9.0G/8.4G
[OK] InnoDB buffer pool instances: 9
[!!] InnoDB Used buffer: 38.74% (228513 used/ 589815 total)
[OK] InnoDB Read buffer efficiency: 100.00% (9858875877 hits/ 9859028204 total)
[!!] InnoDB Write Log efficiency: 82.70% (4580780 hits/ 5538873 total)
[!!] InnoDB log waits: 0.00% (2 waits / 958093 writes)

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Consider dedicating a server for your database installation with less services running on !
    Consider dedicating a server for Web server in production !
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    Adjust your join queries to always utilize indexes. Please note this
    calculation is made by adding Select_full_join + Select_range_check
    status values and triggered when the total >250
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_type (=0)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    innodb_log_buffer_size (>= 6M)

what should i do now to minimize server + RAM usage. because i am using FASTCGI with custom configurations. otherwise on SUPHP, the server load goes from 4% to 60% instantly.

Mysql Eating a lot of memory and CPU

Comments

Didn't find what you were looking for?