Skip to main content

Mysql server getting slower

charliecres
here is my my.cnf [mysqld] sql_mode = "NO_ENGINE_SUBSTITUTION" disable-log-bin = 1 default-authentication-plugin = mysql_native_password performance-schema = 0 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid innodb_buffer_pool_size = 134217728 max_allowed_packet = 268435456 open_files_limit=80000 innodb_file_per_table = 1 mysqlx = 0 and here is MySQL process
Id User Host db Command Time State Info
5 event_scheduler localhost NULL Daemon 111 Waiting on empty queue NULL
94 sgxy_surapp localhost sgxy_surapp Sleep 91 NULL
110 root localhost NULL Sleep 27 NULL
341 weon_Da1 localhost weon_Da1 Sleep 9 NULL
395 root localhost NULL Query 0 init SHOW PROCESSLIST
suddenly MySQL server getting too much slow. how can I fix this permanently? is there anything wrong or need to add at my.cnf?

Comments

9 comments

Date Votes
  • charliecres
    and right now here MySQL" Processes !!
    Id User Host db Command Time State Info
    5 event_scheduler localhost NULL Daemon 7290 Waiting on empty queue NULL
    72 root localhost NULL Sleep 17 NULL
    45766 bes_steo localhost bes_steo Sleep 1 NULL
    50445 bes_steo localhost bes_steo Execute 293 Waiting for table metadata lock insert into `tblemails` (`userid`, `date`, `to`, `cc`, `bcc`, `subject`, `message`, `pending`, `fail
    50988 bes_seo localhost bes_seo Execute 245 Waiting for table metadata lock select * from `tblemails` where `pending` = 1 limit 25
    51153 babu_w61 localhost babu_w61 Sleep 226 NULL
    51676 mad_host localhost mad_host Sleep 130 NULL
    52013 ytpa_best localhost ytpa_best Sleep 2 NULL
    52226 wek_Use1 localhost wek_Use1 Sleep 29 NULL
    52349 root localhost NULL Query 0 init SHOW PROCESSLIST
    0
  • andrew.n
    slower compared to what?:)
    0
  • charliecres
    slower compared to what?:)

    compared to regular speed.
    0
  • kernow
    While its not perfect, the "Edit SQL Configuration" in WHM can give you a reasonable set of variables to use. Note! don't use if your MySQL server has been up for < 48 hours.
    0
  • ffeingol
    The issue does not appear to be MySQL itself, it's something with the bes account and the tblemails table. Something (which is odd that it's not showing) is holding a lock on that table, so the other processes can't process. That's typically not a config issue, it's a coding to database design issue. Can you run "describe tblemails;" so we can see the table structure?
    0
  • charliecres
    The issue does not appear to be MySQL itself, it's something with the bes account and the tblemails table. Something (which is odd that it's not showing) is holding a lock on that table, so the other processes can't process. That's typically not a config issue, it's a coding to database design issue. Can you run "describe tblemails;" so we can see the table structure?

    "bash: describe: command not found" and the problem is MySQL server performance getting too much slower every morning!!
    0
  • andrew.n
    Is it happening constantly or just in the morning? what version of MySQL do you have?
    0
  • charliecres
    Is it happening constantly or just in the morning? what version of MySQL do you have?

    it"s happening morning to evening! mysql v8.0.32! i am using cloudlinux MySQL Governor Mode - abusers Governor ver. - 1.2-88
    0
  • mtindor
    That table looks like it is probably a WHMCS table that holds / retains emails sent by WHMCS. And if your WHMCS has a lot of accounts, has been active for years, and/or has a lot of activity in it, that table can get pretty large (as well as a couple of other tables). I would recommend checking the size of your tblemails table. It's probably pretty large. And you can prune it within WHMCS if indeed it is a WHMCS table. WHMCS --> Utilities --> System --> System Cleanup --> Advanced --> Prune Saved Emails M
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post