tmp_table_size

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

tmp_table_size

Post by fulltilt »

versuche hier gerade mittels tuning-primer zu optimieren, hierbei wird bemängelt:

Code: Select all

Current max_heap_table_size = 356 M
Current tmp_table_size = 356 M
Of 3561 temp tables, 53% 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


sollte ich beide Parameter noch erhöhen bzw. was kann ich verbessern?

Code: Select all

Mem:   4117176k total,  3805324k used,   311852k free,   291528k buffers
Swap:  4200988k total,      480k used,  4200508k free,  2119664k cached


Code: Select all

key_buffer      = 32M
max_allowed_packet   = 2M
thread_stack      = 128K
thread_cache_size       = 10
myisam-recover         = BACKUP
table_cache            = 12000
open_files_limit       = 29000
thread_concurrency     = 2
sort_buffer_size       = 1M
read_buffer_size       = 1M
read_rnd_buffer_size   = 768K
join_buffer_size        = 4M
max_heap_table_size       = 356M
tmp_table_size            = 356M
max_connect_errors     = 10
#max_connections        = 900
interactive_timeout    = 100
wait_timeout           = 100
connect_timeout        = 10
query_cache_limit   = 1M
query_cache_size        = 32M
Top

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

Re: tmp_table_size

Post by fulltilt »

Hm - ich denke auch das es momentan etwas hoch angesetzt ist ...
also die connections aus der Vergangenheit lagen maximal einmal bei 70.
Hier die kompletten Infos und Ausgabe von Primer:

Code: Select all

MySQL Version 5.0.67-0.dotdeb.1 i486
auf Debian Etch 32bit

Uptime = 0 days 1 hrs 8 min 51 sec
Avg. qps = 146
Total Questions = 606443
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 1 out of 606464 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

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 = 10
Current threads_cached = 5
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 6
The number of used connections is 6% 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

MEMORY USAGE
Max Memory Ever Allocated : 115 M
Configured Max Per-thread Buffers : 687 M
Configured Max Global Buffers : 74 M
Configured Max Memory Limit : 761 M
Physical Memory : 3.92 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 125 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 730
Key buffer fill ratio = 16.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 = 32 M
Current query_cache_used = 22 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 71.44 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 768 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 241 queries where a join could not use an index properly
You have had 10 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 29000 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 = 12000 tables
You have a total of 10177 tables
You have 10327 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 356 M
Current tmp_table_size = 356 M
Of 6086 temp tables, 55% 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 = 1 M
Current table scan ratio = 35 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 607662
Your table locking seems to be fine


my.cnf

Code: Select all

[client]
port      = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice      = 0

[mysqld]
#
# * Basic Settings
#
user      = mysql
pid-file   = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port      = 3306
basedir      = /usr
datadir      = /var/lib/mysql
tmpdir      = /tmp
language   = /usr/share/mysql/english
skip-external-locking
#
# For compatibility to other Debian packages that still use
# libmysqlclient10 and libmysqlclient12, set old_passwords to 1.
old_passwords   = 0
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address      = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 32M
max_allowed_packet   = 2M
thread_stack      = 128K
thread_cache_size       = 10
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
table_cache            = 12000
open_files_limit       = 29000
thread_concurrency     = 2
sort_buffer_size       = 1M
read_buffer_size       = 1M
read_rnd_buffer_size   = 768K
join_buffer_size        = 4M
max_heap_table_size       = 356M
tmp_table_size            = 356M
max_connect_errors     = 10
#max_connections        = 900
interactive_timeout    = 100
wait_timeout           = 100
connect_timeout        = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 32M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#log_type           = FILE
#general_log      = /var/log/mysql/mysql.log
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days   = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet   = 16M

