Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Looking for Mariadb configuration/optimisation
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Looking for Mariadb configuration/optimisation

Hiya,
i am looking for mariadb configuration/optimization (/etc/my.cnf);
i have 100+ wordpress blogs and tons of posts.

ryzen 7 3700x 8core/16thread
64gb ram ddr4
2x1tb nvme ssd
directdmin
litespeed (1worker)

can somebody help me to optimize or tell me how to configure it

Comments

  • Give me your popular websites, i will visit fews

    Thanked by 1fayaz90
  • MySQLTuner should work as well with MariaDB.

    Thanked by 1fayaz90
  • @SteveMC said:
    MySQLTuner should work as well with MariaDB.

    i tried mysqltuner.pl but still no performance,

  • @fayaz90 said:

    @SteveMC said:
    MySQLTuner should work as well with MariaDB.

    i tried mysqltuner.pl but still no performance,

    Do you have installed and turned on (on the module side and web server side) LSCache for Wordpress?

    Thanked by 1fayaz90
  • @Hotmarer said:

    @fayaz90 said:

    @SteveMC said:
    MySQLTuner should work as well with MariaDB.

    i tried mysqltuner.pl but still no performance,

    Do you have installed and turned on (on the module side and web server side) LSCache for Wordpress?

    yes, i scan and double checked, lscache is active on all the wordpress sites,
    also seems lsphp is all around in htop(monitor) list

  • You need to first check if MariaDB is indeed the issue.
    If it is Wordpress, you will have a lot of reads. See if you actually have a lot of reads.
    When you say tons of post, how much does that translate to on the DB,
    lot of text could fit under a few hundred MBs or a handful of GBs.

    From the amount of RAM you have, you could likely fit your entire DB in RAM.
    See what are the most of common slow queries.
    See if indexing certain columns help, in most cases it does.
    The rule of thumb is to index everything that goes under a 'WHERE' clause.

    Ensure cache works as intended, see the difference when the cache is cleared vs a page loaded from cache, Most Wordpress sites just need a good cache.
    If none of this helps, then look at optimizing your MySQL instance or even look at introducing a read replica.

    Thanked by 1fayaz90
  • I have the same problem, except mine uses MySQL— in a need for query optimization.

    A simple SELECT query takes 10 minutes and 100% of CPU for 60k row, I found that the database structure needs to be adjusted to lessen the CPU spike, I have yet to solve this problem.

  • Do not engage cocky mjj. He demand, not asking.

  • You should use the ANALYZE and EXPLAIN statements to debug a query, and see what is the bottleneck.

  • @fayaz90 said: i tried mysqltuner.pl but still no performance,

    so you ran it, adjusted the mysql config and nothing improved? did you run it again?

    do you really think that the db is the problem? have you tried any caching plugins inside of wordpress?

    what about the stats? how many posts, comments, visits etc do you have combined?

    ps: my biggest wordpress project with about 1,5 mil unique visits per month, just under 70k posts, around 500k comments, with 150 new posts and around 500 comments added monthly, is running on a small hetzner vps.

  • i tried everything.
    yes i have bigs sits with big database, mostly around 500MB to 2GB database size for each site, logs showing Select and join 90%.

    and traffic is almost 50- to 100K daily.

    i am using litespeed license,(1worker, directadmin, with centos 8 stream);

    please let me know

  • FatGrizzlyFatGrizzly Member, Host Rep
    edited November 2022

    Edit: I'm an idiot

  • @FatGrizzly said:
    Edit: I'm an idiot

    no, you are honey bear!!

  • SteveMCSteveMC Member
    edited November 2022

    @FatGrizzly said: Edit: I'm an idiot

    It's okay, everybody is welcomed in The Great Whole :kissing_closed_eyes:

    @fayaz90, you are not explaining what is wrong, are your sites/servers too slow ? How much too slow ?

  • @SteveMC said:

    @FatGrizzly said: Edit: I'm an idiot

    It's okay, everybody is welcomed in The Great Whole :kissing_closed_eyes:

    @fayaz90, you are not explaining what is wrong, are your sites/servers too slow ? How much too slow ?

    yes, my site are slow, sometime connection timed out, i even used the litespeed 2 worker license, but nothing,

    now my server load average is 35.10, 29.22, 30.11

  • Without much more information, it's unfortunately a lot of guessing. Whats your current value for the innodb_buffer_pool_size?

    Thanked by 1fayaz90
  • mariadb is - besides innodb_buffer_pool_size and those other few keys - pretty much config-less

    databases usally scale linear with cpu threads

    if your server runs at 35 load isrsly suggest u optimize your queries

    hint: look into slow_query_log and log queries not using indexes

  • @jazzii said:
    Without much more information, it's unfortunately a lot of guessing. Whats your current value for the innodb_buffer_pool_size?

    innodb_buffer_pool_size = 40G
    innodb_log_file_size = 10G
    max_allowed_packet = 256M
    open_files_limit = 4000000
    innodb_file_per_table = 1
    max_connections = 200
    join_buffer_size = 64M
    tmp_table_size = 256M
    max_heap_table_size = 256M

  • Add indexes!!!! Everything will fly

  • Give this plugin a try on your busy WordPress sites: https://wordpress.org/plugins/index-wp-mysql-for-speed/

    BTW: Do you see x-litespeed-cache: hit header in your requests for all websites?

  • Cache. Memcache, redis.

  • @George_Fusioned said:
    Give this plugin a try on your busy WordPress sites: https://wordpress.org/plugins/index-wp-mysql-for-speed/

    BTW: Do you see x-litespeed-cache: hit header in your requests for all websites?

    yes, let me check,

  • @LTniger said:
    Cache. Memcache, redis.

    Lscache, and redis cache

  • @George_Fusioned said:
    Give this plugin a try on your busy WordPress sites: https://wordpress.org/plugins/index-wp-mysql-for-speed/

    BTW: Do you see x-litespeed-cache: hit header in your requests for all websites?

    yes, cache: hit

  • Did you review your PHP logs?

    Did you check with top, or htop, which process is consuming what?

    Thanked by 1fayaz90
  • @SteveMC said:
    Did you review your PHP logs?

    Did you check with top, or htop, which process is consuming what?

    yes,
    htop
    481% /usr/sbin/mysqld/
    40% lsphp

Sign In or Register to comment.