Extremely slow MySQL (MariaDB)

Discussion in 'Server Operation' started by Con Hennekens, Sep 12, 2017.

  1. Con Hennekens

    Con Hennekens New Member

    Hi, I wanted to ask a question about the MySQL performance.
    I have an old PC with a Core2Duo Processor that I wanted to setup as a webserver for testing purposes, I installed Proxmox with an SSD, and on that a VM with Ubuntu server and ISPConfig. All done through the excellent manuals on this site. I migrated my Wordpress site to this box but am suffering from really slow performance. I noticed that the time it takes for my wordpress page to start showing anything is incredibly long (like 5 to 10 seconds). So I suspect terrible MariaDB performance. I installed the plugin "PHP/MySQL CPU performance statistics" and this is the result:

    PHP test:
    Time to perform:Math test: 4.20 seconds
    Time to perform:StringManipulation test: 29.40 seconds
    Time to perform:test Loop test: 8.89 seconds


    MySQL test:
    Time to perform: Query test (200 times) : 9.22 seconds (22/sec)
    MySQL tests:
    Time to perform: select BENCHMARK(500000000, EXTRACT(YEAR FROM NOW())) :329.76 seconds
    Time to perform: select BENCHMARK(10000000,ENCODE('hello','goodbye')) : 43.34 seconds
    Time to perform: select BENCHMARK(25000000,1+1*2); : 85.07 seconds
    Total time (all MySQL tests) :458.17 seconds


    I have no comparison whatsoever but this can't be good! I know Wordpress is rather heavy on resources, but looking at the CPU and memory usage that is not very high while generating pages. Any tips to improve this performance?

    This is the PHP / MySQL output:
    Server: [email protected]
    PHP host information: Linux SimplyWeb1 4.4.0-87-generic #110-Ubuntu SMP Tue Jul 18 12:55:35 UTC 2017 x86_64
    PHP version: 7.0.22-0ubuntu0.16.04.1
    PHP memory limit: 256M
    PHP post_max_size: 8M
    PHP upload_max_size: 2M
    PHP max_execution_time: 30 seconds
    PHP extensions loaded Core date libxml openssl pcre zlib filter hash pcntl Reflection SPL session standard cgi-fcgi mysqlnd PDO xml apcu calendar ctype curl dom mbstring fileinfo ftp gd gettext iconv imagick imap intl json ldap exif mcrypt memcache mysqli pdo_mysql pdo_sqlite Phar posix pspell readline recode shmop SimpleXML soap sockets sqlite3 sysvmsg sysvsem sysvshm tidy tokenizer wddx xmlreader xmlrpc xmlwriter xsl zip Zend OPcache
    PHP ini file location: /etc/php/7.0/cgi/php.ini
    Webserver: Test not implemented yet/unknown web server
    Loaded webserver modules: T
    Platform: Linux
    Gateway interface: CGI/1.1
    Path to files: /var/www/clients/client1/web1/web/wp-admin
    MySQL version 10.0.31-MariaDB-0ubuntu0.16.04.2
    Server load now: 0.35
    Server load avg. 5 minutes: 1.35
    Server load avg. 15 minutes: 1.94
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Use a tool like mysqltuner to optimize mariadb settings and use a cache plugin in wordpress to avoid mysql lookups.
     
  3. Con Hennekens

    Con Hennekens New Member

    Thanks till for the swift reply.
    I ran mysqltuner like you suggested. I can't say I completely understand it's output, but I took the recommendations and added them to my.cnf like this:

    # additions following from ./mysqltuner.pl
    tmp_table_size = 16M
    max_heap_table_size = 16M
    performance_schema = ON enable PFS
    query_cache_size = 0
    query_cache_type = 0
    innodb_log_file_size = 16M
    innodb_buffer_pool_instances = 1


    However I notice completely NO difference in performance and when I run mysqltuner again, I get EXACTLY the same recommendations. Yes, I stopped an re-started mysql... Any Ideas?
     
  4. HSorgYves

    HSorgYves Active Member HowtoForge Supporter

    mysql/mariadb need to run for some time for mysqltuner to work correctly
     
  5. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    you can try mytop to see the current mysql-connections. Maybe your server needs more ram to handle all sql-connections. And a core2 is "slow by design". ;)
     
  6. Nogalmarian

    Nogalmarian New Member

    optimize memory and delete old data
     
  7. Con Hennekens

    Con Hennekens New Member

    I am using Proxmox as hypervisor, and manually built a Ubuntu server with ISPConfig on top. I used an old HP Core2Duo PC and have built the server without VT-d support. I installed a second PC with a Core I5 T series processor (2 core with hyperthreading), clusterd proxmox and moved ISPConfig VM to the second PC With alsmost no improvement. So I built a new Server, equal to the first one but with VT-d enabled, and that seems to work blazingly fast. Not sure if VT-d could have this effect, but certainly something was off in the first try.

    Thanks for the tips.
     

Share This Page