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

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 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@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@otrs.org [mailto:otrs-bounces@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 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@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