SQL using more CPU but not the RAM!

jkhaksaar

Verified User
Joined
Feb 25, 2016
Messages
6
Hello
I have a server with 32 GB Ram and i7 Processor, and i want to get a new server with Xeon Processor, but for now when i'm monitoring my server i can see that mysql is using almost 90% of my CPU and sometimes it's getting overload but the RAM only use about 2 GB, i want to know what should i do to reduce the CPU load? and sql use my Ram more than CPU, FYI my storage is also SSD.
i just put the log file for more information:

Code:
1  [|||||||||#####################################********                          60.9%]     5  [|||||||||#######################*****                                           41.1%]    
  2  [||||||||||||||||########################*******                                 53.8%]     6  [|||||############################******                                         43.7%]    
  3  [|||||||||||||||#####################################******                      67.1%]     7  [||||#################***                                                        26.7%]    
  4  [|||||||||||####################################******                           60.5%]     8  [|||########################***                                                  33.3%]    
  Mem[||||||#****************************                                      2076/32110MB]     Tasks: 122, 31 thr; 7 running                                                                 
  Swp[|||                                                                         24/1021MB]     Load average: 3.04 3.91 4.56                                                                  
                                                                                                 Uptime: 121 days(!), 09:45:15                                                                 

  PID USER      PRI  NI  VIRT   RES   SHR S CPU% MEM%   TIME+  Command                                                                                                                         
 4558 mysql      26   6 4384M  196M  6228 S 94.7  0.6 56h42:09 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/
 5262 mysql      26   6 4384M  196M  6228 R 45.5  0.6  0:10.41 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/
 2022 mysql      26   6 4384M  196M  6228 S 45.0  0.6  0:36.35 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/
 1673 admin      20   0  564M  431M  3020 R 42.1  1.3  0:36.20 /usr/bin/perl fs.cgi                                                                                                            
 5051 admin      20   0  564M  431M  3020 R 40.7  1.3  0:12.35 /usr/bin/perl fs.cgi                                                                                                            
 4577 mysql      20   0 4384M  196M  6228 S  1.4  0.6  1h22:12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/




30208 root       20   0  110M  2432  1292 R  1.0  0.0  0:00.33 htop                                                                                                                            
 2179 apache     20   0  269M  9012  2344 S  0.5  0.0  0:00.03 /usr/sbin/httpd -k start -DSSL                                                                                                  
25320 apache     20   0  269M  8972  2296 S  0.5  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
25291 apache     20   0  269M  9040  2348 S  0.5  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
31746 apache     20   0  269M  8968  2300 S  0.5  0.0  0:00.05 /usr/sbin/httpd -k start -DSSL                                                                                                  
25315 apache     20   0  269M  9028  2352 S  0.5  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
27809 apache     20   0  269M  8960  2300 S  0.5  0.0  0:00.07 /usr/sbin/httpd -k start -DSSL                                                                                                  
 4567 mysql      20   0 4384M  196M  6228 S  0.0  0.6  2:00.68 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/
25280 apache     20   0  269M  9012  2356 S  0.0  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
25308 apache     20   0  269M  9044  2348 S  0.0  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
25281 apache     20   0  269M  8976  2300 S  0.0  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
25275 apache     20   0  269M  9016  2352 S  0.0  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
25319 apache     20   0  269M  8960  2300 S  0.0  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
25568 apache     20   0  269M  8940  2292 S  0.0  0.0  0:00.08 /usr/sbin/httpd -k start -DSSL                                                                                                  
25301 apache     20   0  269M  9016  2344 S  0.0  0.0  0:00.07 /usr/sbin/httpd -k start -DSSL                                                                                                  
25278 apache     20   0  269M  9032  2356 S  0.0  0.0  0:00.07 /usr/sbin/httpd -k start -DSSL                                                                                                  
30022 apache     20   0  269M  9004  2348 S  0.0  0.0  0:00.07 /usr/sbin/httpd -k start -DSSL                                                                                                  
25340 apache     20   0  269M  9028  2348 S  0.0  0.0  0:00.17 /usr/sbin/httpd -k start -DSSL                                                                                                  
F1Help  F2Setup F3SearchF4FilterF5Tree  F6SortByF7Nice -F8Nice +F9Kill  F10Quit                                                                                                                
[1]+  Stopped                 htop                                                                                                                                                             
[root@sv1 ~]# wget http://mysqltuner.pl/ -O mysqltuner.pl                                                                                                                                      
--2016-02-25 19:34:29--  http://mysqltuner.pl/                                                                                                                                                 
Resolving mysqltuner.pl... 217.70.184.38                                                                                                                                                       
Connecting to mysqltuner.pl|217.70.184.38|:80... connected.                                                                                                                                    
HTTP request sent, awaiting response... 301 Moved Permanently                                                                                                                                  
Location: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl [following]                                                                                                        
--2016-02-25 19:34:30--  https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl                                                                                                     
Resolving raw.github.com... 185.31.18.133                                                                                                                                                      
Connecting to raw.github.com|185.31.18.133|:443... connected.                                                                                                                                  
HTTP request sent, awaiting response... 301 Moved Permanently                                                                                                                                  
Location: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl [following]                                                                                             
--2016-02-25 19:34:30--  https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl                                                                                          
Resolving raw.githubusercontent.com... 185.31.18.133                                                                                                                                           
Connecting to raw.githubusercontent.com|185.31.18.133|:443... connected.                                                                                                                       
HTTP request sent, awaiting response... 200 OK                                                                                                                                                 
Length: 111311 (109K) [text/plain]                                                                                                                                                             
Saving to: ‚mysqltuner.pl‚                                                                                                                                                                     

100%[=====================================================================================================================================================>] 111,311     --.-K/s   in 0.007s   

2016-02-25 19:34:30 (15.5 MB/s) - ‚mysqltuner.pl‚saved [111311/111311]                                                                                                                         

[root@sv1 ~]# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt                                                                   
--2016-02-25 19:34:30--  https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt                                                                                    
Resolving raw.githubusercontent.com... 185.31.18.133                                                                                                                                           
Connecting to raw.githubusercontent.com|185.31.18.133|:443... connected.                                                                                                                       
HTTP request sent, awaiting response... 200 OK                                                                                                                                                 
Length: 3811 (3.7K) [text/plain]                                                                                                                                                               
Saving to: ‚basic_passwords.txt‚                                                                                                                                                               

100%[=====================================================================================================================================================>] 3,811       --.-K/s   in 0s       

2016-02-25 19:34:30 (844 MB/s) - ‚basic_passwords.txt‚saved [3811/3811]                                                                                                                        

[root@sv1 ~]# perl mysqltuner.pl                                                                                                                                                               
 >>  MySQLTuner 1.6.4 - Major Hayden <[email protected]>                                                                                                                                          
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/                                                                                                                    
 >>  Run with '--help' for additional options and output filtering                                                                                                                             

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

-------- Storage Engine Statistics -------------------------------------------                                                                                                                 
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM                                                                                                                           
[--] Data in MyISAM tables: 100M (Tables: 50)                                                                                                                                                  
[--] Data in InnoDB tables: 575M (Tables: 8)                                                                                                                                                   
[!!] Total fragmented tables: 23                                                                                                                                                               

-------- Security Recommendations  -------------------------------------------                                                                                                                 
[!!] User '@localhost' is an anonymous account.                                                                                                                                                
[!!] User '@upsv.afghanhost.co' is an anonymous account.                                                                                                                                       
[OK] All database users have passwords assigned                                                                                                                                                
[--] There are 605 basic passwords in the list.                                                                                                                                                

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

-------- Performance Metrics -------------------------------------------------                                                                                                                 
[--] Up for: 1d 20h 56m 23s (2B q [13K qps], 4M conn, TX: 3921B, RX: 381B)                                                                                                                     
[--] Reads / Writes: 66% / 34%                                                                                                                                                                 
[--] Binary logging is disabled                                                                                                                                                                
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)                                                                                                                          
[OK] Maximum reached memory usage: 586.0M (1.82% of installed RAM)                                                                                                                             
[OK] Maximum possible memory usage: 583.2M (1.82% of installed RAM)                                                                                                                            
[OK] Slow queries: 0% (5/2B)                                                                                                                                                                   
[!!] Highest connection usage: 100%  (152/151)                                                                                                                                                 
[OK] Aborted connections: 0.01%  (486/4480487)                                                                                                                                                 
[!!] Query cache is disabled                                                                                                                                                                   
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 45K sorts)                                                                                                                           
[OK] Temporary tables created on disk: 3% (1K on disk / 42K total)                                                                                                                             
[!!] Thread cache is disabled                                                                                                                                                                  
[OK] Table cache hit rate: 55% (400 open / 720 opened)                                                                                                                                         
[OK] Open file limit used: 35% (367/1K)                                                                                                                                                        
[OK] Table locks acquired immediately: 99% (2B immediate / 2B locks)                                                                                                                           

-------- MyISAM Metrics ------------------------------------------------------                                                                                                                 
[OK] Key buffer used: 100.0% (8M used / 8M cache)                                                                                                                                              
[OK] Key buffer size / total MyISAM indexes: 8.0M/38.9M                                                                                                                                        
[OK] Read Key buffer hit rate: 100.0% (3B cached / 117K reads)                                                                                                                                 
[!!] Write Key buffer hit rate: 47.7% (168K cached / 88K writes)                                                                                                                               

-------- InnoDB Metrics ------------------------------------------------------                                                                                                                 
[--] InnoDB is enabled.                                                                                                                                                                        
[!!] InnoDB buffer pool / data size: 128.0M/575.7M                                                                                                                                             
[OK] InnoDB buffer pool instances: 1                                                                                                                                                           
[OK] InnoDB Used buffer: 99.99% (8191 used/ 8192 total)                                                                                                                                        
[OK] InnoDB Read buffer efficiency: 99.99% (29263829017 hits/ 29265451995 total)                                                                                                               
[!!] InnoDB Write Log efficiency: 10.96% (46098202 hits/ 420678116 total)                                                                                                                      
[OK] InnoDB log waits: 0.00% (0 waits / 374579914 writes)                                                                                                                                      

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

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

-------- 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:                                                                                                                                                                       
    Run OPTIMIZE TABLE to defragment tables for better performance                                                                                                                             
    Remove Anonymous User accounts - there are 2 anonymous accounts.                                                                                                                           
    Enable the slow query log to troubleshoot bad queries                                                                                                                                      
    Reduce or eliminate persistent connections to reduce connection usage                                                                                                                      
    Set thread_cache_size to 4 as a starting value                                                                                                                                             
Variables to adjust:                                                                                                                                                                           
    max_connections (> 151)                                                                                                                                                                    
    wait_timeout (< 28800)                                                                                                                                                                     
    interactive_timeout (< 28800)                                                                                                                                                              
    query_cache_size (>= 8M)                                                                                                                                                                   
    thread_cache_size (start at 4)                                                                                                                                                             
    innodb_buffer_pool_size (>= 575M) if possible.                                                                                                                                             
[root@sv1 ~]# perl mysqltuner.pl --debug                                                                                                                                                       
 >>  MySQLTuner 1.6.4 - Major Hayden <[email protected]>                                                                                                                                          
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/                                                                                                                    
 >>  Run with '--help' for additional options and output filtering                                                                                                                             
[DG] MySQL Client: /usr/bin/mysql would have been started with the following arguments:                                                                                                        


[DG] MySQL Client: /usr/bin/mysql                                                                                                                                                              

[--] Skipped version check for MySQLTuner script                                                                                                                                               
[DG] Physical Memory: 33669910528                                                                                                                                                              
[DG] Swap Memory: 1071636480                                                                                                                                                                   
[DG] PERFORM: SELECT VERSION()                                                                                                                                                                 
[DG] VERSION: 5.5.31                                                                                                                                                                           
[DG] PERFORM: SHOW /*!50000 GLOBAL */ VARIABLES                                                                                                                                                
[DG] V: auto_increment_increment = 1                                                                                                                                                           
[DG] V: auto_increment_offset = 1                                                                                                                                                              
[DG] V: autocommit = ON                                                                                                                                                                        
[DG] V: automatic_sp_privileges = ON                                                                                                                                                           
[DG] V: back_log = 50                                                                                                                                                                          
[DG] V: basedir = /usr                                                                                                                                                                         
[DG] V: big_tables = OFF                                                                                                                                                                       
[DG] V: binlog_cache_size = 32768                                                                                                                                                              
[DG] V: binlog_direct_non_transactional_updates = OFF                                                                                                                                          
[DG] V: binlog_format = STATEMENT                                                                                                                                                              
[DG] V: binlog_stmt_cache_size = 32768                                                                                                                                                         
[DG] V: bulk_insert_buffer_size = 8388608                                                                                                                                                      
[DG] V: character_set_client = latin1                                                                                                                                                          
[DG] V: character_set_connection = latin1                                                                                                                                                      
[DG] V: character_set_database = latin1                                                                                                                                                        
[DG] V: character_set_filesystem = binary                                                                                                                                                      
[DG] V: character_set_results = latin1                                                                                                                                                         
[DG] V: character_set_server = latin1                                                                                                                                                          
[DG] V: character_set_system = utf8                                                                                                                                                            
[DG] V: character_sets_dir = /usr/share/mysql/charsets/                                                                                                                                        
[DG] V: collation_connection = latin1_swedish_ci                                                                                                                                               
[DG] V: collation_database = latin1_swedish_ci                                                                                                                                                 
[DG] V: collation_server = latin1_swedish_ci                                                                                                                                                   
[DG] V: completion_type = NO_CHAIN                                                                                                                                                             
[DG] V: concurrent_insert = AUTO                                                                                                                                                               
[DG] V: connect_timeout = 10                                                                                                                                                                   
[DG] V: datadir = /var/lib/mysql/                                                                                                                                                              
[DG] V: date_format = %Y-%m-%d                                                                                                                                                                 
[DG] V: datetime_format = %Y-%m-%d %H:%i:%s                                                                                                                                                    
[DG] V: default_storage_engine = InnoDB                                                                                                                                                        
[DG] V: default_week_format = 0                                                                                                                                                                
[DG] V: delay_key_write = ON                                                                                                                                                                   
[DG] V: delayed_insert_limit = 100                                                                                                                                                             
[DG] V: delayed_insert_timeout = 300                                                                                                                                                           
[DG] V: delayed_queue_size = 1000                                                                                                                                                              
[DG] V: div_precision_increment = 4                                                                                                                                                            
[DG] V: engine_condition_pushdown = ON                                                                                                                                                         
[DG] V: event_scheduler = OFF                                                                                                                                                                  
[DG] V: expire_logs_days = 0                                                                                                                                                                   
[DG] V: flush = OFF                                                                                                                                                                            
[DG] V: flush_time = 0                                                                                                                                                                         
[DG] V: foreign_key_checks = ON                                                                                                                                                                
[DG] V: ft_boolean_syntax = + -><()~*:""&|                                                                                                                                                     
[DG] V: ft_max_word_len = 84                                                                                                                                                                   
[DG] V: ft_min_word_len = 4                                                                                                                                                                    
[DG] V: ft_query_expansion_limit = 20                                                                                                                                                          
[DG] V: ft_stopword_file = (built-in)                                                                                                                                                          
[DG] V: general_log = OFF                                                                                                                                                                      
[DG] V: general_log_file = /var/lib/mysql/sv1.log                                                                                                                                              
[DG] V: group_concat_max_len = 1024                                                                                                                                                            
[DG] V: have_compress = YES                                                                                                                                                                    
[DG] V: have_crypt = YES                                                                                                                                                                       
[DG] V: have_csv = YES                                                                                                                                                                         
[DG] V: have_dynamic_loading = YES                                                                                                                                                             
[DG] V: have_geometry = YES                                                                                                                                                                    
[DG] V: have_innodb = YES                                                                                                                                                                      
[DG] V: have_ndbcluster = NO                                                                                                                                                                   
[DG] V: have_openssl = DISABLED                                                                                                                                                                
[DG] V: have_partitioning = YES                                                                                                                                                                
[DG] V: have_profiling = YES                                                                                                                                                                   
[DG] V: have_query_cache = YES                                                                                                                                                                 
[DG] V: have_rtree_keys = YES                                                                                                                                                                  
[DG] V: have_ssl = DISABLED                                                                                                                                                                    
[DG] V: have_symlink = YES                                                                                                                                                                     
[DG] V: hostname = sv1.uploadcdn.net                                                                                                                                                           
[DG] V: ignore_builtin_innodb = OFF                                                                                                                                                            
[DG] V: init_connect =                                                                                                                                                                         
[DG] V: init_file =                                                                                                                                                                            
[DG] V: init_slave =                                                                                                                                                                           
[DG] V: innodb_adaptive_flushing = ON                                                                                                                                                          
[DG] V: innodb_adaptive_hash_index = ON                                                                                                                                                        
[DG] V: innodb_additional_mem_pool_size = 8388608                                                                                                                                              
[DG] V: innodb_autoextend_increment = 8                                                                                                                                                        
[DG] V: innodb_autoinc_lock_mode = 1                                                                                                                                                           
[DG] V: innodb_buffer_pool_instances = 1                                                                                                                                                       
[DG] V: innodb_buffer_pool_size = 134217728                                                                                                                                                    
[DG] V: innodb_change_buffering = all                                                                                                                                                          
[DG] V: innodb_checksums = ON                                                                                                                                                                  
[DG] V: innodb_commit_concurrency = 0                                                                                                                                                          
[DG] V: innodb_concurrency_tickets = 500                                                                                                                                                       
[DG] V: innodb_data_file_path = ibdata1:10M:autoextend                                                                                                                                         
[DG] V: innodb_data_home_dir =                                                                                                                                                                 
[DG] V: innodb_doublewrite = ON                                                                                                                                                                
[DG] V: innodb_fast_shutdown = 1                                                                                                                                                               
[DG] V: innodb_file_format = Antelope                                                                                                                                                          
[DG] V: innodb_file_format_check = ON                                                                                                                                                          
[DG] V: innodb_file_format_max = Antelope                                                                                                                                                      
[DG] V: innodb_file_per_table = OFF                                                                                                                                                            
[DG] V: innodb_flush_log_at_trx_commit = 1                                                                                                                                                     
[DG] V: innodb_flush_method =                                                                                                                                                                  
[DG] V: innodb_force_load_corrupted = OFF                                                                                                                                                      
[DG] V: innodb_force_recovery = 0                                                                                                                                                              
[DG] V: innodb_io_capacity = 200                                                                                                                                                               
[DG] V: innodb_large_prefix = OFF                                                                                                                                                              
[DG] V: innodb_lock_wait_timeout = 50                                                                                                                                                          
[DG] V: innodb_locks_unsafe_for_binlog = OFF                                                                                                                                                   
[DG] V: innodb_log_buffer_size = 8388608                                                                                                                                                       
[DG] V: innodb_log_file_size = 5242880                                                                                                                                                         
[DG] V: innodb_log_files_in_group = 2                                                                                                                                                          
[DG] V: innodb_log_group_home_dir = ./                                                                                                                                                         
[DG] V: innodb_max_dirty_pages_pct = 75                                                                                                                                                        
[DG] V: innodb_max_purge_lag = 0                                                                                                                                                               
[DG] V: innodb_mirrored_log_groups = 1                                                                                                                                                         
[DG] V: innodb_old_blocks_pct = 37                                                                                                                                                             
[DG] V: innodb_old_blocks_time = 0                                                                                                                                                             
[DG] V: innodb_open_files = 300                                                                                                                                                                
[DG] V: innodb_print_all_deadlocks = OFF                                                                                                                                                       
[DG] V: innodb_purge_batch_size = 20                                                                                                                                                           
[DG] V: innodb_purge_threads = 0                                                                                                                                                               
[DG] V: innodb_random_read_ahead = OFF                                                                                                                                                         
[DG] V: innodb_read_ahead_threshold = 56                                                                                                                                                       
[DG] V: innodb_read_io_threads = 4                                                                                                                                                             
[DG] V: innodb_replication_delay = 0                                                                                                                                                           
[DG] V: innodb_rollback_on_timeout = OFF                                                                                                                                                       
[DG] V: innodb_rollback_segments = 128                                                                                                                                                         
[DG] V: innodb_spin_wait_delay = 6                                                                                                                                                             
[DG] V: innodb_stats_method = nulls_equal                                                                                                                                                      
[DG] V: innodb_stats_on_metadata = ON                                                                                                                                                          
[DG] V: innodb_stats_sample_pages = 8                                                                                                                                                          
[DG] V: innodb_strict_mode = OFF                                                                                                                                                               
[DG] V: innodb_support_xa = ON                                                                                                                                                                 
[DG] V: innodb_sync_spin_loops = 30                                                                                                                                                            
[DG] V: innodb_table_locks = ON                                                                                                                                                                
[DG] V: innodb_thread_concurrency = 0                                                                                                                                                          
[DG] V: innodb_thread_sleep_delay = 10000                                                                                                                                                      
[DG] V: innodb_use_native_aio = ON                                                                                                                                                             
[DG] V: innodb_use_sys_malloc = ON                                                                                                                                                             
[DG] V: innodb_version = 5.5.31                                                                                                                                                                
[DG] V: innodb_write_io_threads = 4                                                                                                                                                            
[DG] V: interactive_timeout = 28800                                                                                                                                                            
[DG] V: join_buffer_size = 131072                                                                                                                                                              
[DG] V: keep_files_on_create = OFF                                                                                                                                                             
[DG] V: key_buffer_size = 8388608                                                                                                                                                              
[DG] V: key_cache_age_threshold = 300                                                                                                                                                          
[DG] V: key_cache_block_size = 1024                                                                                                                                                            
[DG] V: key_cache_division_limit = 100                                                                                                                                                         
[DG] V: large_files_support = ON                                                                                                                                                               
[DG] V: large_page_size = 0                                                                                                                                                                    
[DG] V: large_pages = OFF                                                                                                                                                                      
[DG] V: lc_messages = en_US                                                                                                                                                                    
[DG] V: lc_messages_dir = /usr/share/mysql/                                                                                                                                                    
[DG] V: lc_time_names = en_US                                                                                                                                                                  
[DG] V: license = GPL                                                                                                                                                                          
[DG] V: local_infile = OFF                                                                                                                                                                     
[DG] V: lock_wait_timeout = 31536000                                                                                                                                                           
[DG] V: locked_in_memory = OFF                                                                                                                                                                 
[DG] V: log = OFF                                                                                                                                                                              
[DG] V: log_bin = OFF                                                                                                                                                                          
[DG] V: log_bin_trust_function_creators = OFF                                                                                                                                                  
[DG] V: log_error = /var/lib/mysql/sv1.uploadcdn.net.err                                                                                                                                       
[DG] V: log_output = FILE                                                                                                                                                                      
[DG] V: log_queries_not_using_indexes = OFF                                                                                                                                                    
[DG] V: log_slave_updates = OFF                                                                                                                                                                
[DG] V: log_slow_queries = OFF                                                                                                                                                                 
[DG] V: log_warnings = 1                                                                                                                                                                       
[DG] V: long_query_time = 10.000000                                                                                                                                                            
[DG] V: low_priority_updates = OFF                                                                                                                                                             
[DG] V: lower_case_file_system = OFF                                                                                                                                                           
[DG] V: lower_case_table_names = 0                                                                                                                                                             
[DG] V: max_allowed_packet = 1048576                                                                                                                                                           
[DG] V: max_binlog_cache_size = 18446744073709547520                                                                                                                                           
[DG] V: max_binlog_size = 1073741824                                                                                                                                                           
[DG] V: max_binlog_stmt_cache_size = 18446744073709547520                                                                                                                                      
[DG] V: max_connect_errors = 10                                                                                                                                                                
[DG] V: max_connections = 151                                                                                                                                                                  
[DG] V: max_delayed_threads = 20                                                                                                                                                               
[DG] V: max_error_count = 64                                                                                                                                                                   
[DG] V: max_heap_table_size = 16777216                                                                                                                                                         
[DG] V: max_insert_delayed_threads = 20                                                                                                                                                        
[DG] V: max_join_size = 18446744073709551615                                                                                                                                                   
[DG] V: max_length_for_sort_data = 1024                                                                                                                                                        
[DG] V: max_long_data_size = 1048576                                                                                                                                                           
[DG] V: max_prepared_stmt_count = 16382                                                                                                                                                        
[DG] V: max_relay_log_size = 0                                                                                                                                                                 
[DG] V: max_seeks_for_key = 18446744073709551615                                                                                                                                               
[DG] V: max_sort_length = 1024                                                                                                                                                                 
[DG] V: max_sp_recursion_depth = 0                                                                                                                                                             
[DG] V: max_tmp_tables = 32                                                                                                                                                                    
[DG] V: max_user_connections = 0                                                                                                                                                               
[DG] V: max_write_lock_count = 18446744073709551615                                                                                                                                            
[DG] V: metadata_locks_cache_size = 1024                                                                                                                                                       
[DG] V: min_examined_row_limit = 0                                                                                                                                                             
[DG] V: multi_range_count = 256                                                                                                                                                                
[DG] V: myisam_data_pointer_size = 6                                                                                                                                                           
[DG] V: myisam_max_sort_file_size = 9223372036853727232                                                                                                                                        
[DG] V: myisam_mmap_size = 18446744073709551615                                                                                                                                                
[DG] V: myisam_recover_options = OFF                                                                                                                                                           
[DG] V: myisam_repair_threads = 1                                                                                                                                                              
[DG] V: myisam_sort_buffer_size = 8388608                                                                                                                                                      
[DG] V: myisam_stats_method = nulls_unequal                                                                                                                                                    
[DG] V: myisam_use_mmap = OFF                                                                                                                                                                  
[DG] V: net_buffer_length = 16384                                                                                                                                                              
[DG] V: net_read_timeout = 30                                                                                                                                                                  
[DG] V: net_retry_count = 10                                                                                                                                                                   
[DG] V: net_write_timeout = 60                                                                                                                                                                 
[DG] V: new = OFF                                                                                                                                                                              
[DG] V: old = OFF                                                                                                                                                                              
[DG] V: old_alter_table = OFF                                                                                                                                                                  
[DG] V: old_passwords = OFF                                                                                                                                                                    
[DG] V: open_files_limit = 1024                                                                                                                                                                
[DG] V: optimizer_prune_level = 1                                                                                                                                                              
[DG] V: optimizer_search_depth = 62                                                                                                                                                            
[DG] V: optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on                                              
[DG] V: performance_schema = OFF                                                                                                                                                               
[DG] V: performance_schema_events_waits_history_long_size = 10000                                                                                                                              
[DG] V: performance_schema_events_waits_history_size = 10                                                                                                                                      
[DG] V: performance_schema_max_cond_classes = 80                                                                                                                                               
[DG] V: performance_schema_max_cond_instances = 1000                                                                                                                                           
[DG] V: performance_schema_max_file_classes = 50                                                                                                                                               
[DG] V: performance_schema_max_file_handles = 32768                                                                                                                                            
[DG] V: performance_schema_max_file_instances = 10000                                                                                                                                          
[DG] V: performance_schema_max_mutex_classes = 200                                                                                                                                             
[DG] V: performance_schema_max_mutex_instances = 1000000                                                                                                                                       
[DG] V: performance_schema_max_rwlock_classes = 30                                                                                                                                             
[DG] V: performance_schema_max_rwlock_instances = 1000000                                                                                                                                      
[DG] V: performance_schema_max_table_handles = 100000                                                                                                                                          
[DG] V: performance_schema_max_table_instances = 50000                                                                                                                                         
[DG] V: performance_schema_max_thread_classes = 50                                                                                                                                             
[DG] V: performance_schema_max_thread_instances = 1000                                                                                                                                         
[DG] V: pid_file = /var/lib/mysql/sv1.uploadcdn.net.pid                                                                                                                                        
[DG] V: plugin_dir = /usr/lib64/mysql/plugin/                                                                                                                                                  
[DG] V: port = 3306                                                                                                                                                                            
[DG] V: preload_buffer_size = 32768                                                                                                                                                            
[DG] V: profiling = OFF                                                                                                                                                                        
[DG] V: profiling_history_size = 15                                                                                                                                                            
[DG] V: protocol_version = 10                                                                                                                                                                  
[DG] V: query_alloc_block_size = 8192                                                                                                                                                          
[DG] V: query_cache_limit = 1048576                                                                                                                                                            
[DG] V: query_cache_min_res_unit = 4096                                                                                                                                                        
[DG] V: query_cache_size = 0                                                                                                                                                                   
[DG] V: query_cache_type = ON                                                                                                                                                                  
[DG] V: query_cache_wlock_invalidate = OFF                                                                                                                                                     
[DG] V: query_prealloc_size = 8192                                                                                                                                                             
[DG] V: range_alloc_block_size = 4096                                                                                                                                                          
[DG] V: read_buffer_size = 131072                                                                                                                                                              
[DG] V: read_only = OFF                                                                                                                                                                        
[DG] V: read_rnd_buffer_size = 262144                                                                                                                                                          
[DG] V: relay_log =                                                                                                                                                                            
[DG] V: relay_log_index =                                                                                                                                                                      
[DG] V: relay_log_info_file = relay-log.info                                                                                                                                                   
[DG] V: relay_log_purge = ON                                                                                                                                                                   
[DG] V: relay_log_recovery = OFF                                                                                                                                                               
[DG] V: relay_log_space_limit = 0                                                                                                                                                              
[DG] V: report_host =                                                                                                                                                                          
[DG] V: report_password =                                                                                                                                                                      
[DG] V: report_port = 3306                                                                                                                                                                     
[DG] V: report_user =                                                                                                                                                                          
[DG] V: rpl_recovery_rank = 0                                                                                                                                                                  
[DG] V: secure_auth = OFF                                                                                                                                                                      
[DG] V: secure_file_priv =                                                                                                                                                                     
[DG] V: server_id = 0                                                                                                                                                                          
[DG] V: skip_external_locking = ON                                                                                                                                                             
[DG] V: skip_name_resolve = OFF                                                                                                                                                                
[DG] V: skip_networking = OFF                                                                                                                                                                  
[DG] V: skip_show_database = OFF                                                                                                                                                               
[DG] V: slave_compressed_protocol = OFF                                                                                                                                                        
[DG] V: slave_exec_mode = STRICT                                                                                                                                                               
[DG] V: slave_load_tmpdir = /tmp                                                                                                                                                               
[DG] V: slave_max_allowed_packet = 1073741824                                                                                                                                                  
[DG] V: slave_net_timeout = 3600                                                                                                                                                               
[DG] V: slave_skip_errors = OFF                                                                                                                                                                
[DG] V: slave_transaction_retries = 10                                                                                                                                                         
[DG] V: slave_type_conversions =                                                                                                                                                               
[DG] V: slow_launch_time = 2                                                                                                                                                                   
[DG] V: slow_query_log = OFF                                                                                                                                                                   
[DG] V: slow_query_log_file = /var/lib/mysql/sv1-slow.log                                                                                                                                      
[DG] V: socket = /var/lib/mysql/mysql.sock                                                                                                                                                     
[DG] V: sort_buffer_size = 2097152                                                                                                                                                             
[DG] V: sql_auto_is_null = OFF                                                                                                                                                                 
[DG] V: sql_big_selects = ON                                                                                                                                                                   
[DG] V: sql_big_tables = OFF                                                                                                                                                                   
[DG] V: sql_buffer_result = OFF                                                                                                                                                                
[DG] V: sql_log_bin = ON                                                                                                                                                                       
[DG] V: sql_log_off = OFF                                                                                                                                                                      
[DG] V: sql_low_priority_updates = OFF                                                                                                                                                         
[DG] V: sql_max_join_size = 18446744073709551615                                                                                                                                               
[DG] V: sql_mode =                                                                                                                                                                             
[DG] V: sql_notes = ON                                                                                                                                                                         
[DG] V: sql_quote_show_create = ON                                                                                                                                                             
[DG] V: sql_safe_updates = OFF                                                                                                                                                                 
[DG] V: sql_select_limit = 18446744073709551615                                                                                                                                                
[DG] V: sync_frm = ON                                                                                                                                                                          
[DG] V: sync_master_info = 0                                                                                                                                                                   
[DG] V: sync_relay_log = 0

this is also an sql database but as you see the below code it's partitioned! i wanna know if i follow this structure, can i get the chance to reduce the CPU load and more ram will be used ?

Code:
oot@svblog [/var/lib/mysql]# ls -rtl
total 478592
drwx------  2 mysql mysql      4096 Feb 23  2015 modsec/
drwx------  2 mysql mysql      4096 Feb 23  2015 leechprotect/
drwx------  2 mysql mysql      4096 Feb 24  2015 roundcube/
drwx------  2 mysql mysql      4096 Mar 28  2015 whmxfer/
drwx------  2 mysql mysql      4096 Jun 22  2015 cphulkd/
drwx------  2 mysql mysql    290816 Nov 26 14:16 qandol_5/
drwx------  2 mysql mysql     20480 Dec 10 02:24 qandol_global/
drwx------  2 mysql mysql    274432 Dec 28 03:07 qandol_b/
drwx------  2 mysql mysql    348160 Jan 11 01:52 qandol_2/
drwx------  2 mysql mysql    335872 Jan 12 01:46 qandol_9/
drwx------  2 mysql mysql    360448 Jan 13 04:02 qandol_f/
drwx------  2 mysql mysql    282624 Jan 13 04:17 qandol_3/
drwx------  2 mysql mysql    364544 Jan 21 21:14 qandol_1/
drwx------  2 mysql mysql    323584 Jan 25 08:17 qandol_6/
drwx------  2 mysql mysql    294912 Jan 25 14:13 qandol_e/
drwx------  2 mysql mysql    307200 Jan 27 20:21 qandol_4/
drwx------  2 mysql mysql    319488 Feb  7 06:39 qandol_8/
drwx------  2 mysql mysql      4096 Feb 15 02:59 performance_schema/
-rw-r--r--  1 mysql mysql         6 Feb 15 02:59 mysql_upgrade_info
drwx--x--x  2 mysql mysql      4096 Feb 15 02:59 mysql/
-rw-r--r--  1 mysql mysql      1286 Feb 15 02:59 RPM_UPGRADE_MARKER-LAST
-rw-r--r--  1 mysql mysql      3049 Feb 15 02:59 RPM_UPGRADE_HISTORY
drwx------  2 mysql mysql    335872 Feb 17 11:07 qandol_7/
drwx------  2 mysql mysql    307200 Feb 18 07:40 qandol_a/
drwx------  2 mysql mysql    372736 Feb 18 19:33 qandol_0/

Best Wishes
Jamshid Khaksaar
 
Enable the logging of slow queries in mysql. You probably have a site bogging it down with bad code.

Try using mytop command to see what queries are being executed or use phpmyadmin process monitor.
 
Back
Top