[MySQL] query op grote tabel is erg traag

[Verwijderd]

Het uitvoeren van een SQL query op een tabel(MyISAM) met 34M records (9Gb) duurt af en toe meer dan een minuut. Dit probleem hebben we sinds een week en kunnen niet vinden waar het aan ligt.
Op de machine waar de MySql server draait en de database is helemaal niets veranderd.

Specificaties van de machine:

• MS Windows 2003 SP2
• 2 x Xeon 3.2GHz
• 4GB intern geheugen
• MySql versie 5.0.41

SQL:

SELECT Object_Id, UtcDateTime, Longitude, Latitude, Street, Postcode, Town, 
  County, Country, Event, Heading, Speed, Reg 
FROM pre_history 
WHERE Object_Id = 85 AND 
  UtcDateTime BETWEEN 20070620000000 AND 20070628235959 
ORDER BY UtcDateTime

Voorheen deed deze query er ongeveer 1sec over, maar nu dus meestal langer dan een minuut.

Indexen:
Object_Id
UtcDateTime

SERVER VARIABLEN:

code:

Variable_name   Value
auto_increment_increment    1
auto_increment_offset   1
automatic_sp_privileges ON
back_log    50
basedir D:\MySQL\MySQL Server 5.0\
binlog_cache_size   32768
bulk_insert_buffer_size 8388608
character_set_client    latin1
character_set_connection    latin1
character_set_database  latin1
character_set_filesystem    binary
character_set_results   latin1
character_set_server    latin1
character_set_system    utf8
character_sets_dir  D:\MySQL\MySQL Server 5.0\share\charsets\
collation_connection    latin1_swedish_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
completion_type 0
concurrent_insert   1
connect_timeout 5
datadir D:\MySQL\MySQL Server 5.0\Data\
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size  1000
div_precision_increment 4
engine_condition_pushdown   OFF
expire_logs_days    0
flush   OFF
flush_time  1800
ft_boolean_syntax   "+ -><()~*:""""&|"
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit    20
ft_stopword_file    (built-in)
group_concat_max_len    1024
have_archive    YES
have_bdb    NO
have_blackhole_engine   YES
have_compress   YES
have_crypt  NO
have_csv    NO
have_dynamic_loading    YES
have_example_engine NO
have_federated_engine   YES
have_geometry   YES
have_innodb DISABLED
have_isam   NO
have_merge_engine   YES
have_ndbcluster NO
have_openssl    DISABLED
have_ssl    DISABLED
have_query_cache    YES
have_raid   NO
have_rtree_keys YES
have_symlink    YES
hostname    rx300
init_connect    ""
init_file   ""
init_slave  ""
[innodb]
interactive_timeout 28800
join_buffer_size    131072
key_buffer_size 402653184
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100
language    D:\MySQL\MySQL Server 5.0\share\english\
large_files_support ON
large_page_size 0
large_pages OFF
lc_time_names   en_US
license GPL
local_infile    ON
log OFF
log_bin ON
log_bin_trust_function_creators OFF
log_error   D:\MySQL\MySQL Server 5.0\Data\rx300.err
log_queries_not_using_indexes   OFF
log_slave_updates   OFF
log_slow_queries    ON
log_warnings    1
long_query_time 10
low_priority_updates    OFF
lower_case_file_system  ON
lower_case_table_names  1
max_allowed_packet  1047552
max_binlog_cache_size   4294967295
max_binlog_size 1073741824
max_connect_errors  10
max_connections 500
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads  20
max_join_size   4294967295
max_length_for_sort_data    1024
max_prepared_stmt_count 16382
max_relay_log_size  0
max_seeks_for_key   4294967295
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    4294967295
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   2147483647
myisam_recover_options  OFF
myisam_repair_threads   1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
named_pipe  OFF
net_buffer_length   16384
net_read_timeout    30
net_retry_count 10
net_write_timeout   60
new OFF
old_passwords   ON
open_files_limit    2048
optimizer_prune_level   1
optimizer_search_depth  62
pid_file    D:\MySQL\MySQL Server 5.0\Data\rx300.pid
port    3306
preload_buffer_size 32768
profiling   OFF
profiling_history_size  15
protocol_version    10
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    33554432
query_cache_type    ON
query_cache_wlock_invalidate    OFF
query_prealloc_size 8192
range_alloc_block_size  2048
read_buffer_size    2093056
read_only   OFF
read_rnd_buffer_size    8384512
relay_log_purge ON
relay_log_space_limit   0
rpl_recovery_rank   0
secure_auth OFF
secure_file_priv    ""
shared_memory   OFF
shared_memory_base_name MYSQL
server_id   1
skip_external_locking   ON
skip_networking OFF
skip_show_database  OFF
slave_compressed_protocol   OFF
slave_load_tmpdir   C:\WINDOWS\TEMP\
slave_net_timeout   3600
slave_skip_errors   OFF
slave_transaction_retries   10
slow_launch_time    2
sort_buffer_size    2097144
sql_big_selects ON
sql_mode    ""
sql_notes   ON
sql_warnings    OFF
ssl_ca  ""
ssl_capath  ""
ssl_cert    ""
ssl_cipher  ""
ssl_key ""
storage_engine  MyISAM
sync_binlog 0
sync_frm    ON
system_time_zone    W. Europe Daylight Time
table_cache 512
table_lock_wait_timeout 50
table_type  MyISAM
thread_cache_size   8
thread_stack    196608
time_format %H:%i:%s
time_zone   SYSTEM
timed_mutexes   OFF
tmp_table_size  33554432
tmpdir  C:\WINDOWS\TEMP\
transaction_alloc_block_size    8192
transaction_prealloc_size   4096
tx_isolation    REPEATABLE-READ
updatable_views_with_limit  YES
version 5.0.41-community-nt-log
version_comment MySQL Community Edition (GPL)
version_compile_machine ia32
version_compile_os  Win32
wait_timeout    28800

SERVER STATUS:

code:

Variable_name   Value
Aborted_clients 607
Aborted_connects    4
Binlog_cache_disk_use   0
Binlog_cache_use    0
Bytes_received  159
Bytes_sent  257
Com_admin_commands  0
Com_alter_db    0
Com_alter_table 0
Com_analyze 0
Com_backup_table    0
Com_begin   1
Com_change_db   0
Com_change_master   0
Com_check   0
Com_checksum    0
Com_commit  0
Com_create_db   0
Com_create_function 0
Com_create_index    0
Com_create_table    0
Com_create_user 0
Com_dealloc_sql 0
Com_delete  0
Com_delete_multi    0
Com_do  0
Com_drop_db 0
Com_drop_function   0
Com_drop_index  0
Com_drop_table  0
Com_drop_user   0
Com_execute_sql 0
Com_flush   0
Com_grant   0
Com_ha_close    0
Com_ha_open 0
Com_ha_read 0
Com_help    0
Com_insert  0
Com_insert_select   0
Com_kill    0
Com_load    0
Com_load_master_data    0
Com_load_master_table   0
Com_lock_tables 0
Com_optimize    0
Com_preload_keys    0
Com_prepare_sql 0
Com_purge   0
Com_purge_before_date   0
Com_rename_table    0
Com_repair  0
Com_replace 0
Com_replace_select  0
Com_reset   0
Com_restore_table   0
Com_revoke  0
Com_revoke_all  0
Com_rollback    0
Com_savepoint   0
Com_select  3
Com_set_option  0
Com_show_binlog_events  0
Com_show_binlogs    0
Com_show_charsets   0
Com_show_collations 0
Com_show_column_types   0
Com_show_create_db  0
Com_show_create_table   0
Com_show_databases  0
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status  0
Com_show_keys   0
Com_show_logs   0
Com_show_master_status  0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables    0
Com_show_privileges 0
Com_show_processlist    0
Com_show_slave_hosts    0
Com_show_slave_status   0
Com_show_status 1
Com_show_storage_engines    0
Com_show_tables 0
Com_show_triggers   0
Com_show_variables  0
Com_show_warnings   0
Com_slave_start 0
Com_slave_stop  0
Com_stmt_close  0
Com_stmt_execute    0
Com_stmt_fetch  0
Com_stmt_prepare    0
Com_stmt_reset  0
Com_stmt_send_long_data 0
Com_truncate    0
Com_unlock_tables   0
Com_update  0
Com_update_multi    0
Com_xa_commit   0
Com_xa_end  0
Com_xa_prepare  0
Com_xa_recover  0
Com_xa_rollback 0
Com_xa_start    0
Compression OFF
Connections 13038
Created_tmp_disk_tables 0
Created_tmp_files   22
Created_tmp_tables  1
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  0
Handler_delete  0
Handler_discover    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    0
Handler_read_next   0
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   131
[innodb]
Key_blocks_not_flushed  0
Key_blocks_unused   341992
Key_blocks_used 80053
Key_read_requests   8863173
Key_reads   323474
Key_write_requests  606404
Key_writes  605035
Last_query_cost 0.000000
Max_used_connections    139
Not_flushed_delayed_rows    0
Open_files  199
Open_streams    0
Open_tables 100
Opened_tables   0
Prepared_stmt_count 0
Qcache_free_blocks  461
Qcache_free_memory  26032384
Qcache_hits 221355
Qcache_inserts  42361
Qcache_lowmem_prunes    0
Qcache_not_cached   515401
Qcache_queries_in_cache 6596
Qcache_total_blocks 13681
Questions   6979513
Rpl_status  NULL
Select_full_join    0
Select_full_range_join  0
Select_range    0
Select_range_check  0
Select_scan 1
Slave_open_temp_tables  0
Slave_retried_transactions  0
Slave_running   OFF
Slow_launch_threads 0
Slow_queries    0
Sort_merge_passes   0
Sort_range  0
Sort_rows   0
Sort_scan   0
[ssl]
Table_locks_immediate   721090
Table_locks_waited  2211
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  2
Threads_connected   61
Threads_created 3085
Threads_running 1
Uptime  86655
Uptime_since_flush_status   86655

Het is veel informatie, maar ik hoop dat iemand me met dit probleem kan helpen, want ik zit met de handen in het haar...

Alvast bedankt!

[ Voor 22% gewijzigd door een moderator op 29-06-2007 19:26 ]

[Voutloos]

En de tabel definitie? Een index als index(Object_Id, UtcDateTime) klinkt wel handig voor die query, als een id dus blijkbaar veel voor kan komen.

[Gerco]

Ik zou zeggen: Begin eens met een EXPLAIN van die query om te zien of MySQL de indexen wel gebruikt. Er is weliswaar niets aan veranderd, maar het soort probleem wat je hebt is meestal in de indexen terug te vinden.

Een gecombineerde index op Object_Id,UtcDateTime is misschien ook een idee, maar alleen als MySQL de twee bestaande indexen niet kan combineren (geen idee of MySQL dat kan namelijk).

[gorgi_19]

Ondersteunde MySQL niet maar 1 index per table?

quote: http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.

[Gonadan]

gorgi_19 schreef op donderdag 28 juni 2007 @ 15:27:
Ondersteunde MySQL niet maar 1 index per table?

Welnee.
Dat stukje van jou zegt dat als er geen gecombineerde index is de optimizer de index probeert te vinden die de meeste restricties op het resultaat legt.

[Voutloos]

Maar goed, uberhaupt is explain een van de 1e dingen die je moet doen en dan kan je gauw genoeg constateren dat nu bijvoorbeeld de Object_id index gebruikt wordt, en een paupere filesort nodig is voor de order by.

Explain gebruiken is standaard mysql performance debugwerk, daar is echt geen discussie over al dan niet (in bepaalde versies) missende mysql features voor nodig.

[stimpie79]

slightly off-topic: kent mySql RUNSTATS of dergelijke om de statistieken goed te zetten?
--> stel dat mySql denkt dat er maar 50 rijen zijn, dan gaat hij geen index gebruiken maar een tablescan, maar in werkelijkheid komt hij er veel meer tegen...

[Verwijderd]

De index is inderdaad het Object_Id i.c.m. UtcDateTime en het id kan vaak voorkomen.

Hieronder de tabel definitie

code:

CREATE TABLE `ots_history` (
  `Object_Id` int(10),
  `UtcDateTime` datetime,
  `Latitude` char(10) default NULL,
  `Longitude` char(10) default NULL,
  `Speed` double default 0,
  `Heading` double default 0,
  `Street` char(40),
  `Town` char(40),
  `Country` char(3),
  `Street` char(40),
  `County` char(40),
  `Postcode` char(10),  
  KEY `IdxHistory` (`Object_Id`,`UtcDateTime`)
) ENGINE=MyISAM;

[Creepy]

En de EXPLAIN van die query zoals door andere ook al is gevraagd?

[ Voor 39% gewijzigd door Creepy op 28-06-2007 15:51 ]

[Verwijderd]

Hieronder het resultaat van de EXPLAIN:

id = 1
select_type = SIMPLE
table = pre_history
type = ref
possible_keys = Object_Id, UtcDateTime
key = IdxHistory
key_len = 5
ref = const
rows = 108101
Using where; Using filesort

[ Voor 33% gewijzigd door Verwijderd op 28-06-2007 16:25 ]

[Voutloos]

Wat is het verschil tussen ots_history en pre_history?
Want met die index zou je toch geen filesort moeten hebben.

[Verwijderd]

ots_history moet pre_history zijn.

Ik kan ook geen logische oorzaak vinden waarom dit zo lang duurt
Echt frustrerend!

[ Voor 58% gewijzigd door Verwijderd op 28-06-2007 16:29 ]

[Confusion]

gorgi_19 schreef op donderdag 28 juni 2007 @ 15:27:
Ondersteunde MySQL niet maar 1 index per table?

Je kan er meerdere maken, maar in queries wordt maximaal 1 index per table gebruikt. Vaak biedt een multicolumn index uitkomst (waarbij je er, in geval van InnoDB, rekening mee moet houden dat de PK sowieso achter iedere index geplakt wordt, dus dat je die niet nog eens los hoeft te noemen).

Zou het probleem kunnen zijn dat de indices van alle tabellen samen zo groot geworden is dat deze het aan MySQL beschikbaar gestelde geheugen uitloopt?

[ Voor 14% gewijzigd door Confusion op 28-06-2007 16:32 ]

[gvanh]

Kan het zijn dat er boven een bepaalde bestandsgrootte voor de tabel ineens een "magische" grens overschreden is, waardoor je filesystem of MySQL ineens anders reageert op queries?

Probeer de tabel eens te kopieren en dan steeds 1% van de records te verwijderen. Na elke verwijdering opnieuw de query draaien. Wellicht dat dat tot verrassende resultaten leidt?

[Verwijderd]

De nieuwe profiler al geprobeerd?
http://dev.mysql.com/tech...g-new-query-profiler.html

[Verwijderd]

Gewoon even de "order by" vergeten, die forceert namelijk dat het gehele resultaat in het geheugen moet worden gepompt.

[Gwaihir]

Ik zou het ook in de ORDER BY zoeken en de filesort die hij nu veroorzaakt. Maar als je eerst de profiler erop kunt draaien weet je dat zeker.

Kun je 'm dwingen die UtcDateTime index te gebruiken en kijken hoe dat uitpakt (FORCE INDEX)? Dat zal, zoals Voutloos opmerkte, de filesort te vermijden, maar heeft als prijs het niet gebruiken van die andere index.

Helpt dat niet goed, dan: is die resultaatset altijd zo groot geweest? Is de set bijvoorbeeld net te groot geworden voor de sort buffer (sort_buffer_size)? Gebruik je vrijwel altijd die volgorde? Dan helpt het waarschijnlijk ook een beetje om de tabel zelf van tijd tot tijd in die volgorde te sorteren.

[Verwijderd]

Een index zorgt er niet magisch voor dat er geen ordering meer plaats hoeft te vinden. Als je niet achteraf wilt orderen dan moet je zorgen dat je complete tabel geordend op tijd opgeslagen wordt.

[ACM]

Hoeveel concurrent werk is er op die tabel? MyISAM is niet zo'n held voor schaalbare toepassingen als er deletes of updates op je tabel worden uitgevoerd terwijl er selects bezig zijn (inserts zijn niet zo'n probleem vziw). Deletes en updates krijgen een full-table-lock bij MyISAM waardoor je select daar op moet wachten.

Hoeveel updates/deletes worden er uitgevoerd? MyISAM herclaimt niet automatisch alle ruimte die vrij komt van de updates/deletes en dat kan tot gevolg hebben dat er 'overhead' ontstaat. Ik heb zelf meegemaakt dat een tabel daardoor een stuk trager door te worstelen was.
't Nadeel in jouw geval is natuurlijk dat je met een tabel van 9GB wel "even" moet wachten tot je een wijziging hebt doorgevoerd.

Hoeveel records voldoen trouwens aan je 'Object_Id = 85 AND UtcDateTime BETWEEN 20070620000000 AND 20070628235959'-clause ?

stimpie79 schreef op donderdag 28 juni 2007 @ 15:46:
slightly off-topic: kent mySql RUNSTATS of dergelijke om de statistieken goed te zetten?
--> stel dat mySql denkt dat er maar 50 rijen zijn, dan gaat hij geen index gebruiken maar een tablescan, maar in werkelijkheid komt hij er veel meer tegen...

Ja, er is 'analyze table', maar dat is een stuk minder uitgebreid te gebruiken dan runstats en ik durf niet te zeggen hoe goed het werkt bij grote tabellen.

[ Voor 23% gewijzigd door ACM op 29-06-2007 14:37 ]

[igmar]

Vrij simpel : Je resultset is groter als tmp_table_size, waardoor MySQL een filesort gaat gebruiken aangezien je een ORDER BY clause hebt.

[Gwaihir]

ACM schreef op vrijdag 29 juni 2007 @ 14:35:
Hoeveel records voldoen trouwens aan je 'Object_Id = 85 AND UtcDateTime BETWEEN 20070620000000 AND 20070628235959'-clause ?

Verwijderd schreef op donderdag 28 juni 2007 @ 16:22:
Hieronder het resultaat van de EXPLAIN:

[..]
rows = 108101

[ACM]

Birdie schreef op vrijdag 29 juni 2007 @ 16:43:

Vziw maakt explain een schatting... we willen natuurlijk ook weten of die schatting klopt

[Gwaihir]

Hmm... [blader, blader] .. oops ..

http://dev.mysql.com/doc/refman/5.0/en/explain.html

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

Het is niet de omvang van de resultaatset (ook geen schatting dáárvan), maar het aantal rijen dat waarschijnlijk bekeken gaat worden. Waarschijnlijk zijn dat in dit geval alle rijen met Object_Id = 85, ongeacht tijd, die bekeken gaan worden om vast te stellen of ze in te tijdsrange vallen.

Ik sluit me aan bij ACM's vraag : hoe groot is je resultaatset?