The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MariaDB 10.0.10 incredibly lazy

Discussion in 'Workarounds and Optimization' started by Zbig, May 23, 2014.

  1. Zbig

    Zbig Registered

    Joined:
    May 23, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi! I believe, it must be some improper setting in configuration. The problem is, that the INSERT performance (and overall performance too) is surprisingly poor, but there's more: 4-core CPU actually isn't used. While I'm trying to feed the table with new data, I can see, that just one core (sometimes) works on 5% load; as if MariaDB was refusing to work. It's not the problem of slow hardware. Besides: I use LOAD DATA INFILE to speed-up the data insertion.

    I used my-huge.cnf as base configuration template:

    Code:
    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/run/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/run/mysql/mysql.sock
    skip-external-locking 
    key_buffer_size = 384M 
    max_allowed_packet = 1M
    table_open_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8 
    query_cache_size = 32M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    
    default-storage-engine = aria
    
    log-bin=mysql-bin
    
    server-id       = 1
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout

    The non-listed options have been left commented-out, with their default values.

    I would to add, that - as you can see - I use Aria engine as default, and tables (for future use) have been created with "TRANSACTIONAL=1". The main table is big (around 1 billion = 10^9 rows), but has only 3 columns. The machine has plenty of RAM (16 GB) - perhaps increasing some of the buffer sizes could help? But which ones? And is the problem actually buffer-related?

    To give you picture, how slowly it works: before switching from MySQL to MariaDB 10.0.10 I made a dump of the databases. Restoration (with standard mysqldump utility) of the main one - I mean the one with the large table - took more than 24 hours!

    Any helpful hints are appreciated.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. Zbig

    Zbig Registered

    Joined:
    May 23, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I have to confess: never used "sar" before. Tried it now, but it complained: "Cannot open /var/log/sa/sa23: No such file or directory". "touch /var/log/sa/sa23" also won't give that much, since now it responds: "Invalid system activity file: /var/log/sa/sa23".

    I was using mostly "htop" to examine CPU load. Well, as I wrote, it turned out, that 4-core CPU is barely used. Actually it's idle almost all the time.

    - - - Updated - - -

    When used now (database isn't busy with anything), "sar -u 2" reports something like this:

    Linux 3.7.0 (intel1) 05/23/2014 _x86_64_ (4 CPU)

    10:27:11 AM CPU %user %nice %system %iowait %steal %idle
    10:27:13 AM all 0.00 0.00 0.00 0.00 0.00 100.00
    10:27:15 AM all 0.00 0.00 0.00 0.00 0.00 100.00
    10:27:17 AM all 0.00 0.00 0.00 0.00 0.00 100.00
    10:27:19 AM all 0.00 0.00 0.00 0.00 0.00 100.00
    10:27:21 AM all 0.00 0.00 0.00 0.00 0.00 100.00
    10:27:23 AM all 0.00 0.00 0.00 0.00 0.00 100.00
    10:27:25 AM all 0.00 0.00 0.12 0.00 0.00 99.88
     
  4. Zbig

    Zbig Registered

    Joined:
    May 23, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    One more: during conditional query (select count(8) from table where...), sar reported:

    Linux 3.7.0 (intel1) 05/23/2014 _x86_64_ (4 CPU)

    03:07:58 PM CPU %user %nice %system %iowait %steal %idle
    03:08:00 PM all 8.01 0.00 0.51 15.50 0.00 75.98
    03:08:02 PM all 7.91 0.00 0.64 15.05 0.00 76.40
    03:08:04 PM all 8.07 0.00 0.76 15.64 0.00 75.54
    03:08:06 PM all 7.55 0.00 0.51 15.36 0.00 76.57
    03:08:08 PM all 7.40 0.00 1.02 15.31 0.00 76.28
    03:08:10 PM all 12.80 0.00 1.14 10.14 0.00 75.92
    03:08:12 PM all 16.94 0.00 1.38 6.52 0.00 75.16

    "htop" showed only one core (out of four) busy, and only at 33% load. How can I persuade MariaDB to use full hardware's power?
     
Loading...

Share This Page