Re: Query with > 500000 entities in in where clause crashes server
Hi Rick,
thank you very much for your reply. Here my input:
1.)
# mysqlsec -e "show variables like '%buffer%';"
Variable_name Value
bdb_log_buffer_size 262144
bulk_insert_buffer_size 8388608
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_log_buffer_size 1048576
join_buffer_size 131072
key_buffer_size 8489271296
myisam_sort_buffer_size 36700160
net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 131072
read_rnd_buffer_size 262144
sort_buffer_size 2097144
# mysqlsec -e "show variables like '%size';"
Variable_name Value
bdb_cache_size 8384512
bdb_log_buffer_size 262144
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
delayed_queue_size 1000
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
join_buffer_size 131072
key_buffer_size 8489271296
key_cache_block_size 1024
large_page_size 0
max_binlog_cache_size 18446744073709547520
max_binlog_size 1073741824
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_relay_log_size 0
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036853727232
myisam_sort_buffer_size 36700160
preload_buffer_size 32768
profiling_history_size 15
query_alloc_block_size 8192
query_cache_size 0
query_prealloc_size 8192
range_alloc_block_size 4096
read_buffer_size 131072
read_rnd_buffer_size 262144
sort_buffer_size 2097144
thread_cache_size 0
tmp_table_size 33554432
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
With server limits I meant something like the achitectural limits of mysql: e.g. the number of max allowed where/joins conditions, max. column length, max allowed entities in a where statement and the like.
Or the way it is documented for sybase if issuing dbcc serverlimits:
ex. excerpt
General SQL related
Max size of character literals, sproc parameters : 16384
Max size of local @variables in T-SQL : 16384
Max number of arguments to stored procedures : 2048
Max number of arguments to dynamic SQL : 2048
Max number of aggregates in a COMPUTE clause : 254
Max number of arguments to Java methods : 31
Max number of user tables in a single SQL statement : 512
Max number of internal work tables in a single SQL statement : 46
Max number of subqueries in a single statement : 50
Max number of user-supplied expressions in select list : 4096
Max number of referential integrity user tables per query : 192
Max number of referential integrity work tables per query : 192
2.)
I suggested the following index to enforce a ‘closed query’ on table:
Put a composite index on WebsiteId, CustomerId, CampaignId, PlacementId, NetworkPlacementId, Sub NetworkPlacementId
But I omitted the date to be complete with that - but isn't this date range query useless in that case? Shouldn't it be a 'normal' where condition (like "where a.date=20100430").
3.)
Please remember that this query is somehow build by a reporting frontend where users can chose their data.
The query and design is not from me.
SMALLINT itself would indeed not be enough since we indeed have to maintain millions of ids here.
4.)
Reboot: No, the server gives no response any more. A database connection is not possible any more.
5.)SHOW TABLE STATUS LIKE 'NetworkData'\G -- size
Name: NetworkData
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 742325493
Avg_row_length: 59
Data_length: 43797204087
Max_data_length: 16607023625928703
Index_length: 68856199168
Data_free: 0
6.) Explain:
I tried to. But as it started to run that long I killed it again. It is really a monster query.
Even the kill is running since a few minutes, now.
7.)
it is 64bit environment./usr/libexec/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped
Best Regards,
Jens