MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Moderator: tto

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Postby Daniel Obee » 08 Feb 2011, 00:26

Hi all,

since a while I'm working at solving the worst flaws in the OTRS SQL performance. MySQL Booster 1.1.0 is the newest outcome of this, much more complete and much easier to configure than before.

** Beta Warning: Because my test setup is limited and I couldn't test it for long, please consider this a true beta! I will keep up with bug reports, so please feel free to leave a comment when something fishy happens **

How to install
A) Download, unzip and install the opm file
Your system should work the same as before. Maybe the status view will load somewhat faster, all other enhancements need some configuration.

B) Define the quality of your CustomerID and CustomerUserLogin
Check your config.pm for the CustomerUser DB (defined as $Self->{CustomerUser}) after

Code: Select all

        # customer uniq id
        CustomerKey => 'username',
        # customer #
        CustomerID => 'id',

enter the following lines:

Code: Select all

        # SearchKeyType used in Ticket.pm ticket searchs
        SearchKeyType => {
            CustomerID => 'int',                 # 'int', 'low', 'cas' or 'var' - see UserCustomerID in Map
            CustomerUserLogin => 'low',             # 'int', 'low', 'cas' or 'var' - see UserLogin in Map
        },

'int' and 'var' are OTRS standard. In most cases they are rather useless. Mail addresses and user logins seldom are integer and 'var' needs to be lowered, which causes MySQL not to use indexes. That's why I added the qualities 'low' and 'cas' for databases where the columns in question are already lowered ('low') or case sensitive ('cas'). I think in most setups a username will be lowered due to this, so we profit from intelligent db design.

C) Define the quality of all fields of your database
This guarantees the best possible performance in all customer searches. BTW: Nothing we do here would harm a normal OTRS performance. As everything except 'int' is treated as 'var' and lowered... now, it can't get any worse :)

Example:

Code: Select all

[ 'UserCustomerID', 'UserID',       'id',            1, 1, 'int', '', 0, '_blank' ],
[ 'UserLogin',      'Username',     'username',      1, 1, 'cas', '', 0, '_blank' ],
[ 'UserFirstname',  'Vorname',      'firstname',     1, 1, 'var', '', 0, '_blank' ],
[ 'UserLastname',   'Nachname',     'lastname',      1, 1, 'var', '', 0, '_blank' ],
[ 'UserEmail',      'E-Mail',       'email',         1, 1, 'low', '', 0, '_blank' ],


In a further update of this post I will try to explain what's happening inside the whole thing. By now... test and ((enjoy))!

Greets
Dan

* Update: See later version 1.1.3 down the thread

Andre Bauer
OTRS guru
Posts: 2191
Joined: 08 Dec 2005, 17:01
OTRS Version?: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster 1.1.0 beta

Postby Andre Bauer » 08 Feb 2011, 11:25

There are also some infos about this in the otrs bugtracker:

http://bugs.otrs.org/show_bug.cgi?id=4732

http://bugs.otrs.org/show_bug.cgi?id=6063
Prod: Ubuntu Server 16.04 / Zammad 1.2

DO NOT PM ME WITH OTRS RELATED QUESTIONS! ASK IN THE FORUMS!

OtterHub.org

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.0 beta

Postby Daniel Obee » 08 Feb 2011, 17:44

Uploaded new version 1.1.1 beta:

- Fixed a bug in Kernel/System/CustomerUser/DB.pm that caused an SQL error in PostMasterSearch
- Cleaned up code in Kernel/System/DB.pm
- Included Kernel/System/SearchProfile.pm to speed up the search mask call

Andre Bauer
OTRS guru
Posts: 2191
Joined: 08 Dec 2005, 17:01
OTRS Version?: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster 1.1.1 beta

Postby Andre Bauer » 22 Feb 2011, 17:21

Use it for one day without problems.

Queue views feels much faster now :-)
Prod: Ubuntu Server 16.04 / Zammad 1.2

DO NOT PM ME WITH OTRS RELATED QUESTIONS! ASK IN THE FORUMS!

OtterHub.org

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.1 beta

Postby Daniel Obee » 22 Feb 2011, 18:11

Cool!

