1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MariaDB + MySQL Governor on CloudLinux: High Load

Discussion in 'MySQL' started by VideoWhisper.com, Nov 26, 2019.

  1. #1
    A new account is generating very high MySQL load and huge binary logs, with impact on overall server load. Impact on server seems higher that account usage.

    Reviewing MariaDB USER_STATISTICS table from information_schema (enabled plugin), compared usage of this account with next big user in various rows:
    - CPU_TIME 12x higher
    - BYTES_RECEIVED 3x
    - BYTES_SENT 6x
    - BINLOG_BYTES_WRITTEN 36x (getting 20Gb logs for 1 day)
    SEMrush
    There are sites logging 3x more connections generating lower load, so issue is related to specific queries from this account.

    Enabled "all" mode in MySQL Governor so accounts are always in LVE, updated to 10.3.20-MariaDB , optimized binary logs configuration:
    binlog_row_image = MINIMAL
    binlog_annotate_row_events = OFF
    binlog_format = MIXED
    max_binlog_size = 512M
    log_bin_compress = 1

    Account does not show using more than allocated CPU in CloudLinux (100% meaning 1 core) but impact on server is big, with MySQL staying at 80% and total server load around 5/8.

    Binary logs were enabled for reliability, because sometimes mysql can not recover without manual intevention, showing "[ERROR] Can't init tc log" . Will also evaluate longer with current MariaDB version.
    Disabling binary logs does not seem to impact performance (similar mysql and server load) but would reduce extra load associated with writing tens of Gb of data per day.

    How to give this account resources paid for (an entry plan with 1 CPU core, 2 Gb memory) while also protecting server from higher impact?

    Any other suggestions for optimizing load and balancing resources / restrictions?
     
    VideoWhisper.com, Nov 26, 2019 IP
    SEMrush
  2. bountysite

    bountysite Active Member Premium Member

    Messages:
    70
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    73
    #2
    bountysite, Nov 27, 2019 IP