my.cnf - tuning-primer was ist machbar?

MySQL, PostgreSQL, SQLite
fulltilt
Posts: 363
Joined: 2006-08-27 02:06

my.cnf - tuning-primer was ist machbar?

Post by fulltilt » 2010-10-06 13:44

tuning-primer bzw. optimierung was sollte verbessert werden?

Code: Select all

Tasks: 210 total,   2 running, 208 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.0%us,  0.5%sy,  0.0%ni, 97.4%id,  0.8%wa,  0.2%hi,  0.2%si,  0.0%st
Mem:   6077376k total,  6040020k used,    37356k free,   638292k buffers
Swap:  4200988k total,    54164k used,  4146824k free,  2444568k cached


sind diese optimierungesvorschläge nicht völlig überzogen?

Code: Select all

QUERY CACHE
Query cache is enabled                                                                           
Current query_cache_size = 24 M                                                                   
Current query_cache_used = 19 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 81.24 %
Current query_cache_min_res_unit = 4 K
However, 20819 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size                   
                                                                                                 
                                                                                                 
JOINS
Current join_buffer_size = 2.00 M                                                                 
You have had 662 queries where a join could not use an index properly
You have had 27 joins without keys that check for key usage after each row
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 = 36110 files                                                           
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.                                               
You currently have open more than 75% of your open_files_limit                                   
You should set a higher value for open_files_limit in my.cnf                                     
                                                                                                 
TABLE CACHE
Current table_cache value = 18000 tables                                                         
You have a total of 10764 tables
You have 18000 open tables.
Current table_cache hit rate is 37%, while 100% of your table cache is in use
You should probably increase your table_cache
                                                                                                 
TEMP TABLES
Current max_heap_table_size = 64 M                                                               
Current tmp_table_size = 64 M
Of 39133 temp tables, 60% 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.

User avatar
Joe User
Project Manager
Project Manager
Posts: 11598
Joined: 2003-02-27 01:00
Location: Hamburg

Re: my.cnf - tuning-primer was ist machbar?

Post by Joe User » 2010-10-06 14:15

Bitte zusätzlich einmal die vollständige Ausgabe von mysqltuner.pl
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

fulltilt
Posts: 363
Joined: 2006-08-27 02:06

Re: my.cnf - tuning-primer was ist machbar?

Post by fulltilt » 2010-10-06 14:29

hier der mysqltuner outout:

Code: Select all

 >>  MySQLTuner 1.0.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.0.51a-24+lenny4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 236M (Tables: 10460)
[--] Data in InnoDB tables: 23M (Tables: 277)
[--] Data in MEMORY tables: 3M (Tables: 10)
[!!] Total fragmented tables: 58

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4h 12m 34s (564K q [37.269 qps], 11K conn, TX: 1B, RX: 84M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 114.0M global + 14.1M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.5G (25% of installed RAM)
[OK] Slow queries: 5% (30K/564K)
[OK] Highest usage of available connections: 11% (11/100)
[!!] Key buffer size / total MyISAM indexes: 16.0M/101.3M
[!!] Key buffer hit rate: 83.7% (1M cached / 210K reads)
[OK] Query cache efficiency: 73.6% (275K cached / 374K selects)
[!!] Query cache prunes per day: 157594
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 17K sorts)
[!!] Joins performed without indexes: 940
[!!] Temporary tables created on disk: 37% (23K on disk / 64K total)
[OK] Thread cache hit rate: 99% (11 created / 11K connections)
[OK] Table cache hit rate: 37% (18K open / 47K opened)
[OK] Open file limit used: 77% (27K/36K)
[OK] Table locks acquired immediately: 99% (200K immediate / 200K locks)
[!!] InnoDB data size / buffer pool: 23.4M/8.0M

-------- 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
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    key_buffer_size (> 101.3M)
    query_cache_size (> 24M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    innodb_buffer_pool_size (>= 23M)


User avatar
Joe User
Project Manager
Project Manager
Posts: 11598
Joined: 2003-02-27 01:00
Location: Hamburg

Re: my.cnf - tuning-primer was ist machbar?

Post by Joe User » 2010-10-06 15:03

fulltilt wrote:

Code: Select all

Variables to adjust:
    key_buffer_size (> 101.3M)
    query_cache_size (> 24M)
    innodb_buffer_pool_size (>= 23M)

Die drei Werte solltest Du auf jeden Fall erstmal anpassen.
Mein Vorschlag:

Code: Select all

key_buffer                      = 160M
query_cache_type                = 1
query_cache_size                = 128M
query_cache_limit               = 16M
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 512M
innodb_additional_mem_pool_size = 64M
innodb_data_home_dir            = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_data_file_path           = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_file_size            = 64M
innodb_log_buffer_size          = 8M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table           = 1

Vorher bitte wie in viewtopic.php?f=104&t=36343 beschrieben ein Backup anlegen und die InnoDB-Files löschen.

Beim Neustart des MySQLd es ein paar Minuten dauern, bis die InnoDB-Files neu anglegt sind und zu einer falschen Fehlermeldung des Init-Scripts führen kann.

Code: Select all

tail -f /var/log/mysql/mysqld.err

Du solltest daher warten bis im tail -f eine Zeile ähnlich der folgenden erscheint (Beenden mittels ^C (STRG+C)):

Code: Select all

Version: '5.1.50-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-5.1.50-r1
Last edited by Joe User on 2010-10-06 15:06, edited 1 time in total.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

fulltilt
Posts: 363
Joined: 2006-08-27 02:06

Re: my.cnf - tuning-primer was ist machbar?

Post by fulltilt » 2010-10-06 17:46

danke :-D
werds gleich mit dieser config testen.