It's been 14 days now since I installed it on our productive system and it's been running without any flaws. In fact the database hasn't seen any peaks since then and the slow query log runs empty. That said you should put an index on ticket_history.article_id - deleting tickets took ages without.

Michael of the OTRS AG took the thing for review, I guess we can expect it to somehow be implemented in one of the next major releases.

Greez
dan

Andre Bauer
OTRS guru
Posts: 2191
Joined: 08 Dec 2005, 17:01
OTRS Version?: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster 1.1.1 beta

Postby Andre Bauer » 24 Feb 2011, 11:57

Hmmm... I have still some LOWER entries in my MySQL slow log...

Example:

Code: Select all

SELECT login , first_name, last_name, email, email2 FROM customer_user
WHERE ((login LIKE LOWER('robert123%')
OR LOWER(last_name) LIKE LOWER('robert123%')
OR LOWER(customer_id) LIKE LOWER('robert123%')
OR LOWER(email2) LIKE LOWER('robert123%')) ) AND valid_id IN (1)  LIMIT 250;


Wrong config?
Prod: Ubuntu Server 16.04 / Zammad 1.2

DO NOT PM ME WITH OTRS RELATED QUESTIONS! ASK IN THE FORUMS!

OtterHub.org

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.1 beta

Postby Daniel Obee » 24 Feb 2011, 20:52

Could you post the customer map of you config.pm? Looks like the manual customer search, which is based on the config described in C)

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.1 beta

Postby Daniel Obee » 28 Feb 2011, 18:50

Hi all.

While investigating monotek's case I found another way to further increase performance. To be precise I figured out how to get rid of the omnipresent "LIKE" in the query conditions of the Kernel::System::DB.pm. This now finally leads to index usage when doing a manual customer search.

The module attached is a beta version with the debug logging still on. You might wanna comment it out if not interested.

((enjoy))

Dan
You do not have the required permissions to view the files attached to this post.

Andre Bauer
OTRS guru
Posts: 2191
Joined: 08 Dec 2005, 17:01
OTRS Version?: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Andre Bauer » 01 Mar 2011, 18:35

Sorry for the delay...


Code: Select all

    # CustomerUser
    # (customer user database backend and settings)
    $Self->{CustomerUser} = {
        Name => 'Database Backend',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            Table => 'customer_user',
            # mysql lower() fix
            CaseSensitive => 1,
        },
    # customer uniq id
    CustomerKey => 'login',
    # customer #
    CustomerID => 'customer_id',
    # SearchKeyType used in Ticket.pm ticket searchs
    SearchKeyType => {
        CustomerID => 'int',                 # 'int', 'low', 'cas' or 'var' - see UserCustomerID in Map
        CustomerUserLogin => 'low',             # 'int', 'low', 'cas' or 'var' - see UserLogin in Map
    },
    CustomerValid => 'valid_id',
    CustomerUserListFields => ['first_name', 'last_name', 'email', 'email2'],
    CustomerUserSearchFields => ['login', 'last_name', 'customer_id', 'email2'],
    CustomerUserSearchPrefix => '',
    CustomerUserSearchSuffix => '*',
    CustomerUserSearchListLimit => 250,
    CustomerUserPostMasterSearchFields => ['email','email2'],
    CustomerUserNameFields => ['salutation', 'first_name', 'last_name'],
    CustomerUserEmailUniqCheck => 1,
   AdminSetPreferences => 1,
