Optimizing mysql via tuner and mysqltuner


#1

im currently optimizing my database since I always having database connection error :frowning: here are some of my info

this one is the result of the tuning primer, how do I edit the recommendatios?

    MAX CONNECTIONS                                                                       
    Current max_connections = 151                                                         
    Current threads_connected = 7                                                         
    Historic max_used_connections = 7                                                     
    The number of used connections is 4% of the configured maximum.                       
    You are using less than 10% of your configured max_connections.                       
    Lowering max_connections could help to avoid an over-allocation of memory             
    See "MEMORY USAGE" section to make sure you are not over-allocating                   
                                                                                          
    INNODB STATUS                                                                         
    Current InnoDB index space = 816 K                                                    
    Current InnoDB data space = 5 M                                                       
    Current InnoDB buffer pool free = 93 %                                                
    Current innodb_buffer_pool_size = 128 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 : 177 M                                                     
    Configured Max Per-thread Buffers : 160 M                                             
    Configured Max Global Buffers : 170 M                                                 
    Configured Max Memory Limit : 330 M                                                   
    Physical Memory : 990 M                                                               
    Max memory limit seem to be within acceptable norms                                   
                                                                                          
    KEY BUFFER                                                                            
    Current MyISAM index space = 6 M                                                      
    Current key_buffer_size = 16 M                                                        
    Key cache miss rate is 1 : 21                                                         
    Key buffer free ratio = 77 %                                                          
    Your key_buffer_size seems to be fine                                                 
                                                                                          
    QUERY CACHE                                                                           
    Query cache is enabled                                                                
    Current query_cache_size = 10 M                                                       
    Current query_cache_used = 17 K                                                       
    Current query_cache_limit = 11 M                                                      
    Current Query cache Memory fill ratio = .16 %                                         
    Current query_cache_min_res_unit = 4 K                                                
    <img src="/uploads/default/2324/c5d3ab10a9286f0c.jpg" width="494" height="500"> 

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 K                                                      
Current read_rnd_buffer_size = 256 K                                                  
Sort buffer seems to be fine                                                          
                                                                                      
JOINS                                                                                 
Current join_buffer_size = 260.00 K                                                   
You have had 0 queries where a join could not use an index properly                   
Your joins seem to be using indexes properly                                          
                                                                                      
OPEN FILES LIMIT                                                                      
Current open_files_limit = 5000 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 = 1400 tables                                          
You have a total of 113 tables                                                        
You have 215 open tables.                                                             
The table_cache value seems to be fine                                                
                                                                                      
TEMP TABLES                                                                           
Current max_heap_table_size = 16 M                                                    
Current tmp_table_size = 16 M                                                         
Of 792 temp tables, 14% were created on disk                                          
Created disk tmp tables ratio seems fine                                              
                                                                                      
TABLE SCANS                                                                           
Current read_buffer_size = 128 K                                                      
Current table scan ratio = 50 : 1                                                     
read_buffer_size seems to be fine                                                     
                                                                                      
TABLE LOCKING                                                                         
Current Lock Wait ratio = 0 : 1367                                                    
Your table locking seems to be fine                                                   

and for the mysqltuner result

 >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>                                                 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/                           
 >>  Run with '--help' for additional options and output filtering                                    
                                                                                                      
-------- General Statistics --------------------------------------------------                         [--] Skipped version check for MySQLTuner script                       [OK] Currently running supported MySQL version 5.6.22-72.0             [OK] Operating on 64-bit architecture                                 

                                                                                                      
-------- Storage Engine Statistics -------------------------------------------                         [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster        [--] Data in InnoDB tables: 5M (Tables: 39)                            [--] Data in MyISAM tables: 27M (Tables: 74)                           [!!] Total fragmented tables: 1                                       

                                                                                                      
-------- Security Recommendations  -------------------------------------------                         ERROR 1142 (42000) at line 1: SELECT command denied to user 'pcconnec'@'localhost' for table 'user'    [OK] All database users have passwords assigned                                               

                                                                                                      
-------- Performance Metrics -------------------------------------------------                         [--] Up for: 7s (587 q [83.857 qps], 117 conn, TX: 4M, RX: 72K)        [--] Reads / Writes: 100% / 0%                                         [--] Total buffers: 186.0M global + 1.1M per thread (151 max threads)  [OK] Maximum possible memory usage: 346.4M (34% of installed RAM)      [OK] Slow queries: 0% (0/587)                                          [OK] Highest usage of available connections: 1% (3/151)                [OK] Key buffer size / total MyISAM indexes: 16.0M/6.2M                [!!] Key buffer hit rate: 90.9% (35K cached / 3K reads)                [!!] Query cache efficiency: 0.0% (0 cached / 464 selects)             [OK] Query cache prunes per day: 0                                     [OK] Sorts requiring temporary tables: 2% (2 temp sorts / 84 sorts)    [OK] Temporary tables created on disk: 15% (72 on disk / 457 total)    [OK] Thread cache hit rate: 97% (3 created / 117 connections)          [OK] Table cache hit rate: 24% (213 open / 857 opened)                 [OK] Open file limit used: 3% (196/5K)                                 [OK] Table locks acquired immediately: 100% (482 immediate / 482 locks)                                [OK] InnoDB data size / buffer pool: 5.8M/128.0M                                                     

                                                                                                      
-------- Recommendations -----------------------------------------------------                         General recommendations:                                              

    Run OPTIMIZE TABLE to defragment tables for better performance                                    
    MySQL started within last 24 hours - recommendations may be inaccurate                             Variables to adjust:           

    query_cache_limit (> 11M, or use smaller result sets)

#2

i dont think tuning your database will fix anything.

your database settings are fine.

also, you did see "MySQL started within last 24 hours - recommendations may be inaccurate " in your mysqltuner right?

you should be checking your error logs


#3

where is the location of the error logs in easyengine?


#4

/var/log/nginx


#5

Hi, @rnovino,

EE error Log location is /var/log/ee/error.log.


#6

I dont have that file on the var log ee


#7

@rnovino, ok,

check this

/var/log/ee/ee.log


#9

off topic : what framework or system are you using with this community forum? thanks in advance :slight_smile:


#10

Hi @rnovino

It’s been a long time, and we haven’t heard from you. It looks like your issue is resolved.

I am closing this support topic for now. Feel free to create a new support topic if you have any queries further. :slight_smile:


#11