Verzweifelung mit table_cache / temp tables on disk

S

suca

New Member
Hi Forum :-)
Ich verzweifel seit Wochen.. ach was sag ich, seit Monaten an meinem SQL-Server.
Dedicated Server, 2 Gig RAM. Performance ist ok, aber... das kann doch nicht normal sein:

Code: 
[!!] Table cache hit rate: 5% (250 open / 4K opened)

Of 134286 temp tables, 78% were created on disk
bzw.

Temporary tables created on disk: 43% (104K on disk / 239K total)

Egal was ich mache, temp table on disk und table cache ist nicht genug.
Aktuell ist meine my.cnf so:

Code: 
key_buffer = 36M
max_allowed_packet = 1M
table_cache = 512 
sort_buffer_size = 4M 
net_buffer_length = 1M
read_buffer_size = 4M
read_rnd_buffer_size = 4M 
join_buffer_size = 1M
myisam_sort_buffer_size = 64M
query_cache_size=48M
query_cache_limit=8M
query_cache_min_res_unit=4K
max_heap_table_size=128M
tmp_table_size=128M
max_connections=50

Es it völlig wurscht, ob ich bei table_cache und tmp_table_size 1MB oder 1GB eintrage - es reicht nie.
Ich würde es ja verstehen (vielleicht), wenn ich 1.000 Datenbanken laufen hätte.
Aber es sind gerade mal ~ 250 Tabellen in einem halben Dutzend DB. Neben plesk ist es eigentlich nur ein Wordpress-Blog.
Warum bitte schreibt mein Server alles auf Disk? Es ist mir wirklich zu hoch...
Irgendwelche Ideen, was ich wo schrauben könnte?

Hier nochmal ein vollständiges tuner-log:

Code: 
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2 sec.
You have 0 out of 1750646 that take longer than 2 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.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 24
Current threads_cached = 10
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 50
Current threads_connected = 1
Historic max_used_connections = 11
The number of used connections is 22% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 239 M
Configured Max Per-thread Buffers : 661 M
Configured Max Global Buffers : 94 M
Configured Max Memory Limit : 755 M
Physical Memory : 1.95 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 10 M
Current key_buffer_size = 36 M
Key cache miss rate is 1 : 2924
Key buffer fill ratio = 25.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 48 M
Current query_cache_used = 3 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 8.14 %
Current query_cache_min_res_unit = 4 K
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 = 4 M
Current read_rnd_buffer_size = 3 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 44 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 = 1084 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_cache value = 512 tables
You have a total of 232 tables
You have 250 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 134533 temp tables, 77% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your 
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 3 M
Current table scan ratio = 283 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 14160
Your table locking seems to be fine

Danke im vorraus :-)
 
Check die Table

Code: 
TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 134533 temp tables, 77% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your 
ratio of on disk temp tables.
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

Das gilt es zu überprüfen, dann weißt bescheid.
 
Hmmm...
Klar, sowohl Plesk als auch Wordpress hat die ein oder andere Tabelle, die text und longtext Felder beinhaltet.
Wenn das die "schuldigen" sind, dann ist es halt so...

Und der lausig genutze Table Cache ist auch damit zu verbinden?

Code: 
Open_tables 377  Anzahl der geöffneten Tabellen.  
Opened_tables 5,892  Anzahl der Tabellen, die geöffnet wurden. Wenn Opened_tables hoch ist, ist Ihre table_cache-Variable wahrscheinlich zu niedrig.
 
You must log in or register to reply here.
Back
Top