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

Locked
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

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

Post by Daniel Obee »

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
Znuny guru
Posts: 2189
Joined: 08 Dec 2005, 17:01
Znuny Version: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster 1.1.0 beta

Post by Andre Bauer »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.0 beta

Post by Daniel Obee »

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
Znuny guru
Posts: 2189
Joined: 08 Dec 2005, 17:01
Znuny Version: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster 1.1.1 beta

Post by Andre Bauer »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.1 beta

Post by Daniel Obee »

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 xxx 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
Znuny guru
Posts: 2189
Joined: 08 Dec 2005, 17:01
Znuny Version: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster 1.1.1 beta

Post by Andre Bauer »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.1 beta

Post by Daniel Obee »

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)
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster 1.1.1 beta

Post by Daniel Obee »

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
Znuny guru
Posts: 2189
Joined: 08 Dec 2005, 17:01
Znuny Version: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Andre Bauer »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Daniel Obee »

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
Znuny guru
Posts: 2189
Joined: 08 Dec 2005, 17:01
Znuny Version: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Andre Bauer »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Daniel Obee »

Btw... do i still need this line...
Nope. The module has a widely rebuilt SQL engine - the quick "fix" by the xxx is obsolete. Never did too much anyway...
Andre Bauer
Znuny guru
Posts: 2189
Joined: 08 Dec 2005, 17:01
Znuny Version: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Andre Bauer »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Daniel Obee »

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
Znuny guru
Posts: 2189
Joined: 08 Dec 2005, 17:01
Znuny Version: 5.0.x
Real Name: André Bauer
Company: Magix Software GmbH
Location: Dresden
Contact:

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Andre Bauer »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: MySQL Booster (current version: 1.1.3 beta)

Post by Daniel Obee »

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
Znuny newbie
Posts: 52
Joined: 28 Apr 2010, 15:16
Znuny Version: 5.0.18
Location: Leinfelden-Echterdingen

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

Post by meilon »

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
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

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

Post by Daniel Obee »

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
Znuny expert
Posts: 250
Joined: 12 Oct 2010, 01:35
Znuny Version: 3.0.9
Company: LRS Health

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

Post by MichaelR »

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
Znuny expert
Posts: 296
Joined: 04 Jul 2010, 17:49
Znuny 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)

Post by Alexander Halle »

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
Znuny newbie
Posts: 2
Joined: 18 May 2012, 18:52
Znuny Version: 3.1.2
Real Name: Mark Heynes

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

Post by mutl3y »

Any news on the 3.1.x version
zhakal
Znuny newbie
Posts: 47
Joined: 30 Jul 2011, 20:06
Znuny Version: 3.0.11

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

Post by zhakal »

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
Locked