List:General Discussion« Previous MessageNext Message »
From:dimitar nen4ev Date:May 5 2009 7:56am
Subject:mysql 4.1 server optimization
View as plain text  
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
Thread
mysql 4.1 server optimizationdimitar nen4ev5 May
  • Re: mysql 4.1 server optimizationKyong Kim5 May
  • Re: mysql 4.1 server optimizationKyong Kim5 May