MySQL Forums
Forum List  »  Memory Storage Engine

tmp tables always write to disk
Posted by: Ed Sorrell
Date: July 18, 2007 09:48AM

I have different servers displaying this condition.

HP DL380 2x 3.0G CPU
4GB RAM (no swapping/plenty of ram)

Linux 2.4.21-47.0.1.ELsmp
mysqld Ver 3.23.58 for redhat-linux-gnu on i386

have_isam | YES
join_buffer_size | 671084544
key_buffer_size | 805302272
max_heap_table_size | 268434432
max_tmp_tables | 256
record_buffer | 268431360
record_rnd_buffer | 268431360
query_buffer_size | 671084544
tmp_table_size | 268435456
tmpdir | /tmp/mysqltmp/

The Query:

"select router from macstatus where mac='".$mac."' and dtype='N' group by router"

router | varchar(30) | YES | MUL | NULL
mac | varchar(17) | YES | MUL | NULL

(YES I know, why varchar? Stupid!)

Not much, searches 120,000 records, returns either 0 rows or any number of rows up to 8000.

Uptime | 76185
Created_tmp_disk_tables | 424855 |
Created_tmp_tables | 424855 |
Created_tmp_files | 0 |

Very tiny results.....

-rw-rw---- 1 mysql mysql 1024 Jul 18 11:42 #sql27f4_1689_373.MYI
-rw-rw---- 1 mysql mysql 0 Jul 18 11:42 #sql27f4_1689_373.MYD

or

-rw-rw---- 1 mysql mysql 60 Jul 18 11:43 #sql27f4_1689_4d8.MYD


Has to be something stupid, but what? Disk IO through the roof.
Have seen other forums, including here with various versions of mysql, people reporting the same condition but yet to see a resoloution.

Can some one please help me?


Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
tmp tables always write to disk
9992
July 18, 2007 09:48AM
5821
July 18, 2007 10:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.