Announcement

Announcement Module
Collapse
No announcement yet.

table_cache max usable value?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • table_cache max usable value?

    Hi,

    I've read around that table_cache has some performance issues if put too big. But I've not found any mentions what setting is too big.

    I have huge about on databases with lots of tables of myisam.
    So the tables just can't fit in to table_cache and mysql opens
    something like 20 tables/s which isn't nice.

    So I would like to use as big table_cache as possible. At this moment it is 2048.

    There is need for filehandles for the table cache, open_files_limit is set to 16384.

    Systems ulimit for open files is set to 64k.

    Mysql is 5.0.45 from centos (rhel).

    And the question is, how big table_cache setting i can have? and what to do sith open_files_limit (any other settings for same thing or similar thing?) and system's ulimit.

  • #2
    http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

    There are other considerations besides the time it takes to open a table (which is actually pretty small).

    Don't forget every MyISAM table needs two file descriptors, as does every connection, and every temporary disk table, etc. You could probably set table_cache to 6K and be okay.

    Are you on a 64-bit host? How big are those tables? In another thread, to many opened large tables might be causing MySQL to crash.

    Comment


    • #3
      MarkRose wrote on Sat, 21 March 2009 20:10


      Are you on a 64-bit host? How big are those tables? In another thread, to many opened large tables might be causing MySQL to crash.


      It's 64bit and those tables are usually tiny.

      I tested 8k and it seemed slow, but after thinking about it, I think i didn't let the server to fill caches.

      And about those 16k total fd and 64k system limit, I can change them also if that would be recommended.

      Comment


      • #4
        Don't forget opened_tables will also go up with every temporary table created.

        How fast is created_tmp_disk_tables going up?

        Open_tables will give you a rough idea of how big your table_cache needs to be at a minimum. Doubling that at your peak load should be more than enough.

        Comment


        • #5
          MarkRose wrote on Sat, 21 March 2009 21:13


          How fast is created_tmp_disk_tables going up?



          Well, umm.


          # mysql -e "show status"|grep -e Opened -e Created_tmp_taOpened_tables 0Created_tmp_tables 1


          Not going up. But


          # mysqladmin status;sleep 10;mysqladmin statusUptime: 40982 Threads: 3 Questions: 14555653 Slow queries: 21 Opens: 697137 Flush tables: 1 Open tables: 2048 Queries per second avg: 355.172Uptime: 40992 Threads: 1 Questions: 14559457 Slow queries: 21 Opens: 697347 Flush tables: 1 Open tables: 2048 Queries per second avg: 355.178


          Also I've used this to make the figure reading easier:
          http://hackmysql.com/mysqlreport


          __ Tables Open 2048 of 2048 %Cache: 100.00Opened 698.58k 17.0/s__ Created TempDisk table 1.70M 41.4/sTable 1.76M 42.8/s Size: 64.0MFile 1.14k 0.0/s

          Comment


          • #6
            I'd definitely look to see if you can reduce the number of temporary disk tables created. 41/s is a lot!

            Comment


            • #7
              MarkRose wrote on Sat, 21 March 2009 22:33

              I'd definitely look to see if you can reduce the number of temporary disk tables created. 41/s is a lot!


              Any good ideas who to try to find them or what to do with them?

              Slow query log is at 1 second, and readed about that not using indexed log that it will put queries that just need to do full scan also there.

              http://www.mysqlperformanceblog.com/2006/09/06/slow-query-lo g-analyzes-tools/

              Have read that, but don't really fancy the idea of patching and compiling mysql.

              (And figured out why those status figures weren't changing, I need to ask GLOBAL status/variables

              And about those disk tables, those are really going to tmpfs.


              # df -htmpfs 2.0G 4.0K 2.0G 1% /tmpfs# mysql -e "show global variables"|grep -i ^tmpdirtmpdir /tmpfs

              Comment


              • #8
                Temporary tables are often caused when you do a join with another table, and use text/blob columns from that second table, and then order by the whole thing.

                Find select queries that do join .. order by, and put explain in front of them to see if they create temporary disk tables or not.

                Comment

                Working...
                X