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.domene...@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.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.
>
>
>
> ---------------------------------------------------------------------
> 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
