[otrs] strange performance problems migrating otrs from a server to another one

Steven Carr sjcarr at gmail.com
Tue Feb 14 14:35:22 GMT 2012


Where does tmpdir point to on the production server? is that also pointing
at /tmp which is on disk? and are the disk architectures the same type and
same speed? It definitely sounds like a disk I/O issue.

What version of MySQL is the production server running? What happens if you
use the same my.cnf without making any unnecessary changes to it? (tweaking
memory parameters can cause all sorts of I/O upsets if they aren't balanced
correctly - regardless of how much RAM you have).

Have a look at https://github.com/rackerhacker/MySQLTuner-perl - it's a
perl script which will analyse your MySQL installation and give pointers
for increased performance and stability.

Steve



On 14 February 2012 12:48, Luca Domenella <Luca.Domenella at bwinparty.com>wrote:

> Hi Steve,****
>
> ** **
>
> i said i customized, but its similar to the production server i  just
> changed some values because it have the double of memory, and the server is
> the same type.****
>
> ** **
>
> The tmpdir point to /tmp****
>
> ** **
>
> From os:****
>
> Filesystem            Size  Used Avail Use% Mounted on****
>
> /dev/sda3             125G   41G   77G  35% /****
>
> /dev/sda1             190M   24M  157M  14% /boot****
>
> tmpfs                 3.9G     0  3.9G   0% /dev/shm****
>
> ** **
>
> ** **
>
> br,**
>
> *Luca Domenella*****
>
> T: +39 0698962316
> E:  luca.domenella at bwinparty.com****
>
> *bwin Italia*
> Via Adolfo Ravà, 124
> 00142 Roma (RM)****
>
> www.bwinparty.com****
>
>  ****
>
> This email and any attachments are confidential, and may be legally
> privileged and protected by copyright. If you are not the intended
> recipient dissemination or copying of this email is prohibited. If you have
> received this in error, please notify the sender by replying by email and
> then delete the email completely from your system.
>
> Any views or opinions are solely those of the sender. This communication
> is not intended to form a binding contract unless expressly indicated to
> the contrary and properly authorised. Any actions taken on the basis of
> this email are at the recipient's own risk.****
>
> ** **
>
> *From:* otrs-bounces at otrs.org [mailto:otrs-bounces at otrs.org] *On Behalf
> Of *Steven Carr
> *Sent:* martedì 14 febbraio 2012 13:11
> *To:* User questions and discussions about OTRS.
> *Subject:* Re: [otrs] strange performance problems migrating otrs from a
> server to another one****
>
> ** **
>
> You say you have customised the my.cnf configuration, how does that
> compare with the production server, and has the production server been
> optimised? if it has then there is no guarantee the optimised configuration
> will work the same on a different server. Where is the 'tmpdir' variable
> pointing to? is that on-disk filesystem or memory?
>
> Are the spec's of the servers the same? for disk I/O what type of disks
> are the production servers using and what is the new server? SAS/SATA?
>
> Steve
>
>
> ****
>
> On 14 February 2012 11:18, Luca Domenella <Luca.Domenella at bwinparty.com>
> wrote:****
>
> Hi all,
>
> im doing a migration of the otrs database from a server to another one.
> When
> performing the database test I’ve got strange result when running a query:
> 5
> minutes versus the actual production server : 17secs.
>
> What I did is:
> 1) Newly centos installed server 5.7
> 2) Install mysql 5.5.20 (stable release from dev.mysql.com)
> 3) Backup the db on production server using mysqldump
> 4) Restore using canonical way: mysql –uroot –p otrs < mysql.sql
> 5) Customized the my.cnf
>
> Looks normal…
>
> This is the query that on my production server took 17 secs:
> SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM  ticket st, queue
> sq
> , article at WHERE sq.id = st.queue_id AND st.id = at.ticket_id AND
> sq.group_id IN (1, 3, 4) AND ( LOWER(at.a_body) LIKE
> LOWER('%58674056830%'))
> ORDER BY st.create_time_unix DESC LIMIT000;
>
> I have got this from mysql-slowquery.log on the production server… on this
> new server tooks: Empty set (5 min 11.28 sec)
>
> This is the output of show profile;
> +--------------------------------+------------+
> | Status                         | Duration   |
> +--------------------------------+------------+
> | starting                       |   0.000023 |
> | Waiting for query cache lock   |   0.000004 |
> | checking query cache for query |   0.000098 |
> | checking permissions           |   0.000005 |
> | checking permissions           |   0.000003 |
> | checking permissions           |   0.000005 |
> | Opening tables                 |   0.000031 |
> | System lock                    |   0.000014 |
> | Waiting for query cache lock   |   0.000030 |
> | init                           |   0.000058 |
> | optimizing                     |   0.000017 |
> | statistics                     |   0.000044 |
> | preparing                      |   0.000027 |
> | Creating tmp table             |   0.000037 |
> | executing                      |   0.000004 |
> | Copying to tmp table           | 311.316596 |
> | Sorting result                 |   0.000024 |
> | Sending data                   |   0.000015 |
> | end                            |   0.010888 |
> | removing tmp table             |   0.000011 |
> | end                            |   0.000005 |
> | query end                      |   0.000004 |
> | closing tables                 |   0.000017 |
> | freeing items                  |   0.000018 |
> | Waiting for query cache lock   |   0.000003 |
> | freeing items                  |   0.000283 |
> | Waiting for query cache lock   |   0.000005 |
> | freeing items                  |   0.000002 |
> | storing result in query cache  |   0.000005 |
> | logging slow query             |   0.000003 |
> | logging slow query             |   0.000137 |
> | cleaning up                    |   0.000005 |
> +--------------------------------+------------+
> 32 rows in set (0.03 sec)
>
>
> What comes to eyes is the copying to tmp table that take all the time… when
> I runt this query I also took an iostat on the server (local disks) and the
> disk is 90% used with few iostat % (something like 1-5%) and 5-7MB/s reads…
>
> Now, on the production server I don’t have any kind of disk usage… looks
> like its all in memory…
>
> Can anyone help me on this performace issue ? I don’t know how to reproduce
> the performances of the production server on this new one.
>
> Best regards,
>
> Luca Domenella
> T: +39 0698962316
> E:  luca.domenella at bwinparty.com
> bwin Italia
> Via Adolfo Ravà, 124
> 00142 Roma (RM)
> www.bwinparty.com
>
> This email and any attachments are confidential, and may be legally
> privileged and protected by copyright. If you are not the intended
> recipient
> dissemination or copying of this email is prohibited. If you have received
> this in error, please notify the sender by replying by email and then
> delete
> the email completely from your system.
>
> Any views or opinions are solely those of the sender. This communication is
> not intended to form a binding contract unless expressly indicated to the
> contrary and properly authorised. Any actions taken on the basis of this
> email are at the recipient's own risk.
>
>
>
> ---------------------------------------------------------------------
> OTRS mailing list: otrs - Webpage: http://otrs.org/
> Archive: http://lists.otrs.org/pipermail/otrs
> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs****
>
> ** **
>
> ---------------------------------------------------------------------
> OTRS mailing list: otrs - Webpage: http://otrs.org/
> Archive: http://lists.otrs.org/pipermail/otrs
> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.otrs.org/pipermail/otrs/attachments/20120214/8ed7751e/attachment-0001.html>


More information about the otrs mailing list