[mysql]
[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/
Top

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

Re: tmp_table_size

Post by fulltilt »

Danke Dir - werde Primer morgen noch mal ausführen und hier posten.
Es ist momentan nicht wirklich problematisch - ich habe die settings heute nach 3 Monaten mal wieder etwas angepasst weil viele Datenbanken und auch grosse hinzugekommen sind.
Was mich hier nur etwas stutzig macht das Tuning Primer von mir möchte das die Werte für tmp_table_size and/or max_heap_table_size höher sein sollen ...
Sollte ich die query_cache_size = 32M jetzt gleich noch reduzieren?
Grüsse
Last edited by fulltilt on 2009-12-03 16:07, edited 1 time in total.
Top

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

Re: tmp_table_size

Post by Joe User »

fulltilt wrote:

Code: Select all

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.

Den Hinweis (Note) hast Du bedacht?
Zudem solltest Du die Queries mit den unindexierten Sorts überprüfen und passende Indexe setzen, oder die Queries durch Umformulieren optimieren.
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.
Top

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

Re: tmp_table_size

Post by fulltilt »

Hier die aktuelle Ausgabe von tuning-primer - es waren jetzt nicht genau 24 St. aber sehr viel Traffic heute ...
Was ich zwischenzeitlich beobachten konnte, war das die CPU Last bei mysql ganz kurz mal auf 50-99% kommt, klingt aber sofort wieder ab.
Ich denke das die Resourcen für die mysql config nicht ganz ausreichend sind:

Code: Select all

MySQL Version 5.0.67-0.dotdeb.1 i486

Uptime = 0 days 18 hrs 19 min 25 sec
Avg. qps = 83
Total Questions = 5536842
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 4 out of 5536863 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

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 = 10
Current threads_cached = 8
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 2
Historic max_used_connections = 14
The number of used connections is 14% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 170 M
Configured Max Per-thread Buffers : 687 M
Configured Max Global Buffers : 74 M
Configured Max Memory Limit : 761 M
Physical Memory : 3.92 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 123 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 181
Key buffer fill ratio = 59.00 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 24 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 75.15 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 768 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 2813 queries where a join could not use an index properly
You have had 57 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 29000 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 = 12000 tables
You have a total of 10174 tables
You have 10376 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 356 M
Current tmp_table_size = 356 M
Of 113652 temp tables, 61% 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 = 1 M
Current table scan ratio = 68 : 1
read_buffer_size seems to be fine

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

Top

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

Re: tmp_table_size

Post by fulltilt »

Habe hier immer noch Probleme mit TEMP TABLES ...
Bin nach den Empfehlungen von tuning-primer vorgegangen, mitlerweile bin ich bei max_heap_table_size und tmp_table_size auf 1.00 G.
Wenn mysql startet oder längere Abfrgaen laufen geht die Load ziemlich hoch, jetzt empfiehlt mir tuning-primer nochmals die TEMP TABLES höher einzustellen - was würdet Ihr mir empfehlen?

Mem: 4117176k total, 3127740k used, 989436k free, 271712k buffers
Swap: 4200988k total, 0k used, 4200988k free, 1761068k cached

Hier mal der output:

Code: Select all

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2 sec.
You have 427612 out of 15745317 that take longer than 2 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled

WORKER THREADS
Current thread_cache_size = 32
Current threads_cached = 28
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 2
Historic max_used_connections = 30
The number of used connections is 30% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 600 M
Configured Max Per-thread Buffers : 1 G
Configured Max Global Buffers : 154 M
Configured Max Memory Limit : 1 G
Physical Memory : 3.92 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 141 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 115
Key buffer fill ratio = 100.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 10 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 63.93 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 764 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 12.00 M
You have had 20396 queries where a join could not use an index properly
You have had 405 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 29000 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 = 11000 tables
You have a total of 10237 tables
You have 10308 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 1.00 G
Current tmp_table_size = 1.00 G
Of 357884 temp tables, 37% 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 = 1020 K
Current table scan ratio = 91 : 1
read_buffer_size seems to be fine

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

Top

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

Re: tmp_table_size

Post by fulltilt »

hm - also die tmp-tables wieder runter ...
Welches vernünftigen Wert würdest Du hier empfehlen?
Grüsse
Top

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

Re: tmp_table_size

Post by Joe User »

64M sind für alle gängigen 08/15-Webapps völlig ausreichend.
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.
Top

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

Re: tmp_table_size

Post by fulltilt »

habe mal die tmp-table sizes auf 128 und openfiles limit auf 23000 gesetzt, der mysql Dump ist etwa 560MB gross und die grösste DB etwa 190MB ...
Zabbix ist installiert, werde mal schauen ob ich die tmp_tables damit überwache.
Top