Also, do you know if there is a size limit on the VARCHAR field regarding this situation because I changed my mediumtext fields to VARCHAR(17000) (longest body in the table) because I read that MySQL wont write to heap / memory if there is a TEXT / BLOB field in the table for the tmp table and I didn't know if that just made things worse / better / no difference at all.

What settings do you have on the:
sort_buffer_size

Usually I connect a lot of temporary tables with the sort_buffer_size.

I have it set to 64M currently. For the sake of testing I'll change this number to 512M and see if that produces any less tmp tables on disk. After 12 hours there have been roughly 31k tmp tables. I'll let you know how it ends up. )

Initially the prognosis is the same. 100% of the tmp tables are still being written to disk from everything i can see. RAM usage is still very low. It's quite frustrating.

Ok, what are your settings for:
tmp_table_size
heap_table_size
?

The lower of these two will set the limit for the size of in memory tables.

From the documentation:
"To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently."

I.e. if you sort on a column which isn't a part of the index, or it's a part of the index which isn't used in the query conditions, or your query has something like ORDER BY col1 ASC, col2 DESC, a temporary table will be needed regardless of tmp_table_size setting.
Tracking such queries isn't easy, but most probably such queries will be slow so you can look for them in the slow query log.

[mysqld]# Server Configserver-id = 1port = 3306socket = /var/lib/mysql/mysql.sockuser = mysqllog-error = mysql-err.loginit-file = /var/lib/mysql/startup.sqlcharacter_set_server = utf8collation_server = utf8_general_cidefault-storage-engine = InnoDBlog-slow-queries = /var/lib/mysql/mysql-slow.logtmpdir = /var/tmp/skip-lockingskip-bdbskip-name-resolve#skip-networkingbig-tables# Miscellaneous Configopen_files_limit = 2048 # number of tables and threads in cachethread_stack = 128Kthread_concurrency = 8wait_timeout = 300interactive_timeout = 300max_delayed_threads = 200delay_key_write = OFFmax_connections = 100long_query_time = 3max_allowed_packet = 32M # (max of 1GB, should be the size of the largest blob.)#ft_min_word_len = 3#thread_concurrency = 4# Cache Settings# -- table cache is not used for innodb tablestable_cache = 1024 # default is 64, max is subject to OS. 1024 is recommended min. max open files limit is found by "cat /proc/sys/fs/file-max" which outputs 412870query_cache_limit = 4M # defaults to 1Mquery_cache_size = 16M # last checked it had 22M free (qcl was 2M then) so it was reduced from 32M to 16M and qcl bumped to 4Mquery_cache_type = 1thread_cache_size = 1024 # I'm going to set this = to the number of tables in the table cache but I don't know what it should bethread_cache = 64 # 32-64 is recommended# InnoDB Settingsinnodb_data_home_dir = /var/lib/mysql/innodb/innodb_data_file_path = ibdata1:1000M:autoextendinnodb_buffer_pool_size = 2G # this can / should be 70% of the available ram for innodb only systems (4G totals for 32bit chips) so 3G would be recommended. This can be tuned.innodb_additional_mem_pool_size = 20Minnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 4M # do not set over 2-8M, is flushed once a second anywayinnodb_lock_wait_timeout = 50innodb_log_file_size = 256M # if you change this size, you must stop mysql, delete the log files for innodb, then start it to see a differenceinnodb_support_xa = OFF # when off, reduces overhead. may cause out of sync binlogsinnodb_thread_concurrency = 4 # (2 processors + 3 disks) * 2 = 10 concurrent threads. lower is generally better. default is infinite and may result in "thrashing" and "bumping"innodb_flush_method = O_DIRECTinnodb_open_files = 2048innodb_file_per_table# Buffer Settingsread_buffer_size = 4M # Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. (global / instant)read_rnd_buffer_size = 4M # When reading rows for order bys following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. (global / instant)sort_buffer_size = 512M # Each thread that needs to do a sort allocates a buffer of this size. Increase this value for Sort_merge_passes probs. This was 6M for 51k smps @ 17dayskey_buffer_size = 1G # key cache (max 4G) (recommend 30%. 25%-50% but no more of total ram). This appears to be a MyISAM setting but also seems to be globally available#myisam_sort_buffer_size = 6M # we dont use myisam anymore, so don't amp this up for performance anymore# TMP Table Settingsmax_heap_table_size = 1G # Used as needed, no adverse reactionstmp_table_size = 1G # Used as needed, no adverse reactionsmax_join_size = 1G # used to catch bad joins and disallow themjoin_buffer_size = 256M # used for unindexed table joins (never or rarely ever)#max_tmp_tables = 256 # (This option does not yet do anything.)[mysqldump]quick[mysql]no-auto-rehash


Those are the config options on this system. For reference, it's MySQL 5.0.37 on a dual 32bit Xeon system with 15k hard drives and 8 gigs of ram. Redhat only lets each chip address 4 gigs so most of the settings are tuned for a 4 gig setup not an 8 gig setup. I'll upgrade to 64 bit when I can afford it eek:

kmike wrote on Sun, 06 May 2007 05:32

From the documentation: "To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently." I.e. if you sort on a column which isn't a part of the index, or it's a part of the index which isn't used in the query conditions, or your query has something like ORDER BY col1 ASC, col2 DESC, a temporary table will be needed regardless of tmp_table_size setting. Tracking such queries isn't easy, but most probably such queries will be slow so you can look for them in the slow query log.

It's ok that MySQL creates temporary tables, but I don't understand why 100% of them are writing to disk.

edit: scrap this

Having tested sort buffer with a huge amount I've lowered it back down to something reasonable (64M) and dropped my max_connections to 50 from 100. With a really nicely performing database, I don't know that I'll need 100 connections. I'd rather spend those resources on buffer sizes and such

I ran a perl app for profiling memory usage just to see what it would come up with. If this helps shine a light on something I'm missing, I'll try something new.

http://www1.thedilly.com/pics/basics.htm

I'm still having some trouble with this. It looks like everything else is fine. My slow query time is set very low (3 seconds) so it's abnormally large. Really, the only thing that I can find which is slowing down the queries is the use the hard drive rather than the ram for every instance of tmp table creation. Here's a copy of my status page from myadmin for reference:

http://thedilly.com/pics/performance-issue.htm

Do you think that you can find out exactly which query/queries that create the temp tables to disk?

What does that query look like?

seems like its any query which would normally create a tmp table, only, none of them are registering in memory. i'll get back to you with some examples.