1

Current configuration:

+------------------------------------------+--------------------+
|                          key_buffer_size |          64.000 MB |
|                         query_cache_size |          32.000 MB |
|                  innodb_buffer_pool_size |       15360.000 MB |
|          innodb_additional_mem_pool_size |           8.000 MB |
|                   innodb_log_buffer_size |         128.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |       15592.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           0.500 MB |
|                         read_buffer_size |           1.000 MB |
|                     read_rnd_buffer_size |           2.000 MB |
|                         join_buffer_size |           0.500 MB |
|                             thread_stack |           0.250 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |           8.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          12.281 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                275 |
|                          max_connections |                700 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |       18969.344 MB |
|                              TOTAL (MAX) |       24188.875 MB |
+------------------------------------------+--------------------+

top:

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                           
24876 mysql     20   0 57.206g 0.045t   5.4m S 279.8 90.2   2358:20 mysqld   

According to documentation memory should be ~25GB. But with those settings Mysql 5.6.24 actually uses 47GB memory. Any ideas why or how I can reduce requirements?

UPDATE, - more info:

Mysql has about 600K tables so we have a big table_open_cache(=400K) as this made a big improvement in performance.

The system writes to DB every 10-15 minutes using most of the available connections. wait_timeout is 600sec.

System memory is 52G

UPDATE ADDED: SHOW VARIABLES, & SHOW GLOBAL STATUS BELOW: http://pastebin.com/K8ZNY0pC

This is used by a closed-source commercial software (Tridium Niagara). I cannot change the schema or the way it writes. All tables are InnoDB. Reducing table open cache by half has negligible impact on memory.

  • Ouch! Each table needs at least 1 file in the filesystem; this leads to sluggishness in the OS. I would expect 500K tables to be quite slow for this reason. – Rick James Oct 30 '15 at 14:23
  • Why the downvote? – sivann Nov 27 '15 at 11:24
  • @sivann If you still need assistance in reducing RAM requirements, please post current current complete my.cnf/ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) SHOW ENGINE INNODB STATUS; Optional very helpful information, if available includes - htop OR top for most active apps ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, free -m for a linux/unix free memory report, complete MySQLTuner.com report if readily available. – Wilson Hauck Mar 15 '18 at 11:17
  • @sivann Could you post the query that displayed the RAM requirement that leads your question? This is a good one. Thanks – Wilson Hauck Mar 15 '18 at 17:59
  • @WilsonHauck the queries were very simple "give the last row of that table" type of queries. We are now phasing out this software. – sivann Mar 16 '18 at 13:00
1

(adding a second answer -- to address the VARIABLES and STATUS)

Observations:

Version: 5.6.24
52 GB of RAM
Uptime = 21d 02:26:20
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

  • table_open_cache = 200K, yet 156 tables opened per second, and all are misses/overflows from the cache. You must redesign you schema to have a civilized number of tables.

  • Are there really 403 ROLLBACKs per second? If so, think about how to avoid some of them. That's probably more than 10% of the transactions.

  • innodb_lock_wait_timeout = 300 -- Assuming you really need transactions to hang around waiting for 5 minutes, this could be consuming RAM.

  • tmp_table_size = 8M, together with 51 tmp tables created per second, may be leading to quite a few GB of transient MEMORY tables. Look through the slowlog to find which queries need tmp tables and see if they can be rewritten.

  • Decrease long_query_time to 2 (now 10), turn on the slowlog, wait a day, run pt-query-digest. Then study the worst few queries -- improving them will speed up the entire system and probably decrease RAM usage.

Details and other observations

( Innodb_buffer_pool_reads ) = 329,533,959 / 1823180 = 180 /sec -- InnoDB buffer_pool I/O read rate -- check innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 589,768,119 / 1823180 = 323 /sec -- Writes (flushes) -- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 16,106,127,360 / 53248M = 28.8% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 285,544,112 / 1823180 = 156 /sec -- Frequency of opening Tables -- increase table_open_cache

( table_open_cache ) = 200,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Table_open_cache_overflows ) = 285,343,673 / 1823180 = 156 /sec -- May need to increase table_open_cache

( Table_open_cache_misses ) = 285,544,112 / 1823180 = 156 /sec -- May need to increase table_open_cache

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((329533959 + 589768119) ) / 1823180 = 504 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size?

( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions. -- Adjust innodb_log_buffer_size.

( Innodb_log_writes ) = 167,769,519 / 1823180 = 92 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 332,945,398,784 / (1823180 / 3600) / 2 / 100M = 3.13 -- Ratio

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,823,180 / 60 * 100M / 332945398784 = 9.57 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.

( Com_rollback ) = 735,486,482 / 1823180 = 403 /sec -- ROLLBACKs in InnoDB. -- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_lock_wait_timeout ) = 300 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks. -- Fix the cause of timeouts rather than increasing this value.

