On Thu, Apr 22, 2004 at 08:05:07PM -0700, Emmett Bishop wrote: > Howdy all, > > I've got a few questions about the table cache and the > tmp_table_size. > > First off, a little background info. The server has > been running now for 193 hours. I've got 3 GB of RAM > on my box and I'm running RH linux. The MySQL > installation is version 4.0.13. > > After taking a look at Show Status I noticed that the > Opened Tables value was 10188. Does this qualify as > big? It seems like it but I'm not sure.
Maybe. If you look at it on a per-minute or per-second basis it's not. But it's probably worth fixing anyway, just because it's easy. > The table cache is set to the default value (64). My max > connections is 100 and the largest set of joins I do is > 6. Furthermore the max number of concurrent connections is only 9, > at least for now. So should I bump the table cache to 600 like the > docs say? I assume that linux can handle that no problem, but I > don't know linux well so perhaps that's not true. I'd bump it up. Would I use 600? Maybe. I'd go to a couple hundred. You'll probably find that with, say, 200, Opened Tables doesn't increase nearly as quickly. > Second, show status showed that Created_tmp_disk_tables = > 111223. Again, I'm assuming that this is big and that I should > adjust the tmp_table_size server variable. The tmp_table_size is set > to the default (33554432). So, if I'm reading the docs correctly, > MySQL will create the temp table on disk if the table will exceed > ~33MB in size? > That seems awfully large. How does that number compare to the total number of queries and the total number of non-disk-based temp tables? > Does that suggest that there are some queries that are doing full > joins or something to that effect? Is there any guideline on how > large I should make this value (something similar to the guidelines > on innodb_buffer_pool_size or key_cache_size)? It suggests that many queries you're doing require large temporary tables. As for how large to make the value, it's trial and error. MySQL doesn't have a way of saying "if you made it this large, I wouldn't need to use the disk so much." What you can do is bump it up in size and see how it affects things. Be careful not to make it too big. Otherwise, if multiple clients try to allocate lots of memory at once, you can end up running out of memory or causing swapping. > Does anyone know if the new book High Performance MySQL covers > serving tuning in detail? It discusses this and related tuning, yes. In detail? That depends on what you consider detail I guess. > There seems to be a lot that I could do, but most of the > documentation that I've come across seems to be lacking in > explanation of the basic concepts. Which basic concepts are not discussed elsewhere? The basic approach to tuning these values? What the values mean? What the "right" numbers are? Who knows, maybe it'll help improve the 2nd edition. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
