Database optimization involving reducing primary key length for session tables

Discussion regarding Joomla! 3.x Performance issues.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Post Reply
ggossamer
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 113
Joined: Wed Sep 29, 2010 4:50 pm

Database optimization involving reducing primary key length for session tables

Post by ggossamer » Sat Apr 06, 2019 6:34 pm

One of our senior database developers recently discovered an opportunity for improving joomla performance during a recent audit of our system.

During our Slow Query Log Analysis task the following content was detected for your session table.

Code: Select all

SET timestamp=1553408624;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `adf32s_dbuser_terms`;
# Time: 190324  2:23:59
# User@Host: dbuser[dbuser] @ defiant.linuxsecurity.com [64.1.16.14]
# Thread_id: 211325  Schema: lsdb  QC_hit: No
# Query_time: 1.160679  Lock_time: 0.000090  Rows_sent: 0  Rows_examined: 1
# Rows_affected: 1
#
# explain: idselect_typetabletypepossible_keyskeykey_lenrefrowsr_rowsfilteredr_filteredExtra
# explain: 1SIMPLEadf32s_sessionrangePRIMARYPRIMARY766NULL11.00100.00100.00Using where
#
SET timestamp=1553408639;
UPDATE `adf32s_session`
SET `data` = 'joomla|s:736:\"[spam a4n]==\";'
, `time` = '1553408638'
WHERE `session_id` = 'kvhpjcdrjab9ltssl3af0sng1m';
# User@Host: dbuser[dbuser] @ defiant.linuxsecurity.com [64.1.16.14]
# Thread_id: 211322  Schema: lsdb  QC_hit: No
Observation: The PRIMARY KEY is a length of 766 characters. With INNODB tables,
PRIMARY KEYS are desirable to be much SMALLER and frequently under 10 characters for KEY length.
To achieve this goal, with Joomla's assistance the CREATE TABLE -proposed- could achieve this result
and the resulting table will be smaller and perform better for all customers, if this could
be implemented in their 'roll out' cycle.

CREATE TABLE as of March 25, 2019 on our equipment is:

Code: Select all

CREATE TABLE `adf32s_session` (
  `session_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `client_id` tinyint(3) unsigned DEFAULT NULL,
  `guest` tinyint(4) unsigned DEFAULT 1,
  `time` varchar(14) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `data` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `userid` int(11) DEFAULT 0,
  `username` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY (`session_id`),
  KEY `userid` (`userid`),
  KEY `time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Proposed CREATE TABLE 2019 03 28 11:22 AM Central Time

Code: Select all

CREATE TABLE `adf32s_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `session_id` varchar(191) COLLATE utf8mb4_unicode_ci UNIQUE NOT NULL,
  `client_id` tinyint(3) unsigned DEFAULT NULL,
  `guest` tinyint(4) unsigned DEFAULT 1,
  `time` varchar(14) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `data` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `userid` int(11) DEFAULT 0,
  `username` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT '',
  KEY `s_userid` (`userid`),
  KEY `s_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
During implementation provision will be needed to accomplish the change and migrate existing data. How do we get this information to the joomla database developers so that this change may be made?

ggossamer
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 113
Joined: Wed Sep 29, 2010 4:50 pm

Re: Database optimization involving reducing primary key length for session tables

Post by ggossamer » Sat Apr 06, 2019 6:37 pm

Here is a more in-depth analysis of the issue and description of the solution on stackexchange

https://dba.stackexchange.com/questions ... -key-mysql

User avatar
sozzled
Joomla! Champion
Joomla! Champion
Posts: 6934
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia
Contact:

Re: Database optimization involving reducing primary key length for session tables

Post by sozzled » Sat Apr 06, 2019 7:04 pm

ggossamer wrote:
Sat Apr 06, 2019 6:34 pm
How do we get this information to the joomla database developers so that this change may be made?
Many of the key players in Joomla development have (for reasons we cannot guess) "abandoned" this forum, it seems. The best way to engage the J! development team seems to be to use GitHub.

Having said that, there are (perhaps) broader issues surrounding the use of the database for J! session management including, among other things, the fact that over 90% of all J! users are using outdated and unsupported versions of PHP. From what I've read, there can be issues with cleaning up the database if it's used for J! session management and it may be preferable to use PHP for session handling instead (especially if you're using PHP 7).

For what it's worth—to support the claim that over 90% of J! users are using outdated versions of PHP—see the following table for what versions of PHP are currently supported and the percentage of usage of those versions on J! sites around the world:
Image

I don't know what kind of traction this discussion will get on GitHub but, as you can see, the issue has been debated and contested.
https://www.kuneze.com/blog
Former member of Kunena project team
If you think I’m wrong then say “I think you're wrong.” If you say “You’re wrong!”, how do you know?

User avatar
Slackervaara
Joomla! Guru
Joomla! Guru
Posts: 975
Joined: Sat Aug 13, 2011 6:27 am

Re: Database optimization involving reducing primary key length for session tables

Post by Slackervaara » Tue Apr 09, 2019 6:12 pm

It better to abandon database for session and use the setting PHP. Then the sessions are stored in memory and thats faster access than database. With database session my session file grew to 25 GB and due to that reason I used PHP. Big sites usally use PHP.

ggossamer
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 113
Joined: Wed Sep 29, 2010 4:50 pm

Re: Database optimization involving reducing primary key length for session tables

Post by ggossamer » Tue Apr 09, 2019 6:21 pm

Slackervaara wrote:
Tue Apr 09, 2019 6:12 pm
It better to abandon database for session and use the setting PHP. Then the sessions are stored in memory and thats faster access than database. With database session my session file grew to 25 GB and due to that reason I used PHP. Big sites usally use PHP.
Thanks for your help. Would you explain how this is done with joomla?

User avatar
Slackervaara
Joomla! Guru
Joomla! Guru
Posts: 975
Joined: Sat Aug 13, 2011 6:27 am

Re: Database optimization involving reducing primary key length for session tables

Post by Slackervaara » Tue Apr 09, 2019 6:36 pm

It is very easy. Go to administration > Global settings > System
At the bottom of this page: Session settings
Change: session handler: database to PHP Save.
However when you do this all on the site is logged out including you as admin.

User avatar
AMurray
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4543
Joined: Sat Feb 13, 2010 7:35 am
Location: Australia

Re: Database optimization involving reducing primary key length for session tables

Post by AMurray » Sun Apr 14, 2019 9:52 pm

That being the case, do the change at a time you know there's minimal activity on the site therefore you will disrupt the minimum number of users; but all they have to do is log in again.
Regards,
--------------------------------------------------------------
A Murray
Millennium Falcon - it's the ship that made the Kessel run in less than 12 parsecs! The fastest hunk of junk in the galaxy.


Post Reply

Return to “Performance - Joomla! 3.x”