0

how much to keep table_open cache and what settings is available in mysql to release unwanted memory cache and table cache? MY server has 672GB RAM windows environment server 2012 Innodb_buffer_pool_size = 450GB Can you please suggest me what are the things need to check ?

0

Each unit in table_open_cache is one table (not one byte). Since you have a huge amount of RAM, the 'right' answer is probably to make it a little more than the number of tables in all the databases.

It is limited to open_files_limit, which, in turn, is limited by the OS. Some OSs have an artificially low 1024, but it can be changed.

Suggest table_open_cache_instances = 16 if your version includes that relatively new feature.

If you would like a lengthier check, please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; after mysqld has been running for more than a day, and preferably with a realistic load.

A common mistake is setting query_cache_size bigger than about 50M. The code in the QC was not designed to scale. Anyway, most production servers run better with the QC turned off.

The buffer_pool is the main cache in RAM. There are a number of lesser ones. Is this what you mean by "memory cache"?

Will this server be running anything other than MySQL? If so, you should subtract that from the 672G when posing the question.

8
  • query_cache_size is off
    – SAM AB
    Jun 9 '17 at 5:08
  • 'Innodb_page_size', '16384','Innodb_pages_created', '15292312','Innodb_pages_read', '152420011' 'Innodb_pages_written', '29082111' 'Innodb_row_lock_current_waits', '0' 'Innodb_row_lock_time', '106335' 'Innodb_row_lock_time_max', '51034' 'Innodb_rows_deleted', '6571985' 'Innodb_rows_inserted', '395261295' 'Innodb_rows_read', '1000948314' 'Innodb_rows_updated', '286405875' 'Innodb_num_open_files', '300' 'Innodb_data_written', '725130240' 'Innodb_log_write_requests', '1135214437' 'Innodb_log_writes', '454460' 'Innodb_os_log_written', '369411484672'
    – SAM AB
    Jun 9 '17 at 5:23
  • query_cache_size is off ,Bytes_received 232815028880,Bytes_sent 1151271430250 'Handler_write', '1346404623' 'Innodb_buffer_pool_pages_data', '1048533' 'Innodb_buffer_pool_bytes_data', '4294262784' 'Innodb_buffer_pool_pages_flushed', '28998737' 'Innodb_buffer_pool_pages_total', '1048576' 'Innodb_buffer_pool_read_requests', '4172228660' 'Innodb_buffer_pool_reads', '3906424','Innodb_buffer_pool_wait_free', '133084''Innodb_buffer_pool_write_requests', '1723114281','Innodb_data_read', '1763889664''Innodb_data_reads', '152457769','Innodb_data_writes', '30842750'
    – SAM AB
    Jun 9 '17 at 5:23
  • Recheck innodb_buffer_pool_size and whether you are running a 32-bit version of MySQL or the OS. You should be running 64-bit.
    – Rick James
    Jun 9 '17 at 14:52
  • Yes 64bit windows server 2012R2 @Rick James , any suggestions?
    – SAM AB
    Jun 9 '17 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.