VP> Can you supply us with an example? Some explain plans to corroborate your
VP> reported slowness.
Of course.
Here is the query.
It is big and ugly, I'm curently working on system optimization. But
why the same query is good at one machine and bad at another?
SELECT ...
FROM positionReports p
INNER JOIN drivers d ON p.driverID=d.ID
LEFT JOIN reverseGeo rg ON rg.latitude=p.latitude AND rg.longitude=p.longitude
LEFT JOIN reverseGeo rg1 ON rg1.latitude=p.cellLatitude AND
rg1.longitude=p.cellLongitude
LEFT JOIN companyPref cp ON cp.companyID = d.companyID
LEFT JOIN events ev ON p.eventID=ev.ID
INNER JOIN eventGroups evg ON ev.eventGroup=evg.ID
LEFT JOIN reportDetails rd ON rd.reportID=p.ID
WHERE p.`date`='2004-03-07' AND
p.`driverID` IN
(92,85,96,93,86,74,72,83,89,97,78,77,84,75,81,91,98,90,88,105,99,100,82,103,73,95,102,94,87,80,76,104,101,489,79)
AND evg.ID IN ('1','2','3','4','5','6','7','8','9','10','11') GROUP BY
1,2,3,4,5,6,7,8,9,10
ORDER BY p.gmtTime DESC, p.time DESC LIMIT 0,201
+-------+--------+---------------------------------------------------------------------------+-------------------------------+---------+----------------+------+----------------------------------------------+
| table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+-------+--------+---------------------------------------------------------------------------+-------------------------------+---------+----------------+------+----------------------------------------------+
| p | range | IDX_POSITIONREPORTS_DRIVER_ID,IDX_POSITIONREPORTS_DATE
| IDX_POSITIONREPORTS_DRIVER_ID | 8 | NULL | 5679 | Using where;
Using temporary; Using filesort |
| d | eq_ref | PRIMARY,IDX_UNIQUE_DRIVERS_ID
| PRIMARY | 8 | p.driverID | 1 |
|
| rg | ref |
IDX_REVERSE_GEO_LATITUDE,IDX_REVERSE_GEO_LONGITUDE,IDX_REVERSE_GEO_LATLON |
IDX_REVERSE_GEO_LATITUDE | 17 | p.latitude | 1 |
|
| rg1 | ref |
IDX_REVERSE_GEO_LATITUDE,IDX_REVERSE_GEO_LONGITUDE,IDX_REVERSE_GEO_LATLON |
IDX_REVERSE_GEO_LATITUDE | 17 | p.cellLatitude | 1 |
|
| cp | ALL | NULL
| NULL | NULL | NULL | 587 |
|
| ev | eq_ref | PRIMARY,IDX_UNIQUE_EVENTS_ID,IDX_UNIQUE_EVENTGROUPS_ID
| PRIMARY | 8 | p.eventID | 1 |
|
| evg | eq_ref | PRIMARY
| PRIMARY | 8 | ev.eventGroup | 1 | Using where;
Using index |
| rd | ref | IDX_REPORTDETAILS_REPORTID
| IDX_REPORTDETAILS_REPORTID | 8 | p.ID | 1 |
|
+-------+--------+---------------------------------------------------------------------------+-------------------------------+---------+----------------+------+----------------------------------------------+
8 rows in set (0.13 sec)
In process list I always see "Copying to tmp table" . I guess the
system is limited by hard disk, top shows low process load.
show variables
Result
# Variable_name Value
1 back_log 50
2 basedir /usr/local/mysql/
3 binlog_cache_size 32768
4 bulk_insert_buffer_size 8388608
5 character_set latin1
6 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8
dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia
hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
7 concurrent_insert ON
8 connect_timeout 5
9 convert_character_set
10 datadir /var/lib/mysql/
11 default_week_format 0
12 delay_key_write ON
13 delayed_insert_limit 100
14 delayed_insert_timeout 300
15 delayed_queue_size 1000
16 flush OFF
17 flush_time 0
18 ft_boolean_syntax + -><()~*:""&|
19 ft_min_word_len 4
20 ft_max_word_len 254
21 ft_max_word_len_for_sort 20
22 ft_stopword_file (built-in)
23 have_bdb NO
24 have_crypt YES
25 have_innodb YES
26 have_isam YES
27 have_raid NO
28 have_symlink YES
29 have_openssl NO
30 have_query_cache YES
31 init_file
32 innodb_additional_mem_pool_size 67108864
33 innodb_buffer_pool_size 268435456
34 innodb_data_file_path ibdata1:2000M:autoextend
35 innodb_data_home_dir /var/lib/mysql/ibdata/
36 innodb_file_io_threads 4
37 innodb_force_recovery 0
38 innodb_thread_concurrency 8
39 innodb_flush_log_at_trx_commit 0
40 innodb_fast_shutdown ON
41 innodb_flush_method fdatasync
42 innodb_lock_wait_timeout 50
43 innodb_log_arch_dir /var/lib/mysql/iblogs/
44 innodb_log_archive OFF
45 innodb_log_buffer_size 52428800
46 innodb_log_file_size 104857600
47 innodb_log_files_in_group 2
48 innodb_log_group_home_dir /var/lib/mysql/iblogs/
49 innodb_mirrored_log_groups 1
50 innodb_max_dirty_pages_pct 90
51 interactive_timeout 28800
52 join_buffer_size 131072
53 key_buffer_size 8388600
54 language /usr/local/mysql/share/mysql/english/
55 large_files_support ON
56 local_infile ON
57 locked_in_memory OFF
58 log OFF
59 log_update OFF
60 log_bin OFF
61 log_slave_updates OFF
62 log_slow_queries ON
63 log_warnings OFF
64 long_query_time 3
65 low_priority_updates OFF
66 lower_case_table_names 0
67 max_allowed_packet 1048576
68 max_binlog_cache_size 4294967295
69 max_binlog_size 1073741824
70 max_connections 2000
71 max_connect_errors 10
72 max_delayed_threads 20
73 max_heap_table_size 16777216
74 max_join_size 4294967295
75 max_relay_log_size 0
76 max_seeks_for_key 4294967295
77 max_sort_length 1024
78 max_user_connections 0
79 max_tmp_tables 32
80 max_write_lock_count 4294967295
81 myisam_max_extra_sort_file_size 268435456
82 myisam_max_sort_file_size 2147483647
83 myisam_repair_threads 1
84 myisam_recover_options OFF
85 myisam_sort_buffer_size 8388608
86 net_buffer_length 16384
87 net_read_timeout 30
88 net_retry_count 10
89 net_write_timeout 60
90 new OFF
91 open_files_limit 10010
92 pid_file /var/lib/mysql/aaa.pid
93 log_error
94 port 3306
95 protocol_version 10
96 query_alloc_block_size 8192
97 query_cache_limit 1048576
98 query_cache_size 0
99 query_cache_type ON
100 query_prealloc_size 8192
101 range_alloc_block_size 2048
102 read_buffer_size 131072
103 read_only OFF
104 read_rnd_buffer_size 262144
105 rpl_recovery_rank 0
106 server_id 0
107 slave_net_timeout 3600
108 skip_external_locking ON
109 skip_networking OFF
110 skip_show_database OFF
111 slow_launch_time 2
112 socket /tmp/mysql.sock
113 sort_buffer_size 2097144
114 sql_mode 0
115 table_cache 64
116 table_type MYISAM
117 thread_cache_size 0
118 thread_stack 126976
119 tx_isolation REPEATABLE-READ
120 timezone EST
121 tmp_table_size 33554432
122 tmpdir /tmp/
123 transaction_alloc_block_size 8192
124 transaction_prealloc_size 4096
125 version 4.0.18-standard-log
126 version_comment Official MySQL-standard binary
127 wait_timeout 28800
my.cnf:
# This is for large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
log-slow-queries
set-variable = long_query_time=3
set-variable = max_connections=2000
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/ibdata/
innodb_data_file_path = ibdata1:2000M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/iblogs/
innodb_log_archive=0
#innodb_log_arch_dir = /var/lib/mysql/iblogs/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 64M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
innodb_log_buffer_size = 50M
#innodb_flush_log_at_trx_commit = 1
#lots of small transactions
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=fdatasync
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