#   # just a read only source
   ReadOnly => 1,

    Map => [
        # note: Login, Email and CustomerID needed!
        # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
        [ 'UserSalutation', 'Salutation', 'salutation', 1, 0, 'var', '', 0 ],
        [ 'UserFirstname',  'Firstname',  'first_name', 1, 1, 'var', '', 0 ],
        [ 'UserLastname',   'Lastname',   'last_name',  1, 1, 'var', '', 0 ],
        [ 'UserCompany',    'Company',    'company',   1, 0, 'var', '', 0 ],
        [ 'UserStreet',       'Street',      'street',       1, 0, 'var', '', 0 ],
        [ 'UserZip',          'Zip',         'zip',          1, 0, 'var', '', 0 ],
        [ 'UserCity',         'City',        'city',         1, 0, 'var', '', 0 ],
        [ 'UserCountry',      'Country',     'country',      1, 0, 'var', '', 0 ],
        [ 'UserEmail',      'E-Mail',  'email', 1, 1, 'low', '$Env{"CGIHandle"}?Action=AgentTicketPhone&Subaction=StoreNew&ExpandCustomerName=1&From=$Data{"UserEmail"}&CustomerID=$Data{"CustomerID"}', 0 ],
        [ 'UserEmail2',      'E-Mail2', 'email2', 1, 0, 'low', '$Env{"CGIHandle"}?Action=AgentTicketPhone&Subaction=StoreNew&ExpandCustomerName=1&From=$Data{"UserEmail2"}&CustomerID=$Data{"CustomerID"}', 0 ],
        [ 'UserCustomerID', 'CustomerID', 'customer_id', 1, 0, 'int', 'https://secretlink/secretfile.php?mod=showUser&customerid=$Data{"CustomerID"}', 0, '_blank' ],
        [ 'UserPhone',        'Phone (VOIP Call)',       'phone',        1, 0, 'var', 'click2dial.pl?agent=$Env{"UserComment"}&customer=$Data{"UserPhone"}', 0 ],
        [ 'UserFax',          'Fax',         'fax',          0, 0, 'var', '', 0 ],
    ],

};


Prod: Ubuntu Server 16.04 / Zammad 1.2

DO NOT PM ME WITH OTRS RELATED QUESTIONS! ASK IN THE FORUMS!

OtterHub.org

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Daniel Obee » 01 Mar 2011, 18:42

There was a small glitch in the DB.pm that caused the system to ignore 'int'. Fixed that in 1.1.3 plus the LIKE enhancement.

Try the new opm, it does it's work on our productive system since yesterday - now probs by now.

You should remove the customersearchsuffix though. This makes the LIKE necessary in each and every search - hindering the system from using the indexes. If necessary the agents still can use foo* if necessary.

Using 1.1.3 and now CustomerSearchSuffix you'd get:

Code: Select all

SELECT login , first_name, last_name, email, email2 FROM customer_user
WHERE (( login = ('robert123')
OR LOWER(last_name) = LOWER('robert123')
OR LOWER(customer_id) = ('robert123')
OR LOWER(email2) = LOWER('robert123')) ) AND valid_id IN (1)  LIMIT 250;


That's using two indexes (login and customer_id) instead of none.

(BTW: I don't see email2 in your map...)

Greets
Dan

Andre Bauer
OTRS guru
Posts: 2191
Joined: 08 Dec 2005, 17:01
OTRS Version?: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Andre Bauer » 01 Mar 2011, 19:34

I removed some lines... Maybe a little bit to much *g*

I will try the new opm...

Set:

Code: Select all

CustomerUserSearchSuffix => ''


Btw... do i still need this line:

Code: Select all

            # mysql lower() fix
            CaseSensitive => 1,
Prod: Ubuntu Server 16.04 / Zammad 1.2

DO NOT PM ME WITH OTRS RELATED QUESTIONS! ASK IN THE FORUMS!

OtterHub.org

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Daniel Obee » 01 Mar 2011, 21:36

Btw... do i still need this line...


Nope. The module has a widely rebuilt SQL engine - the quick "fix" by the OTRS AG is obsolete. Never did too much anyway...

Andre Bauer
OTRS guru
Posts: 2191
Joined: 08 Dec 2005, 17:01
OTRS Version?: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Andre Bauer » 02 Mar 2011, 11:27

Where can i disable debug?

Is a sysconfig option available?
Prod: Ubuntu Server 16.04 / Zammad 1.2

DO NOT PM ME WITH OTRS RELATED QUESTIONS! ASK IN THE FORUMS!

OtterHub.org

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Daniel Obee » 02 Mar 2011, 11:36

Just go to System/DB.pm (sub QuerieCondition) and remove (or outcomment) the log entries.

Lines:
1040
1061
1071
1096
1102
1109
1115

The part to be removed:

Code: Select all

# debug
                    $Self->{LogObject}->Log[...]


Or just leave it like that - I'll post a final version without those soon. And it doesn't really hurt either.

Greets
Dan

Andre Bauer
OTRS guru
Posts: 2191
Joined: 08 Dec 2005, 17:01
OTRS Version?: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Andre Bauer » 02 Mar 2011, 11:49

A sysconfig option would be nice :-)
Prod: Ubuntu Server 16.04 / Zammad 1.2

