MySQL Forums :: Performance :: Simple performance question - differs on 2 computers


Advanced Search

Simple performance question - differs on 2 computers
Posted by: John Melton ()
Date: February 27, 2009 07:01PM

Hi, I have what I believe to be a simple problem that I just can't figure out. I am getting 2 different performance results from the same query on 2 different systems. I am also running 5.1 on 1 machine and 5.0 on the other, but I can't imagine it would make a difference for this simple query.
System 1 - Windows laptop running mysql 5.1
System 2 - Linux desktop running mysql 5.0

I have the exact same schema and a very similar amount of data on both systems (the laptop has a backup of data from the desktop from a few days ago. I have run show index on all the associated tables, and analyze table on all tables on both systems. The only variance between system from what I can see on the indexes appear for this 1 table:

System 1 results
mysql> show index from signature;
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| signature | 0 | PRIMARY | 1 | sig_id | A | 33 | NULL | NULL | | BTREE | |
| signature | 1 | sign_idx | 1 | sig_name | A | NULL | 20 | NULL | | BTREE | |
| signature | 1 | sig_class_id_idx | 1 | sig_class_id | A | NULL | NULL | NULL | | BTREE | |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+


System 2 results
mysql> show index from signature;
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| signature | 0 | PRIMARY | 1 | sig_id | A | 36 | NULL | NULL | | BTREE | |
| signature | 1 | sign_idx | 1 | sig_name | A | 36 | 20 | NULL | | BTREE | |
| signature | 1 | sig_class_id_idx | 1 | sig_class_id | A | 7 | NULL | NULL | | BTREE | |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+


Here is the actual explain query that I ran:
explain SELECT e.*, ip.*, s.*, d.*,
sig.*, tcp.*, udp.*, icmp.*, opt.* FROM
event e inner join iphdr ip on e.cid = ip.cid and e.sid = ip.sid
inner join sensor s on e.sid = s.sid inner join data d on e.sid =
d.sid and e.cid = d.cid inner join signature sig on e.signature =
sig.sig_id left outer join tcphdr tcp on e.sid = tcp.sid and e.cid =
tcp.cid left outer join udphdr udp on e.sid = udp.sid and e.cid =
udp.cid left outer join icmphdr icmp on e.sid = icmp.sid and e.cid =
icmp.cid left outer join opt opt on e.sid = opt.sid and e.cid =
opt.cid ORDER BY e.timestamp DESC LIMIT 25;


Here is the explain results from system 1
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+------+-------------+
| 1 | SIMPLE | s | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | e | index | PRIMARY,sig,idx_sid_cid_timestamp | time | 8 | NULL | 8 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | sig | eq_ref | PRIMARY | PRIMARY | 4 | snort.e.signature | 1 | |
| 1 | SIMPLE | tcp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | udp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | icmp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | opt | ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 3 | |
| 1 | SIMPLE | ip | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.d.cid | 1 | Using where |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+------+-------------+

and here are the explain results from system 2

+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+--------+----------------+
| 1 | SIMPLE | s | system | PRIMARY | NULL | NULL | NULL | 1 | Using filesort |
| 1 | SIMPLE | e | range |PRIMARY,sig,idx_sid_cid_timestamp | PRIMARY | 4 | NULL | 528165 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | sig | eq_ref | PRIMARY | PRIMARY | 4 | snort.e.signature | 1 | |
| 1 | SIMPLE | tcp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | udp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | icmp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | opt | ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 3 | |
| 1 | SIMPLE | ip | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.d.cid | 1 | Using where |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+--------+----------------+

I can't understand why the results from system 2 show using filesort, and why the rows are 528165 whereas on system 1 filesort is not used and the rows are 8.

Could someone please help?

Options: ReplyQuote


Subject Views Written By Posted
Simple performance question - differs on 2 computers 2492 John Melton 02/27/2009 07:01PM
Re: Simple performance question - differs on 2 computers 1588 Rick James 02/27/2009 07:41PM
Re: Simple performance question - differs on 2 computers 1593 John Melton 02/27/2009 09:15PM
Re: Simple performance question - differs on 2 computers 1539 Rick James 02/28/2009 10:42AM
Re: Simple performance question - differs on 2 computers 1576 John Melton 02/28/2009 08:12PM
Re: Simple performance question - differs on 2 computers 1549 Rick James 03/01/2009 12:12AM
Re: Simple performance question - differs on 2 computers 1608 John Melton 03/01/2009 03:20PM
Re: Simple performance question - differs on 2 computers 1583 Rick James 03/01/2009 09:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.