0

I am relatively new in running enterprise db server and as a developer, I am used to running my DB in the default setting on more focused on the code. Now, I saw that the DB is not properly configured in an enterprise level with at least 2,000 user and generating > 1000 tickets per day.

How should I configure my DB server? I read this article https://medium.com/@richb_/tuning-mysql-3-simple-tweaks-6356768f9b90 and somehow I got lost on the process of getting the innodb_buffer_pool_instances and more.

Below is my current configuration:

https://pastebin.com/1TaKpRaB -> my.cnf

https://pastebin.com/pWF6SV6W -> show variables;

https://pastebin.com/CnwiWmx6 -> show global status;

  • 2
    What exactly led you to believe that "the DB is not properly configured"? – mustaccio Jul 11 '18 at 12:40
5

Set innodb_buffer_pool_size = 14G; don't bother with any other tuning until you encounter issues.

If, by 1000 tickets/day, you mean a few thousand queries per day, well, that is 'trivial'. Even a few thousand queries per minute is not a lot.

Further analysis...

Observations:

  • Version: 5.6.32-log
  • 25 GB of RAM
  • Uptime = 22:16:52; some GLOBAL STATUS values may not be meaningful yet.
  • You are running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

1700 qps, not queries-per-day! So the VARIABLES and STATUS are worth studying.

innodb_buffer_pool_size = 14G since is currently considerably bigger than the dataset.) Also innodb_buffer_pool_instances = 14

Com_rollback is quite high (3.7/sec); are you deliberately running things that need to be undone? Could they check sooner, thereby avoiding a potentially costly rollback? Com_rollback and Com_commit are surprisingly close in value. Sounds fishy.

The next upgrade may spit at you about innodb_additional_mem_pool_size, which is unused and deprecated. Remove it from my.cnf.

myisam_sort_buffer_size = 500M (The current 3G threatens to cause you to run out of RAM.)

tmp_table_size and max_heap_table_size work together. The former is dangerously high; the latter is possibly too small. Set both to 200M.

(Normally I advise against using the Query Cache. However, your application seems to make good use of it.)

Created_tmp_disk_tables is quite high. That, and some other clues, indicate inefficient queries. See this for details on finding slow queries and presenting them for critique.

Since you are running on Windows, I think thread_cache_size should be set to 0.

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 1,192 * 1024 / 8M = 14.6% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 20480M / 25600M = 80.0% -- % of RAM used for InnoDB buffer_pool

( innodb_buffer_pool_instances ) = 20 -- For large RAM, consider using 1-16 buffer pool instances, not allowing less than 1GB each

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 1,107,476 * 16384 / 20480M = 84.5% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 1,107,476 / 1310720 = 84.5% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,195,551,744 / 20480M = 14.9% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,081,532,416 / (80212 / 3600) / 2 / 2048M = 0.0113 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 80,212 / 60 * 2048M / 1081532416 = 2,654 -- 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. (Cannot change in AWS.)

( Com_rollback ) = 295,694 / 80212 = 3.7 /sec -- ROLLBACKs in InnoDB. -- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_additional_mem_pool_size ) = 32M -- (deprecated in 5.6.3, removed in 5.7.4.) -- A high value is unnecessary.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( join_buffer_size / _ram ) = 262,144 / 25600M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( myisam_sort_buffer_size / _ram ) = 3072M / 25600M = 12.0% -- Used for ALTER, CREATE INDEX, OPTIMIZE, LOAD DATA; set when you need it. Also for MyISAM's REPAIR TABLE. -- Decrease myisam_sort_buffer_size to keep from blowing out RAM.

( query_prealloc_size / _ram ) = 8,192 / 25600M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 8,192 / 25600M = 0.00% -- For parsing. Pct of RAM

( net_buffer_length / max_allowed_packet ) = 16,384 / 256M = 0.01%

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

( Qcache_inserts - Qcache_queries_in_cache ) = (9257553 - 3004) / 80212 = 115 /sec -- Invalidations/sec.

( Created_tmp_disk_tables ) = 597,469 / 80212 = 7.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 on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Created_tmp_tables ) = 597,469 / 614226 = 97.3% -- Percent of temp tables that spilled to disk -- Maybe increase tmp_table_size and max_heap_table_size; improve indexes; avoid blobs, etc.

( tmp_table_size ) = 853M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Com_rollback / Com_commit ) = 295,694 / 298826 = 99.0% -- Rollback : Commit ratio -- Rollbacks are costly; change app logic

( Select_scan ) = 98,730 / 80212 = 1.2 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".

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

( Threads_created / Connections ) = 322 / 1160 = 27.8% -- Rapidity of process creation -- Increase thread_cache_size (non-Windows)

Abnormally large:

Com_show_keys = 0.063 /sec
Com_show_plugins = 0.27 /HR
Innodb_buffer_pool_pages_free = 1.11e+6
Qcache_hits = 1529 /sec
Select_range = 35 /sec
Select_range / Com_select = 23.2%
Sort_range = 36 /sec
myisam_sort_buffer_size = 3072MB
| improve this answer | |
  • Even the buffer pool configuration is probably not necessary. The database won't reach that size for months, if ever. – Bill Karwin Jul 11 '18 at 4:08
  • I set my innodb_buffer_pool_size= 20G as they said it should be 80% of my memory then allocated innodb_buffer_pool_instances=20, is that ok and then innodb_thread_concurrency=0 – MiksMeister Jul 11 '18 at 5:58
  • @MiksMeister Rick James's suggestion of 14G is more reasonable than 20G, leave some breathing room until you have an understanding of how RAM is being used. – Wilson Hauck Jul 12 '18 at 0:29
  • @MiksMeister - I added an analysis of the VARIABLES and GLOBAL STATUS that Wilson requested. Thanks Paul for making it practical to add the analysis. – Rick James Aug 16 '18 at 22:52
1

Suggestions for your my.cnf [mysqld] section Rate Per Second = RPS

max_heap_table_size=256M  # from 16M to support your level of activity in RAM
tmp_table_size=256M  # from 894M = max_heap_table_size & to reduce created_tmp_disk_tables count
thread_cache_size=100  # from 10 for CAP per v8 & to reduce threads_created
innodb_io_capacity=1000  # from 200 to enable higher RPS
read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_next RPS
innodb_purge_threads=2  # from 1 to minimize delays when DEL rows
innodb_buffer_pool_instances=8  # from 20 will be adequate for you 4G of data
innodb_lru_scan_depth=128  # from 1024 to reduce CPU busy every SECOND see refman
innodb_buffer_pool_size=8G  # from 20G for effective in RAM of 6G data & ndxs
query_cache_min_res_unit=512  # from 4096 to store more results in current QC

Remember ONE change per working day, monitor, if detrimental, remove last change from my.cnf and let me know your perspective.

For additional suggestions, please view my profile, Network Profile for contact info including my Skype ID.

| improve this answer | |
  • @MiksMeister When you have a few minutes, please review comments/Answers posted after July 27. You will find some details to be considered to improve your situation. – Wilson Hauck Aug 19 '18 at 21:56
  • @MiksMeister Have you made significant progress with your use of MySQL in last few weeks? Please let us know. – Wilson Hauck Aug 25 '18 at 18:32
0

For a noob PhpMyAdmin can be a good start. It makes some suggestions, but you should read the MySQL manual and get familiar with the options. Don't forget: No Backup, No Mercy

| improve this answer | |

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.