DO NOT PM ME WITH OTRS RELATED QUESTIONS! ASK IN THE FORUMS!

OtterHub.org

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Postby Daniel Obee » 07 Mar 2011, 12:54

Okay. I re-built the package without logging now.

Given that it's running on our system since a while and had a perfect runtime I now declare release candidate.

Greets
Dan

Edit: In an absent moment I called the version Alpha. Should be at least release candidate
You do not have the required permissions to view the files attached to this post.

meilon
OTRS expert
Posts: 51
Joined: 28 Apr 2010, 15:16
OTRS Version?: 5.0.18
Location: Leinfelden-Echterdingen

Re: MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Postby meilon » 21 Mar 2011, 13:46

Sounds good, how about a Version for OTRS 3.x?
OTRS 5.0.18 :: ITSM auf Apache 2.4.18 (Ubuntu), Perl 5.22.1 mit Ubuntu Server 16.04.2
Agent Auth über ActiveDirectory, Customer Auth mit Kerberos SingleSignOn über ActiveDirectory

User avatar
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Postby Daniel Obee » 21 Mar 2011, 14:21

Since I don't run a 3.0 by now it might take me a moment to port it, but most probably I will.

Until that feel free to do so :) Actually I don't think it's too much work since the files haven't changed that much from 2.4 to 3.0 - if at all.

Greets
Dan

MichaelR
OTRS superhero
Posts: 250
Joined: 12 Oct 2010, 01:35
OTRS Version?: 3.0.9
Company: LRS Health

Re: MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Postby MichaelR » 05 Apr 2011, 03:28

Just checking to see if a port to 3.x is in the works? This patch looks exciting!
OTRS: 3.0.9 & ITSM 3.0.4 - OS: Windows 7 - DB: MySQL - Heaps of random/useful hacks :)
[Major Code Changes]
ArticleFreeTime1-3
Ability to search ArticleFreeText

Alexander Halle
OTRS superhero
Posts: 296
Joined: 04 Jul 2010, 17:49
OTRS Version?: 3.1.x
Real Name: Alexander Halle
Company: radprax MVZ GmbH
Location: Wuppertal
Contact:

Re: MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Postby Alexander Halle » 09 Apr 2011, 15:09

MichaelR wrote:Just checking to see if a port to 3.x is in the works? This patch looks exciting!


The optimization of the SQL code is an ongoing topic in the OTRS Community Board (OCB) and one of our first projects for teamwork betwenn the OTRS community and the OTRS company group. We are constantly in search of testers and contributors to speed the whole process up. The more people test and extend the code the faster this project will be finished. (In contrast there won't be weekly achievements or so if Daniel works almost alone on this code or if we wait for the OTRS company group to do the work alone. One has to use the power of open source.)
Alexander Halle
System: OTRS 3.1.x, Ubuntu 10.04.x LTS, MySQL 5.1.x, Apache 2.2.x
OTRS Community Links: User Meetings, Projects

mutl3y
OTRS newbie
Posts: 2
Joined: 18 May 2012, 18:52
OTRS Version?: 3.1.2
Real Name: Mark Heynes

Re: MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Postby mutl3y » 22 Jun 2012, 16:53

Any news on the 3.1.x version

zhakal
OTRS newbie
Posts: 47
Joined: 30 Jul 2011, 20:06
OTRS Version?: 3.0.11

Re: MySQL Booster (current version: 1.1.4 RC for OTRS 2.4.x)

Postby zhakal » 12 Jul 2012, 22:54

Also awaiting any news around 3.1.X or any way to contribute on it.
OTRS 3.0.11 , RHLE 5.6 & CentOS 6
MSSQL 2008 R2 , Windows 2008 R2


Return to “Patches / Modules”

Who is online

Users browsing this forum: No registered users and 1 guest