CODEWITHSUNDEEP
wordpressperformancecachingwoocommercemariadb
sarakinos
sarakinos

Reputation: 686

MariaDB low memory usage

I am running a woocommerce installation at a dedicated server with 32GB of ram. Server uses CentOS , nginx, php-fpm ( version 7 with opcache and apcu ) and MariaDB.

Server performs really well for day to day ecommerce operations. I am also running a custom script for products import which does a lot of intensive queries ( updates products with 6-20 variations with stock, price, description and other meta information ) .

I've been measuring the script execution time and it ran at 80secs max.

The problem is that in the last days the script runs extreamly slow at about 1500secs on average.

There were no changes at the server or the script itselfe. The only problem i've found is that when the script runs, mariadb uses 100% cpu (there was not such case before).

Here is my.cnf

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid


innodb_buffer_pool_size = 13G
innodb_buffer_pool_instances = 8

innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1

tmp_table_size = 128M
max_heap_table_size = 128M

table_open_cache = 4000
table_definition_cache = 4000

key_buffer_size = 64M

thread_cache = 64

query_cache_type = 1
query_cache_limit = 4M
query_cache_size = 64M

It seems that the server doesnt use any of the available ram, during the script execution , server uses only 768mb of the 32GB available.

MariaDB had to utilize 13GB at start, isnt that the point of inno_db_pool_size?

Upvotes: 0

Views: 1280

Answers (2)

sarakinos
sarakinos

Reputation: 686

Just for everyones information, i've just found the problem.

I am looping over all variations of each product in woocommerce and i am updating some metas ( price, stock etc ). The problem was that after every variation loop i've performed wp_flush_cache , so in the next loop wordpress fetched every piece of information again from the Database.

Commenting out the mentioned above line reduced execution time from 1500sec to 30secs.

So the problem was never about mariadb.

Upvotes: 1

danblack
danblack

Reputation: 14666

To determine what parts of your script are slow look at enabling the slow query log.

The queries you do in your scripts combined with the table structure, size, and indexes (SHOW CREATE TABLE {tbl} / SHOW INDEXES FROM {tbl}) will determine their speed.

Its quite likely your tables aren't using Innodb. To see what they are using:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

Upvotes: 0

Related Questions