( Innodb_dblwr_writes ) = 14,574,293 / 1823180 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)

( local_infile ) = ON -- local_infile = ON is a potential security issue

( Questions ) = 7,836,091,851 / 1823180 = 4298 /sec -- Queries (outside SP) -- "qps" -- >2000 may be stressing server

( Queries ) = 8,262,090,685 / 1823180 = 4531 /sec -- Queries (including inside SP) -- >3000 may be stressing server

( Created_tmp_tables ) = 92,537,439 / 1823180 = 51 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 6,158,440 / 1823180 = 3.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size and max_heap_table_size. Check the rules for temp tables being able to use MEMORY instead of MyISAM. It may be possible to make a minor schema or query change to avoid MyISAM. Better indexes and reformulation of queries can

( Handler_read_rnd_next ) = 266,467,437,770 / 1823180 = 146155 /sec -- High if lots of table scans -- possibly inadequate keys

( Com_rollback / Com_commit ) = 735,486,482 / 741864016 = 99.1% -- Rollback : Commit ratio -- Rollbacks are costly; change app logic

( Com_show_variables ) = 6,666,028 / 1823180 = 3.7 /sec -- SHOW VARIABLES ... -- Why are you requesting the VARIABLES so often?

( Select_scan ) = 315,076,005 / 1823180 = 172 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 315,076,005 / 3888296954 = 8.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (613230844 + 1238003 + 0 + 30479625 + 71282659 + 0) / 1823180 = 392 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 23 - 22 ) / ( 23 + 22 ) = 2.2% -- Are you closing your prepared statements? -- Add Closes.

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( Com_change_db ) = 31,947,189 / 1823180 = 18 /sec -- Probably comes from USE statements. -- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( max_connect_errors ) = 10,000 -- A small protection against hackers. -- Perhaps no more than 200.

( Connections ) = 21,121,749 / 1823180 = 12 /sec -- Connections -- Increase wait_timeout; use pooling?

( Threads_running - 1 ) = 22 - 1 = 21 -- Active threads (concurrency when data collected) -- Optimize queries and/or schema

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

39 issues flagged, out of 125 computed Variables/Status/Expressions. Settings not mentioned are mostly OK.

  • Hi, thanks for helping, but as I mentioned in a previous comment, this is a closed source software, we cannot change the schema or the way it commits. Yes we have 600K tables, and yes it makes lots of (fake) rollbacks. Your suggestions to increase cache will improve performance but raise memory requirements, we want to decrease it :-) – sivann Nov 27 '15 at 11:40
  • Feel free to pass my comments on to the 3rd party software. Or get them to add comments to this thread. I am not familiar with Tridium Niagara. – Rick James Nov 27 '15 at 16:36
0

(I don't see where "47GB" comes from. The "total" includes the "base". Anyway...)

The "MEMORY PER CONNECTION" is seriously pessimistic. And yet it is actually an underestimate!

Here's the pessimistic: The thread_stack (0.25MB) is the only thing that is necessarily allocated for a connection; the rest may or may not be allocated, and they may be freed while the connection lives.

Here's the underestimate: A complex SELECT can allocate multiple tmp tables of up to 16MB each.

To protect yourself, decrease max_connections and/or increase wait_timeout. (The latter kills off connections that sit around idle too long.)

Find out why 248 clients are staying connected. One possible reason is MaxClients in Apache could be higher than is realistic.

After that, I would set innodb_buffer_pool_size to 70% of available RAM and not worry about the pessimistic formula. (How much RAM do you have?)

I have reviewed formulas similar to yours on literally hundreds of production machines. All had the scare of "too much memory"; none actually exhibited "too much memory".

  • Thanks for the time to review this. Connections come from a closed-source software not apache. I added more info on the question. – sivann Oct 26 '15 at 10:15
  • Your case is unusual. I don't happen to know the RAM needed each entry in the table_open_cache. I see that it is not listed in your 'formula'. Clearly, if you need 400K entries, that should be a very important factor in the formula. Sorry, I don't know the size. – Rick James Oct 30 '15 at 15:17
  • Another factor is innodb_open_files, but you are using MyISAM? MyISAM needs 3 files per table, making the OS overhead even more. – Rick James Oct 30 '15 at 15:19
  • No MyISAM, only innodb. You're probably right, table open cache might be the problem, althrough I can't imagine it requiring so much RAM.. – sivann Nov 2 '15 at 10:54
  • Swapping is really bad for MySQL; make sure that is not happening. – Rick James Nov 2 '15 at 20:11

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.