mysql 4.1 server optimization

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
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
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
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
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
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=gcdmg-mysql-2@m.gmane.org

Re: mysql 4.1 server optimization

Dimitar,
Just looking over the server status, it looks=20
like you have a high rate of temp table to disk.

Created_tmp_disk_tables 5454
Created_tmp_files 1010
Created_tmp_tables 22223

I would try increasing the max_heap_table_size.=20
Increasing the tmp_table_size alone wouldn't be enough.

tmp_table_size 536870912
max_heap_table_size 104856576

Your key buffer usage looks pretty good.
(1- (220362*1024)/1073741824) * 100) =3D 79%

key_buffer_size 1073741824
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
Key_blocks_unused 220362


What variables did you tune to make it worse?
Is this on a dedicated db machine?

Also, if you have a read heavy workload, I would=20
suggest increasing the query cache size and query cache limit.
We saw significant performance improvement when we tuned our query cache.

Kyong

At 12:56 AM 5/5/2009, dimitar nen4ev wrote:
>Hi
>I am using Mysql 4.1 on
>
>CPU 2 Xeon 3.0 Ghz + 2 Virtual (Hyperthreads) (I=20
>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,=20
>because there are very bad search queries, but=20
>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=20
>A 2594514 \N \N BTREE
>tbl_items 1 type_id 1 type_id=20
>A 2 \N \N BTREE
>tbl_items 1 type_id=20
>2 item_rights A 4 \N \N YES BTREE
>tbl_items 1 source 1=20
>item_source A 1652 \N \N BTREE
>tbl_items 1 date_created 1=20
>date_created A 10174 \N \N BTREE
>tbl_items 1 set_id 1 set_id=20
>A 96093 \N \N BTREE
>tbl_items 1 set_id 2=20
>date_created A 152618 \N \N BTREE
>tbl_items 1 simple_search 1=20
>caption \N 1 \N \N YES FULLTEXT
>tbl_items 1 simple_search 2=20
>keywords \N 1 \N \N YES FULLTEXT
>
>6 million but itâ€=99s has only 3 columnst (twi int(11) and 1 int(2)
>
>tbl_items_categories 0 PRIMARY=20
>1 id A \N \N \N BTREE
>tbl_items_categories 0 PRIMARY=20
>2 item_id A \N \N \N BTREE
>tbl_items_categories 0 PRIMARY=20
>3 category_id A 5729087 \N \N BTREE
>tbl_items_categories 1=20
>FK_item_category_idx 1 category_id=20
>A 63 \N \N BTREE
>tbl_items_categories 1 item_id=20
>1 item_id A 5729087 \N \N BTREE
>
>The queries are:
>
>SELECT
>FROM tbl_items i WHERE (MATCH(i.caption,=20
>i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
>AND (((i.product_id =3D 4 OR i.product_id2 =3D 4)))=20
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>SELECT DISTINCT
>FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id AND=20
>(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
>AND (((i.product_id =3D4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
11)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>SELECT DISTINCT
>FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id
>AND (((i.product_id =3D4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
11)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>
>SELECT DISTINCT
> FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id AND=20
>(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
>AND (((i.product_id =3D6 OR i.product_id2 =3D 6) AND=20
>(icpx.category_id =3D 83 OR icpx.category_id =3D 87))
>OR ((i.product_id =3D 5 OR i.product_id2 =3D 5) AND (icpx.category_id =3D=
24))
>OR ((i.product_id =3D 4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
12)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>SELECT DISTINCT
> FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id
>AND (((i.product_id =3D6 OR i.product_id2 =3D 6) AND=20
>(icpx.category_id =3D 83 OR icpx.category_id =3D 87))
>OR ((i.product_id =3D 5 OR i.product_id2 =3D 5) AND (icpx.category_id =3D=
24))
>OR ((i.product_id =3D 4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
12)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>
>I personally donâ€=99t like them, because first it=20
>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=20
>keyword(i.e. fulltext search) and the joining=20
>with distinct + order by is killing every thing,
>I see in the show processlist copy to tmp that=20
>takes from 1-3 minutes to execute.
>
>I remove the search without keywords for now and=20
>tuned some part of the system variables but copy to tmp appears again.
>Also the mysql is 4.1 it cannot use merge=20
>indexes, so it uses only fulltext search index,=20
>and the ordering becames very slow, even that is the
>Bat thing that causes copy to tmp to appear, and=20
>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=20
>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=20
>it happens write and this blocks all other=20
>selects the appear Locked in the show processlist;
>
>Please advice how to optimize this situation, I=20
>read that the tmp ordering can be optimized with=20
>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=3Dkimkyong@fhda.edu


Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql 4.1 server optimization

If you have the slow query log enabled, grab some=20
slow queries from there and run EXPLAIN.
From there, you can figure out how the optimizer is executing the query.
I found that approach to be very useful in=20
optimizing individual queries and making indexing decisions.

I can't quite make out what the table structure=20
is showing but it's generally not a good idea to over-index.
You can have a significant overhead on inserts.=20
We've seen something similar in production with=20
InnoDB and my understanding is that you can incur=20
even greater overhead on MyISAM.

Kyong

At 12:56 AM 5/5/2009, dimitar nen4ev wrote:
>Hi
>I am using Mysql 4.1 on
>
>CPU 2 Xeon 3.0 Ghz + 2 Virtual (Hyperthreads) (I=20
>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,=20
>because there are very bad search queries, but=20
>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=20
>A 2594514 \N \N BTREE
>tbl_items 1 type_id 1 type_id=20
>A 2 \N \N BTREE
>tbl_items 1 type_id=20
>2 item_rights A 4 \N \N YES BTREE
>tbl_items 1 source 1=20
>item_source A 1652 \N \N BTREE
>tbl_items 1 date_created 1=20
>date_created A 10174 \N \N BTREE
>tbl_items 1 set_id 1 set_id=20
>A 96093 \N \N BTREE
>tbl_items 1 set_id 2=20
>date_created A 152618 \N \N BTREE
>tbl_items 1 simple_search 1=20
>caption \N 1 \N \N YES FULLTEXT
>tbl_items 1 simple_search 2=20
>keywords \N 1 \N \N YES FULLTEXT
>
>6 million but itâ€=99s has only 3 columnst (twi int(11) and 1 int(2)
>
>tbl_items_categories 0 PRIMARY=20
>1 id A \N \N \N BTREE
>tbl_items_categories 0 PRIMARY=20
>2 item_id A \N \N \N BTREE
>tbl_items_categories 0 PRIMARY=20
>3 category_id A 5729087 \N \N BTREE
>tbl_items_categories 1=20
>FK_item_category_idx 1 category_id=20
>A 63 \N \N BTREE
>tbl_items_categories 1 item_id=20
>1 item_id A 5729087 \N \N BTREE
>
>The queries are:
>
>SELECT
>FROM tbl_items i WHERE (MATCH(i.caption,=20
>i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
>AND (((i.product_id =3D 4 OR i.product_id2 =3D 4)))=20
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>SELECT DISTINCT
>FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id AND=20
>(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
>AND (((i.product_id =3D4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
11)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>SELECT DISTINCT
>FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id
>AND (((i.product_id =3D4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
11)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>
>SELECT DISTINCT
> FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id AND=20
>(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
>AND (((i.product_id =3D6 OR i.product_id2 =3D 6) AND=20
>(icpx.category_id =3D 83 OR icpx.category_id =3D 87))
>OR ((i.product_id =3D 5 OR i.product_id2 =3D 5) AND (icpx.category_id =3D=
24))
>OR ((i.product_id =3D 4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
12)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>SELECT DISTINCT
> FROM tbl_items i, tbl_items_categories icpx
>WHERE icpx.item_id =3D i.item_id
>AND (((i.product_id =3D6 OR i.product_id2 =3D 6) AND=20
>(icpx.category_id =3D 83 OR icpx.category_id =3D 87))
>OR ((i.product_id =3D 5 OR i.product_id2 =3D 5) AND (icpx.category_id =3D=
24))
>OR ((i.product_id =3D 4 OR i.product_id2 =3D 4) AND (icpx.category_id =3D=
12)))
>ORDER BY i.date_created DESC LIMIT 0, 24;
>
>
>I personally donâ€=99t like them, because first it=20
>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=20
>keyword(i.e. fulltext search) and the joining=20
>with distinct + order by is killing every thing,
>I see in the show processlist copy to tmp that=20
>takes from 1-3 minutes to execute.
>
>I remove the search without keywords for now and=20
>tuned some part of the system variables but copy to tmp appears again.
>Also the mysql is 4.1 it cannot use merge=20
>indexes, so it uses only fulltext search index,=20
>and the ordering becames very slow, even that is the
>Bat thing that causes copy to tmp to appear, and=20
>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=20
>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=20
>it happens write and this blocks all other=20
>selects the appear Locked in the show processlist;
>
>Please advice how to optimize this situation, I=20
>read that the tmp ordering can be optimized with=20
>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=3Dkimkyong@fhda.edu


Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg