low performance - what am i doing wrong?

Could anyone tell me what is wrong - after ive put the database on the server query takes ages to execute:

SELECT company.company_id,company.enhancement FROM company LEFT JOIN company_in_cat_100 ON company.company_id = company_in_cat_100.company_id where company_in_cat_100.category_id =53;

1st run: 39627 rows in set (1 min 50.44 sec)
later : ~10 sec

I dont really understand why - I have tested everything on my local machine, and it was maximum 0,5-1,2sec.

Structure dump:

CREATE TABLE company_in_cat_100 ( id mediumint(7) NOT NULL auto_increment, company_id mediumint(7) unsigned NOT NULL default ‘0’, category_id smallint(4) unsigned NOT NULL default ‘0’, PRIMARY KEY (id), KEY category_id (category_id)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=158105 ;

CREATE TABLE company ( company_id mediumint(7) unsigned NOT NULL auto_increment, name varchar(100) NOT NULL default ‘’, add1 varchar(64) NOT NULL default ‘’, add2 varchar(64) NOT NULL default ‘’, add3 varchar(64) NOT NULL default ‘’, town_id smallint(4) unsigned NOT NULL default ‘0’, county_id mediumint(6) unsigned NOT NULL default ‘0’, postcode varchar(9) NOT NULL default ‘’, telephone varchar(30) NOT NULL default ‘’, description_text varchar(255) NOT NULL default ‘’, enhancement tinyint(1) NOT NULL default ‘0’, PRIMARY KEY (company_id), KEY name (name), KEY town (town_id)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1707942 ;

Explein Query:

±—±------------±-------------------±-------±--------------±------------±--------±----------------------------------------------±------±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±-------------------±-------±--------------±------------±--------±----------------------------------------------±------±------------+| 1 | SIMPLE | company_in_cat_100 | ref | category_id | category_id | 2 | const | 34695 | Using where || 1 | SIMPLE | company | eq_ref | PRIMARY | PRIMARY | 3 | thisisbusnew_en.company_in_cat_100.company_id | 1 | |±—±------------±-------------------±-------±--------------±------------±--------±----------------------------------------------±------±------------+

Show variables:

±--------------------------------±---------------------------------------------+| Variable_name | Value |±--------------------------------±---------------------------------------------+| back_log | 50 || basedir | / || binlog_cache_size | 32768 || bulk_insert_buffer_size | 8388608 || character_set_client | latin1 || character_set_connection | latin1 || character_set_database | latin1 || character_set_results | latin1 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ || collation_connection | latin1_swedish_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 | 0 || flush | OFF || flush_time | 0 || 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 | NO || 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 | 131072 || key_buffer_size | 8388600 || 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 | OFF || log_error | || log_slave_updates | OFF || log_slow_queries | OFF || 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 | 1048576 || max_binlog_cache_size | 4294967295 || max_binlog_size | 1073741824 || max_connect_errors | 10 || max_connections | 100 || 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_tmp_tables | 32 || 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 | 8388608 || 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 | ON || open_files_limit | 1024 || pid_file | /var/lib/mysql/STEAM3.POBOXHOSTING.CO.UK.pid || port | 3306 || preload_buffer_size | 32768 || prepared_stmt_count | 0 || protocol_version | 10 || query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 0 || query_cache_type | ON || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || range_alloc_block_size | 2048 || read_buffer_size | 131072 || read_only | OFF || read_rnd_buffer_size | 262144 || relay_log_purge | ON || relay_log_space_limit | 0 || rpl_recovery_rank | 0 || secure_auth | OFF || server_id | 0 || 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 | 2097144 || sql_mode | || sql_notes | ON || sql_warnings | ON || storage_engine | MyISAM || sync_binlog | 0 || sync_frm | ON || sync_replication | 0 || sync_replication_slave_id | 0 || sync_replication_timeout | 0 || system_time_zone | BST || table_cache | 64 || table_type | MyISAM || thread_cache_size | 0 || thread_stack | 196608 || time_format | %H:%i:%s || time_zone | SYSTEM || tmp_table_size | 33554432 || tmpdir | || transaction_alloc_block_size | 8192 || transaction_prealloc_size | 4096 || tx_isolation | REPEATABLE-READ || version | 4.1.22-standard || version_comment | MySQL Community Edition - Standard (GPL) || version_compile_machine | i686 || version_compile_os | pc-linux-gnu || wait_timeout | 28800 |±--------------------------------±---------------------------------------------+

System:linux with 512 RAM memory
One more thing - while running query mysql uses only 3% of CPU.
Ive tried everything. Please let me know what you think.

could you please post some additional information from both machines?
The output of:
free

  • memory consumtpion
    the header and first couple of iterations of ‘iostat 5’
  • i/o and system load
    the header info from ‘top’
  • additional system info
    diff output from ‘show variables’ and ‘show status’ on both machines.
    Basic configuration information about both machines - CPUs, RAM, other major hardware differences.
    Are both databases using the same storage engine? Are indexes the same across both pairs of tables?

Sure
Thanks for interest - I haven’t got too much database administration experience. It sad that companys PHP guy has to do admin too…

free:

total used free shared buffers cachedMem: 515760 503052 12708 0 6612 160224-/+ buffers/cache: 336216 179544Swap: 1048568 44512 1004056

iostat:

Linux 2.6.15-1.2054_FC5 (STEAM3.POBOXHOSTING.CO.UK) 05/16/2008avg-cpu: %user %nice %system %iowait %idle 0.18 0.00 0.28 0.75 98.79Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtnsda 1.57 9.66 28.28 206298096 603960526dm-0 3.95 9.11 27.81 194502234 594018392dm-1 0.13 0.55 0.47 11789056 9936048avg-cpu: %user %nice %system %iowait %idle 0.40 0.00 0.40 0.00 99.20Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtnsda 0.00 0.00 0.00 0 0dm-0 0.20 0.00 1.60 0 8dm-1 0.00 0.00 0.00 0 0avg-cpu: %user %nice %system %iowait %idle 0.60 0.00 0.60 1.40 97.41Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtnsda 0.80 0.00 35.13 0 176dm-0 4.19 0.00 33.53 0 168dm-1 0.00 0.00 0.00 0 0avg-cpu: %user %nice %system %iowait %idle 0.40 0.00 2.20 40.52 56.89Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtnsda 162.87 3388.42 30.34 16976 152dm-0 167.66 3390.02 30.34 16984 152dm-1 0.00 0.00 0.00 0 0

top:

top - 17:07:52 up 247 days, 5:13, 1 user, load average: 0.71, 0.22, 0.07Tasks: 91 total, 2 running, 88 sleeping, 1 stopped, 0 zombieCpu(s): 1.7% us, 5.0% sy, 0.0% ni, 0.0% id, 93.4% wa, 0.0% hi, 0.0% siMem: 515760k total, 509368k used, 6392k free, 2508k buffersSwap: 1048568k total, 44512k used, 1004056k free, 170148k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1951 mysql 16 0 109m 14m 2652 S 3.3 2.8 175:39.95 mysqld32315 root 21 0 1042m 280m 16m S 2.7 55.6 17:46.98 java 1 root 16 0 1992 316 292 S 0.0 0.1 1:00.23 init 2 root 34 19 0 0 0 S 0.0 0.0 0:00.10 ksoftirqd/0 3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0

show status:

Aborted_clients 220Aborted_connects 22769Binlog_cache_disk_use 0Binlog_cache_use 0Bytes_received 1509950981Bytes_sent 4137678502Com_admin_commands 0Com_alter_db 0Com_alter_table 21Com_analyze 1Com_backup_table 0Com_begin 0Com_change_db 980757Com_change_master 0Com_check 0Com_checksum 0Com_commit 0Com_create_db 2Com_create_function 0Com_create_index 1Com_create_table 530Com_dealloc_sql 0Com_delete 2047Com_delete_multi 0Com_do 0Com_drop_db 1Com_drop_function 0Com_drop_index 0Com_drop_table 984Com_drop_user 0Com_execute_sql 0Com_flush 11804Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 8186319Com_insert_select 541Com_kill 0Com_load 0Com_load_master_data 0Com_load_master_table 0Com_lock_tables 14313Com_optimize 1Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_rename_table 0Com_repair 1Com_replace 0Com_replace_select 0Com_reset 0Com_restore_table 0Com_revoke 0Com_revoke_all 0Com_rollback 0Com_savepoint 0Com_select 1959916Com_set_option 2941675Com_show_binlog_events 0Com_show_binlogs 0Com_show_charsets 0Com_show_collations 980512Com_show_column_types 0Com_show_create_db 0Com_show_create_table 136Com_show_databases 156Com_show_errors 0Com_show_fields 2762Com_show_grants 0Com_show_innodb_status 3Com_show_keys 514Com_show_logs 0Com_show_master_status 0Com_show_ndb_status 0Com_show_new_master 0Com_show_open_tables 0Com_show_privileges 0Com_show_processlist 14Com_show_slave_hosts 0Com_show_slave_status 0Com_show_status 25Com_show_storage_engines 0Com_show_tables 299Com_show_variables 980520Com_show_warnings 0Com_slave_start 0Com_slave_stop 0Com_stmt_close 0Com_stmt_execute 0Com_stmt_prepare 0Com_stmt_reset 0Com_stmt_send_long_data 0Com_truncate 0Com_unlock_tables 14314Com_update 13845Com_update_multi 0Connections 1004177Created_tmp_disk_tables 9280Created_tmp_files 29Created_tmp_tables 210144Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flush_commands 11805Handler_commit 0Handler_delete 1334Handler_discover 0Handler_read_first 32646Handler_read_key 56036266Handler_read_next 24846980Handler_read_prev 0Handler_read_rnd 3217171Handler_read_rnd_next 2056257919Handler_rollback 2Handler_update 8637Handler_write 19741888Key_blocks_not_flushed 0Key_blocks_unused 0Key_blocks_used 7248Key_read_requests 247503495Key_reads 6715842Key_write_requests 34762520Key_writes 30179666Max_used_connections 12Not_flushed_delayed_rows 0Open_files 100Open_streams 0Open_tables 52Opened_tables 140490Qcache_free_blocks 0Qcache_free_memory 0Qcache_hits 0Qcache_inserts 0Qcache_lowmem_prunes 0Qcache_not_cached 0Qcache_queries_in_cache 0Qcache_total_blocks 0Questions 17075502Rpl_status NULLSelect_full_join 106827Select_full_range_join 0Select_range 12657Select_range_check 0Select_scan 869758Slave_open_temp_tables 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 1Slow_queries 81Sort_merge_passes 11Sort_range 544Sort_rows 19170611Sort_scan 241650Table_locks_immediate 10140589Table_locks_waited 26Threads_cached 0Threads_connected 2Threads_created 1004176Threads_running 1Uptime 21359841

Will give you outputs from local machine on Monday when Ill be back in the office.

Differences:
local / server
XP (yeah…I know) / Fedora Core 5
Intel 1,8 Ghz / Intel® Xeon @ 2.33GHz
1024 RAM / 512 RAM
80 SATA / 30 GB SCSI
Apache 1.3 / Apache 2
MySql 4.1.9 / MySql 4.1.22

Both databases use same storage engine, and indexes are the same.
There are few other databases on that machine, but theyre not generating too much traffic. Maybe I should mention that these are used by Tomcat.(tomcat running on port 80, Apache on 8080 for now)

The Keybuffer-Size (key_buffer_size) is set to a very small value: 8MB.

Try to raise it (start with 32MB = 33554432 Bytes) and see what happens.

Proglem solved - server restart and upgrading memory from 500MB to 2GB did it. (Probably the problem was that tomcat was eating all available mem.)

After that + chaning buffers + enabling query cache, average query execution time = 0.5 sec.

PS - it came out we are on a shared server after all…noughty hosting company. Theyre supposed to transfer us to a dedicated one soon.

Thanks for your help guys. Really apreciate it.