0

I have a joomla web site with about 160.000 articles. I use 10.4.6-MariaDB, nginx and php 7.3.

When I check my server with top, I see a maximum of 3,3 MB of memory usage by MariaDB while I have given it 25GB, or at least this is what I think.

Bellow is my excerpt 50-server.cnf:

# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]
innodb_log_buffer_size=2G
innodb_buffer_pool_size=25G
innodb_file_per_table= ON
sql_bin_log=0;

The above are on a Debian 8, nginx, hosting a joomla 4 alpha 7 cms.

In response to Rick James comments:

The query that takes long is 7 sec:

SELECT DISTINCT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.introtext,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,`wa`.`stage_id` AS `stage_id`,`ws`.`title` AS `stage_title`,`ws`.`condition` AS `stage_condition`,`ws`.`workflow_id` AS `workflow_id`,COUNT(asso2.id)>1 as association
FROM bwn_content AS a
LEFT JOIN `bwn_languages` AS l ON l.lang_code = a.language
LEFT JOIN bwn_users AS uc ON uc.id=a.checked_out
LEFT JOIN bwn_viewlevels AS ag ON ag.id = a.access
LEFT JOIN bwn_categories AS c ON c.id = a.catid
LEFT JOIN bwn_users AS ua ON ua.id = a.created_by
INNER JOIN `bwn_workflow_associations` AS `wa` ON `wa`.`item_id` = `a`.`id`
INNER JOIN `bwn_workflow_stages` AS `ws` ON `ws`.`id` = `wa`.`stage_id`
LEFT JOIN bwn_associations AS asso ON asso.id = a.id AND asso.context='com_content.item'
LEFT JOIN bwn_associations AS asso2 ON asso2.key = asso.key
WHERE `ws`.`condition` IN (1, 0) AND `wa`.`extension`='com_content'
GROUP BY `a`.`id`,`a`.`title`,`a`.`alias`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`state`,`a`.`access`,`a`.`created`,`a`.`created_by`,`a`.`created_by_alias`,`a`.`modified`,`a`.`ordering`,`a`.`featured`,`a`.`language`,`a`.`hits`,`a`.`publish_up`,`a`.`publish_down`,`a`.`catid`,`l`.`title`,`l`.`image`,`uc`.`name`,`ag`.`title`,`c`.`title`,`ua`.`name`,`ws`.`title`,`ws`.`workflow_id`,`ws`.`condition`,`wa`.`stage_id`
ORDER BY a.id DESC

The main table at the above query is

bwn_content is about 1.5G and has about 160.000 records

Show create statement for the above is:

CREATE TABLE `bwn_content` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `asset_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `introtext` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `fulltext` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `state` tinyint(3) NOT NULL DEFAULT 0,
  `catid` int(10) unsigned NOT NULL DEFAULT 0,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(10) unsigned NOT NULL DEFAULT 0,
  `created_by_alias` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(10) unsigned NOT NULL DEFAULT 0,
  `checked_out` int(10) unsigned NOT NULL DEFAULT 0,
  `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `images` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `urls` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `attribs` varchar(5120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `version` int(10) unsigned NOT NULL DEFAULT 1,
  `ordering` int(11) NOT NULL DEFAULT 0,
  `metakey` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `metadesc` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `access` int(10) unsigned NOT NULL DEFAULT 0,
  `hits` int(10) unsigned NOT NULL DEFAULT 0,
  `metadata` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `featured` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'Set if article is featured.',
  `language` char(7) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The language code for the article.',
  `xreference` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'A reference to enable linkages to external data sets.',
  PRIMARY KEY (`id`),
  KEY `idx_access` (`access`),
  KEY `idx_checkout` (`checked_out`),
  KEY `idx_state` (`state`),
  KEY `idx_catid` (`catid`),
  KEY `idx_createdby` (`created_by`),
  KEY `idx_featured_catid` (`featured`,`catid`),
  KEY `idx_language` (`language`),
  KEY `idx_xreference` (`xreference`),
  KEY `idx_alias` (`alias`(191))
) ENGINE=InnoDB AUTO_INCREMENT=165248 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_c
  • How big is the dataset? Probably a lot less than 25GB. How much RAM do you have? Where did 3.3M come from? – Rick James Jul 12 '19 at 5:09
  • I dont know how to calculate the dataset. My ram is 32G. I see 3.3 from top command. This happens on Joomla backend when looking at the articles tables which have almost 165.000 records that makes many to many join with the tag tables with almost 55.000 records – Christoforos Jul 12 '19 at 6:15
  • For optimizing many:many mapping tables, see mysql.rjweb.org/doc.php/… Or provide SHOW CREATE TABLE and a sample SELECT – Rick James Jul 12 '19 at 6:25
1

Your settings are in a [mariadb-10.1] section, so this will not be used by MariaDB 10.4. You need to either move them to e.g. a [mariadb-10.4] or a [mariadb] section.

You can check the actual values used by MariaDB from the command-line client with commands such as:

SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

To find the size of your data + indexes, run e.g. one of these queries:

SELECT 
  round(sum(data_length + index_length) / 1024 / 1024, 1) "DB size (MB)"
FROM information_schema.tables;

This will show the size per schema + the total:

SELECT table_schema "DB name", 
  round(sum(data_length + index_length) / 1024 / 1024, 1) "DB size (MB)"
FROM information_schema.tables
GROUP BY table_schema WITH ROLLUP;

This will show the size per table + total in a particular database:

SELECT table_name "table name",        
  round(sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
WHERE table_schema = 'joomla' 
GROUP BY table_name WITH ROLLUP;
  • Thanks. I have now log_buffer_size ~2G and pool_size ~26G. Still thow when I run a slow query I see at the top command that memory usage is about 13%. How can I achieve something better? – Christoforos Jul 13 '19 at 5:12
  • @Christoforos Presumably, either 13% is enough for your entire dataset or your entire dataset hasn't been accessed since last time you restarted MariaDB so therefore hasn't been loaded into memory yet. There are queries you can run to see how large your dataset is - I'll add that to the answer in a minute. – dbdemon Jul 13 '19 at 9:43
  • seems you are right. My data set is indeed small. It seems it is loaded on the memory and manipulated there. So I have to increase the memory for this process. I am not sure though if it is the memory of nginx or php7.3-fpm that I have to increase. Any suggestion on this? – Christoforos Jul 15 '19 at 6:44
  • @Christoforos If you know that it is the query that is slow (e.g. from the MariaDB slow log), then I suppose the problem is still within the database. You mentioned 55,000 article tags - this sounds a bit excessive (even though it may make sense to you), and the software may not have been designed for that many tags. However, you may be able to speed up the performance by adding or modifying indexes, but this requires careful analysis of the queries, tables and existing indexes. Make sure there are no stale table statistics. This could trick the query optimizer to use a suboptimal exec plan. – dbdemon Jul 15 '19 at 7:26
  • I dont know how to check for stale table statistics – Christoforos Jul 27 '19 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.