Hi again. :-) On Thu 2002-09-05 at 14:18:10 -0500, [EMAIL PROTECTED] wrote: [...] > 3) I'm somewhat at a loss for this one and perhaps the answer is more > obvious than not. I have 257 total tables from my main DB and mysql. I > figured this by a "ls -al var/ | grep -c MYD". How can I possibly have > 512 (which is also the table_cache value) open tables?
Because each concurrent access needs an own entry, e.g. self-joins and parallel accesses. See http://www.mysql.com/doc/en/Table_cache.html for more info. [...] > | Open_tables | 512 | > | Opened_tables | 1499 | > > I also see Opened_tables is 1499, which is 3x the number of open tables. > I would consider this number average and not think about increasing > table_cache. Depends on the time period. According to your quote below, your server is running for about 23 days (uptime 2020009 secs). That makes 1 opened table every 2 hours. I wouldn't care about it, before this figure goes well beyond 1 every minute. [...] > OTOH, one of my slaves has this: > > | Open_tables | 256 | > | Opened_tables | 3532 | > > Where 256 is the table_cache limit. I'd consider the opened tables to > be big, and would probably want to increase the table_cache size. See above. > 4) How can I reliably determine how much RAM MySQL is indeed using? > >From the manual: > > "ps and other system status programs may > report that mysqld uses a lot of memory" > > ps shows each instance of MySQL using 91760k of RAM x ~40 processes = > 3.6GB, which is how much real RAM I have. You are running Linux 2.4.x. Linux displays threads as processes, and with every thread the data of the whole process, i.e. it's just 91760k for all threads together. But, of course, the number 91760k seems a bit out of place, as your key cache alone already uses 400MB. Would you mind to quote some lines from ps? And some info about mem usage: http://www.mysql.com/doc/en/Memory_use.html [...] > To further assistance here, here are my settings for the master, which > is the DB that's swapping: [...] > myisam_sort_buffer_size | 67108864 This is a per-connection buffer. Although it's only allocated when needed, you could run into troubles if several concurrent connections need to sort something. [...] > record_buffer | 2093056 > record_rnd_buffer | 2093056 > sort_buffer | 2097144 There are also per-connection buffers. Not really something wrong with them, but note that if you happen to come to your connection maximum (250), these alone would eat up to 6MB*250 = 1.5GB. I would not change them now; just wanted that you aware of it. All other settings look sane to me (compared with one of my servers). > Now we go to extended-status: [...] > | Handler_delete | 15398143 | > | Handler_read_first | 4561849 | > | Handler_read_key | 78715268 | > | Handler_read_next | 2518057153 | > | Handler_read_prev | 2759123 | > | Handler_read_rnd | 51014466 | > | Handler_read_rnd_next | 639269479 | > | Handler_update | 6824117 | > | Handler_write | 25044236 | These numbers give me the impression that your queries could benefit from some additional indexes. There seem to be a lot of ranged and full table reads. But - being ignorant of your real usage - I could be dead wrong, of course. > | Key_blocks_used | 433327 | Btw, this means, as we already discussed, that only 433327KB of your key_cache are used at all. All numbers look sane to me. [...] > And lastly, free.. > > total used free shared buffers > cached > Mem: 3703180 3694624 8556 0 12800 > 2158160 > -/+ buffers/cache: 1523664 2179516 > Swap: 2096440 1582784 513656 That is plain strange. You have 2179516KB virtually free (i.e. Linux could free it, if it wanted), but Linux decided anyhow to swap out 1582784KB. I cannot help, but seems dead wrong to me. It could be due to the VM swapping problem I mentioned earlier. Also, it says that about 1.5GB (+1.5GB swap) are in real use, though considering the stat numbers from MySQL you provided above, I would not expect it to use more than about 800MB. Are you running other services on that machine? Could you provide the full output of ps? I cannot get rid of the feeling that we are missing something significant here (maybe I am blind at the moment ;). Bye, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
