Hi all, im doing a migration of the otrs database from a server to another one. When performing the database test Ive 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 dont have any kind of disk usage
looks
like its all in memory
Can anyone help me on this performace issue ? I dont know how to reproduce
the performances of the production server on this new one.
Best regards,
Luca Domenella
T: +39 0698962316
E: luca.domene...@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.
smime.p7s
Description: S/MIME cryptographic signature
--------------------------------------------------------------------- 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
