MySQL Forums
Forum List  »  Performance

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
2587
February 27, 2009 07:01PM


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.