Hi
I am using Mysql 4.1 on
CPU 2 Xeon 3.0 Ghz + 2 Virtual (Hyperthreads) (I think it is not using the 2nd
processor)
Memory 4GB
I think that the read from the HDD is 50mb per 1 sec, but I am not sure.
I have very big problem with performance, because there are very bad search
queries, but even worse after I tuned some of the mysql system variables
There are 2 main tables(MyIsam) that are in many to many table relation ship
3 million records up to now, the table has about 35 well typed columns.
tbl_items 0 PRIMARY 1 item_id A 2594514 \N \N
BTREE
tbl_items 1 type_id 1 type_id A 2 \N \N
BTREE
tbl_items 1 type_id 2 item_rights A 4 \N
\N YES BTREE
tbl_items 1 source 1 item_source A 1652 \N
\N BTREE
tbl_items 1 date_created 1 date_created A 10174
\N \N BTREE
tbl_items 1 set_id 1 set_id A 96093 \N \N
BTREE
tbl_items 1 set_id 2 date_created A 152618 \N
\N BTREE
tbl_items 1 simple_search 1 caption \N 1 \N
\N YES FULLTEXT
tbl_items 1 simple_search 2 keywords \N 1
\N \N YES FULLTEXT
6 million but it’s has only 3 columnst (twi int(11) and 1 int(2)
tbl_items_categories 0 PRIMARY 1 id A \N \N
\N BTREE
tbl_items_categories 0 PRIMARY 2 item_id A \N \N
\N BTREE
tbl_items_categories 0 PRIMARY 3 category_id A 5729087
\N \N BTREE
tbl_items_categories 1 FK_item_category_idx 1 category_id
A 63 \N \N BTREE
tbl_items_categories 1 item_id 1 item_id A 5729087 \N
\N BTREE
The queries are:
SELECT <SOME FIELDS>
FROM tbl_items i WHERE (MATCH(i.caption, i.keywords) AGAINST ('london' IN
BOOLEAN MODE ))
AND (((i.product_id = 4 OR i.product_id2 = 4))) ORDER BY i.date_created DESC
LIMIT 0, 24;
SELECT DISTINCT<SOME FIELDS>
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND (MATCH(i.caption, i.keywords) AGAINST
('london' IN BOOLEAN MODE ))
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT<SOME FIELDS>
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT <SOME FIELDS>
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND (MATCH(i.caption, i.keywords) AGAINST
('london' IN BOOLEAN MODE ))
AND (((i.product_id =6 OR i.product_id2 = 6) AND (icpx.category_id = 83 OR
icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT <SOME FIELDS>
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =6 OR i.product_id2 = 6) AND (icpx.category_id = 83 OR
icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
I personally don’t like them, because first it can be used unions instead OR.
Also there are many parenthesis ant it will be slow for parsing.
But the worse is that when you are not using keyword(i.e. fulltext search) and
the joining with distinct + order by is killing every thing,
I see in the show processlist copy to tmp that takes from 1-3 minutes to
execute.
I remove the search without keywords for now and tuned some part of the system
variables but copy to tmp appears again.
Also the mysql is 4.1 it cannot use merge indexes, so it uses only fulltext
search index, and the ordering becames very slow, even that is the
Bat thing that causes copy to tmp to appear, and when there is no memory it
writes to disk ? that tmp table. Also
Mysql 4.1 uses 1 index per query per table that is terrible ?.
I am thinking to remake the queries with unions and to set product_id2 to index
with date_created, the same for product_id i.e
Index on iproduct_id, item_datecareted) and the same for the other.
There are some times big slow select, and after it happens write and this
blocks all other selects the appear Locked in the show processlist;
Please advice how to optimize this situation, I read that the tmp ordering can
be optimized with moving the mysql tmp dir to another fast disck or even ram
disc(tmpfs may be?)
Here are the system variables :
back_log 50
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
concurrent_insert ON
connect_timeout 5
datadir /var/lib/mysql/
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
expire_logs_days 3
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 3
ft_query_expansion_limit 20
ft_stopword_file (built-in)
group_concat_max_len 1024
have_archive YES
have_bdb NO
have_blackhole_engine NO
have_compress YES
have_crypt YES
have_csv NO
have_example_engine NO
have_geometry YES
have_innodb YES
have_isam NO
have_merge_engine YES
have_ndbcluster NO
have_openssl NO
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
init_connect
init_file
init_slave
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_fast_shutdown ON
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_table_locks ON
innodb_thread_concurrency 8
interactive_timeout 28800
join_buffer_size 4190208
key_buffer_size 1073741824
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/share/mysql/english/
large_files_support ON
lc_time_names en_US
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin ON
log_error
log_slave_updates OFF
log_slow_queries ON
log_update OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1047552
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 200
max_delayed_threads 20
max_error_count 64
max_heap_table_size 104856576
max_insert_delayed_threads 20
max_join_size 18446744073709551615
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_tmp_tables 90
max_user_connections 0
max_write_lock_count 4294967295
myisam_data_pointer_size 4
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 1024
pid_file /var/lib/mysql/localhost.localdomain.pid
port 3306
preload_buffer_size 32768
prepared_stmt_count 0
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 8388608
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
server_id 1
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_net_timeout 3600
slave_transaction_retries 0
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer_size 4194296
sql_mode
sql_notes ON
sql_warnings ON
storage_engine MyISAM
sync_binlog 1
sync_frm ON
sync_replication 0
sync_replication_slave_id 0
sync_replication_timeout 0
system_time_zone CDT
table_cache 407
table_type MyISAM
thread_cache_size 16
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
tmp_table_size 536870912
tmpdir
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
version 4.1.22-standard-log
version_comment MySQL Community Edition - Standard (GPL)
version_compile_machine i686
version_compile_os pc-linux-gnu
wait_timeout 28800
Show status
Aborted_clients 234
Aborted_connects 0
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 225606412
Bytes_sent 3005443782
Com_admin_commands 66
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 1
Com_change_db 183
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_dealloc_sql 0
Com_delete 1280
Com_delete_multi 3
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 32819
Com_insert_select 43
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 250474
Com_set_option 607
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 158
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 308
Com_show_databases 4
Com_show_errors 0
Com_show_fields 145
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 45
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 320
Com_show_slave_hosts 1
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 108
Com_show_variables 173
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_unlock_tables 1
Com_update 78123
Com_update_multi 0
Connections 170
Created_tmp_disk_tables 5454
Created_tmp_files 1010
Created_tmp_tables 22223
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 949
Handler_discover 0
Handler_read_first 1599
Handler_read_key 355904561
Handler_read_next 1142413734
Handler_read_prev 363834
Handler_read_rnd 7371541
Handler_read_rnd_next 587538060
Handler_rollback 161
Handler_update 20384
Handler_write 50259202
Key_blocks_not_flushed 47435
Key_blocks_unused 220362
Key_blocks_used 708736
Key_read_requests 3885623178
Key_reads 1106287
Key_write_requests 50896389
Key_writes 643557
Max_used_connections 39
Not_flushed_delayed_rows 0
Open_files 196
Open_streams 0
Open_tables 232
Opened_tables 238
Qcache_free_blocks 204
Qcache_free_memory 28672400
Qcache_hits 857901
Qcache_inserts 249443
Qcache_lowmem_prunes 63919
Qcache_not_cached 1030
Qcache_queries_in_cache 878
Qcache_total_blocks 2280
Questions 1222721
Rpl_status NULL
Select_full_join 155
Select_full_range_join 0
Select_range 14658
Select_range_check 0
Select_scan 3788
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 4143
Sort_merge_passes 503
Sort_range 41071
Sort_rows 7353497
Sort_scan 5967
Table_locks_immediate 458067
Table_locks_waited 850
Threads_cached 14
Threads_connected 17
Threads_created 70
Threads_running 2
Uptime 444983
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
