Page MenuHomePhabricator
Create Task
Maniphest T423950

Fundraising mariadb database server/service tuning
Open, Needs TriagePublic
Actions

Description

Kernel/OS/RAID

CPU frequency - see T225713: CPU scaling governor audit

  • frdb1005 for example drops to 1200 MHz at idle
  • BIOS is configured to powersave profile by default
  • prod uses cpufrequtils/cpupower
  • on frav1005 I set it like this:
racadm set BIOS.SysProfileSettings.SysProfile PerfOptimized
racadm jobqueue create BIOS.Setup.1-1 -r pwrcycle -s TIME_NOW

Reevaluate use of LVM

  • LVM is expected to slow writes a bit
  • we don't using snapshots or thin provisioning

Raise async I/0 (fs.aio-max-nr)

  • currently 65536
  • in theory could cause resource-unavailable errors, not observed AFAIK
  • increasing some other limits could increase async I/O activity
  • possibly raise to 1048576?

Raise mariadb user's open files limits

  • currently 5000/5000
  • should accomodate max_conn + table_open_cache + a couple k for other db files
  • increase to 15-20K?

MariaDB

innodb_log_file_size

  • currently 512MB, single log
  • smaller file means greater checkpoint frequency
  • should we increase it to 2-8 GB?

innodb_io_capacity

  • currently 5000
  • IOPS capacity measured with fio on frdb1006 was ~ 58K
  • should we raise it to more like 20K?

innodb_io_capacity_max

  • currently 10K
  • we don't set it, but make sure it increases along with innodb_io_capacity

innodb_write_io_threads

  • currently 4 (default)
  • we have a 32 and 48 core machines
  • increase to 16?

innodb_read_io_threads

  • increase to 16?

innodb_flush_neighbors:

  • default/current: 1
  • best SSDs: 0

table_open_cache

  • currently 3000
  • considering 5K max connections this is probably too low
  • increase to 8000?

table_open_cache_instances:

  • currently 4
  • servers are 32 or 48 core
  • increase to 16?

Related Objects
Search...

StatusSubtypeAssignedTask
 OpenNoneT241083 Epic: fundraising database scalability
 OpenNoneT423950 Fundraising mariadb database server/service tuning

Event Timeline

Jgreen created this task.Mon, Apr 20, 7:54 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMon, Apr 20, 7:54 PM
Jgreen added a parent task: T241083: Epic: fundraising database scalability.Mon, Apr 20, 7:55 PM
Jgreen updated the task description. (Show Details)Mon, Apr 20, 7:58 PM
Jgreen updated the task description. (Show Details)
Jgreen updated the task description. (Show Details)Mon, Apr 20, 8:24 PM
Dwisehaupt subscribed.Tue, Apr 21, 10:21 PM

@Jgreen Was thinking about the thread bits a little. Would it make sense to have something like this for thread count (done dynamically based on cpu count)?

db typereadwrite
readercpu_count * 2/3cpu_count * 1/4
origincpu_count * 1/4cpu_count * 2/3

On a 32 core box, that would get a possible 21 threads of the primary function and 8 threads of the secondary. It would leave 3 threads untouched for other activity such as OS activities. That would move to 32/12/4 with the 48 core hosts. Or we could do some math for a total number of mariadb threads, and divide up based on that subset. The factors could be adjusted.

I would be a bit concerned of having 16 possible read and 16 possible write on a 32 core host if things went really heavy. Don't want to introduce unnecessary context switching from over subscription.

Just a thought.

Jgreen added a comment.EditedThu, Apr 23, 3:50 PM
In T423950#11847663, @Dwisehaupt wrote:

@Jgreen Was thinking about the thread bits a little. Would it make sense to have something like this for thread count (done dynamically based on cpu count)?

db typereadwrite
readercpu_count * 2/3cpu_count * 1/4
origincpu_count * 1/4cpu_count * 2/3

On a 32 core box, that would get a possible 21 threads of the primary function and 8 threads of the secondary. It would leave 3 threads untouched for other activity such as OS activities. That would move to 32/12/4 with the 48 core hosts. Or we could do some math for a total number of mariadb threads, and divide up based on that subset. The factors could be adjusted.

I would be a bit concerned of having 16 possible read and 16 possible write on a 32 core host if things went really heavy. Don't want to introduce unnecessary context switching from over subscription.

Just a thought.

It's hard to find super clear info on this, as far as I can tell 8-16 should be appropriate for PERC/SSD RAID and should be very safe on 32+ core boxes. It's dynamically tunable in 10.11, we could experiment with different values to see if we see any benefit and/or jump in context switching.

Jgreen updated the task description. (Show Details)Thu, May 7, 12:40 PM
Jgreen updated the task description. (Show Details)Thu, May 7, 12:45 PM
Jgreen added a comment.EditedThu, May 7, 1:20 PM

frdb1008 first tuning attempt (all settings are in hosts.yaml):

frdb1008:
    mariadb_innodb_flush_neighbors: 0
    mariadb_innodb_io_capacity: 20000
    mariadb_innodb_io_capacity_max: 30000
    mariadb_innodb_log_file_size: '4G'
    mariadb_innodb_read_io_threads: 16
    mariadb_innodb_write_io_threads: 16
    mariadb_open_files_limit: 15000
    mariadb_table_open_cache: 8000
    mariadb_table_open_cache_instances: 16

Also set PerfOptimized in BIOS

Jgreen moved this task from Triage to In Progress on the fundraising-tech-ops board.Mon, May 11, 3:39 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits