[MySQL]Analiza logów Opcje

[aras785]

Witam. Posiadam serwer VPS CLOUD 4 (https://www.ovh.pl/vps/vps-cloud.xml) na OVH i od początku mam z nim problemy z MYSQL. Od tygodnia procesor jest na max obrotach. Sprawdzałem procesy i MYSQL sięga 100% i ponad nawet... Ludzie online jest może z 20... Jest to sklep internetowy z bazą 10 000 produktami...

Chciałbym abyście napisali mi jak mam podejść do tematu, czyli najpierw które logi sprawdzić itd.

Mój plik my.cnf

Kod

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
interactive_timeout=180
wait_timeout=180

max_connections=100
query_cache_size=256M
key_buffer_size=256M
sort_buffer_size=20M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
query_cache_type=1

Proszę o pomoc

[aras785]

A co myślicie o my.cnf (IMG:style_emoticons/default/questionmark.gif)

Zapytaniem spróbuję powalczyć ale one jest długie ponieważ od razu przelicza cenę po prowizji itd.

Lepiej rozłożyć na dwa zapytania? i policzyć to w PHP?

Wynik z mysqltuner:

Kod

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 410M (Tables: 1232)
[--] Data in InnoDB tables: 21M (Tables: 676)
[!!] Total fragmented tables: 733

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5m 37s (44K q [133.240 qps], 114 conn, TX: 121M, RX: 10M)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 442.0M global + 12.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.3G (38% of installed RAM)
[OK] Slow queries: 0% (7/44K)
[OK] Highest usage of available connections: 58% (89/151)
[OK] Key buffer size / total MyISAM indexes: 384.0M/121.1M
[OK] Key buffer hit rate: 100.0% (61M cached / 11K reads)
[OK] Query cache efficiency: 69.6% (28K cached / 40K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 142K sorts)
[!!] Joins performed without indexes: 3
[OK] Temporary tables created on disk: 12% (615 on disk / 5K total)
[!!] Thread cache hit rate: 14% (98 created / 114 connections)
[OK] Table cache hit rate: 97% (339 open / 346 opened)
[OK] Open file limit used: 46% (554/1K)
[OK] Table locks acquired immediately: 99% (21K immediate / 21K locks)
[!!] InnoDB  buffer pool / data size: 8.0M/21.5M
[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
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (> 8)
    innodb_buffer_pool_size (>= 21M)

TOP:
(IMG:http://i.imgur.com/cD8lxvx.jpg)

Ten post edytował aras785 3.04.2014, 11:13:20