MySQL schreibt ALLE temp. Tabellen auf HDD

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

MySQL schreibt ALLE temp. Tabellen auf HDD

Post by mr_vista »

Hallo,

obwohl ich die entscheidenen Variablen groß genug gesetzt hab (tmp table size auf 2G), schreibt MySQL alle temporären Tabellen auf die Festplatte :(

zur Stoßzeit sind dies schon mal einige hundert Tabellen oder mehr pro Minute.

Hat jemand einen Tipp für mich warum das so sein könnte? Danke.


MySQL Version ist 4.1.10a


hier mal gleich die variablen:
    back log 50 50 basedir /usr/ /usr/ binlog cache size 32768 32768 bulk insert buffer size 8388608 8388608 character set client utf8 latin1 character set connection utf8 latin1 character set database latin1 latin1 character set results utf8 latin1 character set server latin1 latin1 character set system utf8 utf8 character sets dir /usr/share/mysql/charsets/ /usr/share/mysql/charsets/ collation connection utf8_general_ci latin1_swedish_ci collation database latin1_swedish_ci latin1_swedish_ci collation server latin1_swedish_ci latin1_swedish_ci concurrent insert ON ON connect timeout 5 5 datadir /var/lib/mysql/ /var/lib/mysql/ date format %Y-%m-%d %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s %Y-%m-%d %H:%i:%s default week format 0 0 delay key write ON ON delayed insert limit 100 100 delayed insert timeout 300 300 delayed queue size 1000 1000 expire logs days 0 0 flush OFF OFF flush time 0 0 ft boolean syntax + -><()~*:""&| + -><()~*:""&| ft max word len 84 84 ft min word len 4 4 ft query expansion limit 20 20 ft stopword file (built-in) (built-in) group concat max len 1024 1024 have archive NO NO have bdb NO NO have compress YES YES have crypt YES YES have csv NO NO have example engine NO NO have geometry YES YES have innodb YES YES have isam YES YES have ndbcluster NO NO have openssl NO NO have query cache YES YES have raid NO NO have rtree keys YES YES have symlink YES YES init connect init file init slave innodb additional mem pool size 1048576 1048576 innodb autoextend increment 8 8 innodb buffer pool awe mem mb 0 0 innodb buffer pool size 8388608 8388608 innodb data file path ibdata1:10M:autoextend ibdata1:10M:autoextend innodb data home dir innodb fast shutdown ON ON innodb file io threads 4 4 innodb file per table OFF OFF innodb flush log at trx commit 1 1 innodb flush method innodb force recovery 0 0 innodb lock wait timeout 50 50 innodb locks unsafe for binlog OFF OFF innodb log arch dir innodb log archive OFF OFF innodb log buffer size 1048576 1048576 innodb log file size 5242880 5242880 innodb log files in group 2 2 innodb log group home dir ./ ./ innodb max dirty pages pct 90 90 innodb max purge lag 0 0 innodb mirrored log groups 1 1 innodb open files 300 300 innodb table locks ON ON innodb thread concurrency 8 8 interactive timeout 28800 28800 [b]join buffer size 67104768 67104768 key buffer size 134217728 134217728[/b] key cache age threshold 300 300 key cache block size 1024 1024 key cache division limit 100 100 language /usr/share/mysql/english/ /usr/share/mysql/english/ large files support ON ON license GPL GPL local infile ON ON locked in memory OFF OFF log OFF OFF log bin OFF OFF log error log slave updates OFF OFF log slow queries OFF OFF log update OFF OFF log warnings 1 1 long query time 10 10 low priority updates OFF OFF lower case file system OFF OFF lower case table names 0 0 max allowed packet 10484736 10484736 max binlog cache size 4294967295 4294967295 max binlog size 1073741824 1073741824 max connect errors 10 10 max connections 500 500 max delayed threads 20 20 max error count 64 64 [b]max heap table size 2147482624 2147482624[/b] max insert delayed threads 20 20 [b]max join size 4294967295 4294967295[/b] max length for sort data 1024 1024 max relay log size 0 0 max seeks for key 4294967295 4294967295 max sort length 1024 1024 max tmp tables 32 32 max user connections 0 0 max write lock count 4294967295 4294967295 myisam data pointer size 4 4 myisam max extra sort file size 2147483648 2147483648 myisam max sort file size 2147483647 2147483647 myisam recover options OFF OFF myisam repair threads 1 1 myisam sort buffer size 33554432 33554432 net buffer length 10240 10240 net read timeout 30 30 net retry count 10 10 net write timeout 60 60 new OFF OFF old passwords ON ON open files limit 16558 16558 pid file /var/lib/mysql/mysqld.pid /var/lib/mysql/mysqld.pid port 3306 3306 preload buffer size 32768 32768 protocol version 10 10 query alloc block size 8192 8192 query cache limit 1048576 1048576 query cache min res unit 4096 4096 query cache size 3145728 3145728 query cache type ON ON query cache wlock invalidate OFF OFF query prealloc size 8192 8192 range alloc block size 2048 2048 [b]read buffer size 1044480 1044480[/b] read only OFF OFF [b]read rnd buffer size 10481664 10481664[/b] relay log purge ON ON rpl recovery rank 0 0 secure auth OFF OFF server id 1 1 skip external locking ON ON skip networking OFF OFF skip show database OFF OFF slave net timeout 3600 3600 slow launch time 2 2 socket /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock [b]sort buffer size 33554424 33554424[/b] sql mode storage engine MyISAM MyISAM sync binlog 0 0 sync replication 0 0 sync replication slave id 0 0 sync replication timeout 0 0 sync frm ON ON system time zone CEST CEST [b]table cache 8024 8024[/b] table type MyISAM MyISAM thread cache size 30 30 thread stack 196608 196608 time format %H:%i:%s %H:%i:%s time zone SYSTEM SYSTEM [b]tmp table size 2671771648 2671771648[/b] tmpdir transaction alloc block size 8192 8192 transaction prealloc size 4096 4096 tx isolation REPEATABLE-READ REPEATABLE-READ version 4.1.10a 4.1.10a version comment SUSE MySQL RPM SUSE MySQL RPM version compile machine i686 i686 version compile os suse-linux suse-linux wait timeout 28800 28800
Die Werte sind so gering, da sie aus den frühen Morgenstunden stammen und MySQL noch nicht lange lief... aber 605 von 605 Tabellen auf der Festplatte ist ja eindeutig.

Statusvariablen:
    Binlog cache disk use 0 Binlog cache use 0 [b]Created tmp disk tables 605[/b] Created tmp files 3 [b]Created tmp tables 605[/b] Delayed errors 0 Delayed insert threads 0 Delayed writes 0 Flush commands 1 Handler commit 0 Handler delete 59 Handler discover 0 Handler read first 57 Handler read key 68347 Handler read next 718923 Handler read prev 25496 Handler read rnd 3949 Handler read rnd next 6022 Handler rollback 0 Handler update 19396 Handler write 4264 Key blocks not flushed 4 Key blocks unused 111123 Key blocks used 4857 Key read requests 230306 Key reads 5141 Key write requests 4149 Key writes 477 Max used connections 2 Not flushed delayed rows 0 Open files 81 Open streams 0 Open tables 43 Opened tables 54 Qcache free blocks 178 Qcache free memory 2799896 Qcache hits 2254 Qcache inserts 3794 Qcache lowmem prunes 0 Qcache not cached 3406 Qcache queries in cache 301 Qcache total blocks 799 Rpl status NULL Select full join 0 Select full range join 0 Select range 55 Select range check 0 Select scan 237 Slave open temp tables 0 Slave running OFF Slow launch threads 0 Slow queries 0 Sort merge passes 0 Sort range 186 Sort rows 5465 Sort scan 605 Table locks immediate 12771 Table locks waited 1 Threads cached 1 Threads connected 1 Threads created 2 Threads running 1
Top

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: MySQL schreibt ALLE temp. Tabellen auf HDD

Post by mr_vista »

[ gelöst ]

nachdem ich ungefähr das ganze internet durchgelesen habe, hab ich die lösung gefunden...
"--big-tables
Große Ergebnismengen zulassen, indem alle temporären Mengen in eine
Datei gesichert werden. Das löst die meisten
'table full'
-Fehler, ver-
langsamt aber in den Fällen Anfragen, in denen Tabellen im Speicher
ausreichen würden. Ab Version 3.23.2 ist MySQL in der Lage, das Problem
automatisch zu lösen, indem für kleine temporäre Tabellen der Arbeits-
speicher benutzt wird und auf Festplatten-Tabellen nur dann umge-
schaltet wird, wenn das nötig ist. Die Option wird daher faktisch nicht
mehr benötigt."
das heisst, wenn "big-tables" in der my.cnf steht, werden alle temporären tabellen auf die festplatte geschrieben... also weg damit...
    Created tmp disk tables 0 Created tmp files 3 Created tmp tables 5959
Top