GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

low performance - what am i doing wrong?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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?

    Comment


    • #3
      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(R) 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)

      Comment


      • #4
        One quick biggie is the last block of iostat output. The iowait spike to 40% means that your system is really lagging reading from disk to the point that virtually no processing is getting done. Two other scary indicators are the top line indicating that java is eating over 50% of your very limited pool of memory, and the swap indicators. Your machine appears to be entirely out of RAM and will wind up swapping to disk a lot. I'll take a closer look at the show status output once I get to the office today.

        One more thing - it looks like this is a hosted machine. If you're sharing services - CPU/memory/disk with other customers, all bets are off. Do you own the machine? If not, is it dedicated to you, or is it a shared system?

        Comment


        • #5
          mwallace wrote on Fri, 16 May 2008 19:22

          ..Two other scary indicators are the top line indicating that java is eating over 50% of your very limited pool of memory, and the swap indicators. Your machine appears to be entirely out of RAM and will wind up swapping to disk a lot.

          ?! I'm surprised I haven't noticed that before..another interesting thing I have just found - guess what's the HDD status:

          Status of drive /dev/sdaLocation SCSI device ADrive size 30 GBMake and model VMware Virtual diskSupports SMART? NoSMART enabled? No

          I do not own the machine- it is a dedicated server for witch my boss is paying proper money..but now I'm starting to think the whole 'dedicated server' is a hoax loaded on 512MB VMware, shared with other users.. Is there any way of veryfying that theory?

          Comment


          • #6
            I don't know of any way to introspect the host hardware from within the guest os, but the odds are good that therein lies the root of your trouble. Do you have a budget for physical hardware? You can buy an equivalent server class machine for well under $1000. The colo costs on top of that, however, I know nothing about.

            Comment


            • #7
              Thank you for your help. I really appreciate it.
              Unfortunately buying new server isn't an option.
              Could you have a look at my MySql settings, just to make sure they’re not messing everything up, before I go to the hosting company with a complaint?

              Thanks again

              Comment


              • #8
                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.

                Comment


                • #9
                  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.

                  Comment

                  Working...
                  X