Skyuser
Member
Recently i started to monitor my server activity because pages with 20 posts ( large threads) opens very slowly.
Page Time: 1.3822s
Memory: 4.1461 MB (Peak: 5.3929 MB)
Queries (10, time: 0.0136s, 1.0%)
I have DigitalOcean VPS with 1 Gb Ram, nginx 1.4.7 with ngx_pagespeed + Apache, MySQL 5.5.6, PHP 5.4 and APC.
My.cnf settings:
tuning-primer output:
mysqltuner.pl output
Here i have 1 question - Should i run OPTIMIZE TABLE every time i got this message? Does this command increase the size of my DB?
httpd.conf settings:
apc.ini settings:
I would really appreciate if you can help me with my problem 
Page Time: 1.3822s
Memory: 4.1461 MB (Peak: 5.3929 MB)
Queries (10, time: 0.0136s, 1.0%)
I have DigitalOcean VPS with 1 Gb Ram, nginx 1.4.7 with ngx_pagespeed + Apache, MySQL 5.5.6, PHP 5.4 and APC.
My.cnf settings:
Code:
character_set_server=utf8
collation_server=utf8_unicode_ci
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_size = 128M
query_cache_limit = 2M
thread_cache_size = 4
innodb_buffer_pool_size = 300M
innodb_additional_mem_pool_size = 15M
innodb_flush_log_at_trx_commit = 2
log-slow-queries = /var/log/mysqlslowlog/slow.logtuning-primer output:
Code:
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 4925 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 4
Current threads_cached = 3
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 5
The number of used connections is 3% 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 = 19 M
Current InnoDB data space = 60 M
Current InnoDB buffer pool free = 89 %
Current innodb_buffer_pool_size = 300 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 : 472 M
Configured Max Per-thread Buffers : 405 M
Configured Max Global Buffers : 459 M
Configured Max Memory Limit : 864 M
Physical Memory : 1006 M
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 4 M
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 55
Key buffer free ratio = 87 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 1 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 1.47 %
Current query_cache_min_res_unit = 4 K
Query Cache is 23 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
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 = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 79 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 1024 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 = 400 tables
Current table_definition_cache = 400 tables
You have a total of 229 tables
You have 250 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 428 temp tables, 15% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 26 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 0 : 5220
Your table locking seems to be finemysqltuner.pl output
Code:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 5M (Tables: 8)
[--] Data in InnoDB tables: 60M (Tables: 176)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 504K (Tables: 4)
[!!] Total fragmented tables: 178
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 25m 3s (5K q [3.918 qps], 644 conn, TX: 33M, RX: 2M)
[--] Reads / Writes: 58% / 42%
[--] Total buffers: 475.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 880.8M (87% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/5.0M
[OK] Key buffer hit rate: 98.3% (6K cached / 104 reads)
[OK] Query cache efficiency: 40.3% (1K cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 187 sorts)
[!!] Joins performed without indexes: 95
[OK] Temporary tables created on disk: 15% (87 on disk / 567 total)
[OK] Thread cache hit rate: 99% (6 created / 644 connections)
[OK] Table cache hit rate: 51% (252 open / 494 opened)
[OK] Open file limit used: 6% (64/1K)
[OK] Table locks acquired immediately: 100% (8K immediate / 8K locks)
[OK] InnoDB buffer pool / data size: 300.0M/60.3M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)Here i have 1 question - Should i run OPTIMIZE TABLE every time i got this message? Does this command increase the size of my DB?
httpd.conf settings:
Code:
# prefork MPM
<IfModule prefork.c>
StartServers 4
MinSpareServers 5
MaxSpareServers 10
ServerLimit 50
MaxClients 25
MaxRequestsPerChild 1000
</IfModule>
# worker MPM
<IfModule worker.c>
StartServers 4
MaxClients 25
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 1000
</IfModule>apc.ini settings:
Code:
extension=apc.so
apc.enabled=1
apc.shm_segments=1
apc.shm_size=128M
apc.num_files_hint=7000
apc.user_entries_hint=4096
;apc.ttl=7200
;apc.user_ttl=7200
apc.gc_ttl=3600
apc.optimization = 2
apc.slam_defense=0
apc.stat=1
apc.include_once_override=0
apc.enable_cli=0
apc.file_update_protection=2
apc.max_file_size=2M
