i have much traffic at my site.
but its still slow.
What will be the best way to load tables in memory to speed up access.
i can hold tables in memory without problems but how can i do it using myisam tables?
I would like to optimize search time, because my server load is very high.
here i have some info about index use and configuration:
my.cnf is:
mysql> show status like “key%”;
this is my.cnf:
[mysqld]
xaprb
May 10, 2010, 9:53pm
2
I see
key_buffer_size=1500M
and you say key_buffer_size = 3G. What size is it really? Check SHOW VARIABLES.
sorry it was before i already changed both to 3G with same load problem.
key_buffer_size=3G
xaprb
May 11, 2010, 8:45am
4
You should get a whole-server picture of what is going on and try to understand where the time is being consumed by these queries. I have a few suggestions.
Get system-wide stats from iostat, vmstat
Get incremental samples of SHOW GLOBAL STATUS
Use the Percona builds and set long_query_time = 0 and analyze your slow query log with mk-query-digest.
At: Tue May 11 16:50:54 CEST 2010
vmstat
mysql> show global status;
two hours later when load was more high:
iostathost.cables2.com ) 05/11/2010
avg-cpu: %user %nice %sys %iowait %idle
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
vmstat
±----------------------------------±------------+
xaprb
May 11, 2010, 12:27pm
6
vmstat and iostat are not very useful the way you ran them. You need to run them for several iterations. The first line you see is just averages since boot. Try
vmstat 5 5
Also please use something like mext (from Aspersa project) to digest the show status so I don’t have to do math on it. mysqladmin -ri5 -c5 is better.
account >> vmstat 5 5procs -----------memory---------- —swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa11 0 2212 1210384 470724 4443416 0 0 159 246 32 12 64 13 22 115 0 2212 1375816 470744 4306240 0 0 42 274 1404 1798 84 16 0 011 0 2212 1256208 470800 4358136 0 0 538 182 2063 2394 82 18 0 0 7 0 2212 1473584 470876 4277548 0 0 522 198 2423 801 71 11 18 012 0 2212 1386752 470900 4283304 0 0 26 266 1566 2529 84 13 2 0account >> iostat -dx 5 5Linux 2.6.9-89.0.23.ELsmp (host.cables2.com ) 05/11/2010Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 2.24 290.06 26.53 40.91 1261.46 2649.01 630.73 1324.51 57.98 2.45 36.27 1.50 10.12sda1 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 39.95 0.00 3.43 3.43 0.00sda2 0.04 11.33 0.99 1.98 61.30 106.46 30.65 53.23 56.56 0.06 21.33 3.50 1.04sda3 0.00 0.11 0.00 0.01 0.02 0.95 0.01 0.48 88.17 0.00 15.58 8.16 0.01sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2.00 0.00 15.00 15.00 0.00sda5 0.02 3.59 0.61 3.85 9.30 59.59 4.65 29.80 15.45 0.05 12.07 3.62 1.61sda6 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 54.60 0.00 6.85 6.85 0.00sda7 0.03 238.18 0.54 26.39 21.18 2116.63 10.59 1058.32 79.39 1.59 59.11 0.66 1.77sda8 2.14 36.85 24.40 8.67 1169.64 365.37 584.82 182.69 46.41 0.74 22.28 2.50 8.28sdb 0.27 159.56 5.57 1.52 1281.42 1288.62 640.71 644.31 362.68 1.44 203.18 4.01 2.84sdb1 0.27 159.56 5.57 1.52 1281.40 1288.62 640.70 644.31 362.68 1.44 203.18 4.01 2.84Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.00 54.18 7.37 54.58 82.87 870.12 41.43 435.06 15.38 4.63 74.82 1.79 11.12sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 10.36 0.00 1.00 0.00 90.84 0.00 45.42 91.20 0.00 4.60 2.80 0.28sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 2.59 0.60 2.79 11.16 43.03 5.58 21.51 16.00 0.05 13.94 13.18 4.46sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 3.78 0.80 0.40 6.37 33.47 3.19 16.73 33.33 0.00 1.17 1.17 0.14sda8 0.00 37.45 5.98 50.40 65.34 702.79 32.67 351.39 13.63 4.58 81.28 1.76 9.92sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.20 63.53 5.41 16.03 80.16 638.08 40.08 319.04 33.50 0.36 16.90 2.41 5.17sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 12.22 0.00 8.22 0.00 163.53 0.00 81.76 19.90 0.10 12.71 1.32 1.08sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 0.80 0.40 1.00 4.81 14.43 2.40 7.21 13.71 0.01 4.14 2.43 0.34sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 5.01 2.20 3.61 17.64 68.94 8.82 34.47 14.90 0.21 36.41 3.76 2.18sda8 0.20 45.49 2.81 3.21 57.72 391.18 28.86 195.59 74.67 0.04 6.73 4.23 2.55sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.00 51.79 1.00 9.36 19.12 489.24 9.56 244.62 49.08 0.06 5.40 2.25 2.33sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 16.53 0.00 1.39 0.00 143.43 0.00 71.71 102.86 0.00 3.14 2.14 0.30sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 9.56 0.60 6.18 11.16 125.90 5.58 62.95 20.24 0.04 6.56 2.18 1.47sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 6.77 0.00 0.60 0.00 58.96 0.00 29.48 98.67 0.00 0.67 0.33 0.02sda8 0.00 18.92 0.40 1.20 7.97 160.96 3.98 80.48 106.00 0.01 4.25 3.38 0.54sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.00 45.20 2.20 5.80 134.40 408.00 67.20 204.00 67.80 0.05 5.70 3.05 2.44sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 10.00 0.00 1.80 0.00 94.40 0.00 47.20 52.44 0.01 5.00 2.22 0.40sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 0.80 0.00 1.00 0.00 14.40 0.00 7.20 14.40 0.00 3.60 2.20 0.22sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 5.80 0.00 0.40 0.00 49.60 0.00 24.80 124.00 0.00 0.00 0.00 0.00sda8 0.00 28.60 2.20 2.60 134.40 249.60 67.20 124.80 80.00 0.03 6.88 3.79 1.82sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Aborted_clients 37 0 0 0 0Aborted_connects 103 0 0 0 0Binlog_cache_disk_use 0 0 0 0 0Binlog_cache_use 0 0 0 0 0Bytes_received 210921284 41622 41219 31101 59423Bytes_sent 53189657487 13789200 1796858 1137202 802390Com_admin_commands 314 0 0 0 0Com_alter_db 0 0 0 0 0Com_alter_table 5 0 0 0 0Com_analyze 0 0 0 0 0Com_backup_table 0 0 0 0 0Com_begin 0 0 0 0 0Com_call_procedure 0 0 0 0 0Com_change_db 201073 44 45 50 28Com_change_master 0 0 0 0 0Com_check 13 0 0 0 0Com_checksum 0 0 0 0 0Com_commit 0 0 0 0 0Com_create_db 0 0 0 0 0Com_create_function 0 0 0 0 0Com_create_index 0 0 0 0 0Com_create_table 16 0 0 0 0Com_create_user 0 0 0 0 0Com_dealloc_sql 0 0 0 0 0Com_delete 1417 0 0 0 0Com_delete_multi 0 0 0 0 0Com_do 0 0 0 0 0Com_drop_db 0 0 0 0 0Com_drop_function 0 0 0 0 0Com_drop_index 0 0 0 0 0Com_drop_table 15 0 0 0 0Com_drop_user 0 0 0 0 0Com_execute_sql 0 0 0 0 0Com_flush 0 0 0 0 0Com_grant 0 0 0 0 0Com_ha_close 0 0 0 0 0Com_ha_open 0 0 0 0 0Com_ha_read 0 0 0 0 0Com_help 0 0 0 0 0Com_insert 16095 2 1 10 2Com_insert_select 27 0 0 0 0Com_kill 0 0 0 0 0Com_load 0 0 0 0 0Com_load_master_data 0 0 0 0 0Com_load_master_table 0 0 0 0 0Com_lock_tables 317 0 0 0 0Com_optimize 0 0 0 0 0Com_preload_keys 0 0 0 0 0Com_prepare_sql 0 0 0 0 0Compression 0 0 0 0 0Com_purge 0 0 0 0 0Com_purge_before_date 0 0 0 0 0Com_rename_table 0 0 0 0 0Com_repair 0 0 0 0 0Com_replace 2178 0 0 0 3Com_replace_select 0 0 0 0 0Com_reset 0 0 0 0 0Com_restore_table 0 0 0 0 0Com_revoke 0 0 0 0 0Com_revoke_all 0 0 0 0 0Com_rollback 0 0 0 0 0Com_savepoint 0 0 0 0 0Com_select 300160 60 49 52 55Com_set_option 95232 14 14 22 14Com_show_binlog_events 0 0 0 0 0Com_show_binlogs 0 0 0 0 0Com_show_charsets 0 0 0 0 0Com_show_collations 0 0 0 0 0Com_show_column_types 0 0 0 0 0Com_show_create_db 0 0 0 0 0Com_show_create_table 3 0 0 0 0Com_show_databases 49 0 0 0 0Com_show_errors 0 0 0 0 0Com_show_fields 132 0 0 0 0Com_show_grants 0 0 0 0 0Com_show_innodb_status 0 0 0 0 0Com_show_keys 0 0 0 0 0Com_show_logs 0 0 0 0 0Com_show_master_status 0 0 0 0 0Com_show_ndb_status 0 0 0 0 0Com_show_new_master 0 0 0 0 0Com_show_open_tables 0 0 0 0 0Com_show_privileges 0 0 0 0 0Com_show_processlist 98 0 0 0 0Com_show_slave_hosts 0 0 0 0 0Com_show_slave_status 0 0 0 0 0Com_show_status 52 1 1 1 1Com_show_storage_engines 0 0 0 0 0Com_show_tables 872 0 0 0 0Com_show_triggers 2 0 0 0 0Com_show_variables 74 0 0 0 0Com_show_warnings 0 0 0 0 0Com_slave_start 0 0 0 0 0Com_slave_stop 0 0 0 0 0Com_stmt_close 0 0 0 0 0Com_stmt_execute 0 0 0 0 0Com_stmt_fetch 0 0 0 0 0Com_stmt_prepare 0 0 0 0 0Com_stmt_reset 0 0 0 0 0Com_stmt_send_long_data 0 0 0 0 0Com_truncate 14 0 0 0 0Com_unlock_tables 316 0 0 0 0Com_update 10882 3 2 0 1Com_update_multi 210 0 0 0 0Com_xa_commit 0 0 0 0 0Com_xa_end 0 0 0 0 0Com_xa_prepare 0 0 0 0 0Com_xa_recover 0 0 0 0 0Com_xa_rollback 0 0 0 0 0Com_xa_start 0 0 0 0 0Connections 94473 14 14 22 14Created_tmp_disk_tables 8843 1 2 2 1Created_tmp_files 11121 6 2 0 4Created_tmp_tables 106349 27 28 21 21Delayed_errors 0 0 0 0 0Delayed_insert_threads 0 0 0 0 0Delayed_writes 0 0 0 0 0Flush_commands 1 0 0 0 0Handler_commit 0 0 0 0 0Handler_delete 9933258 0 0 0 0Handler_discover 0 0 0 0 0Handler_prepare 0 0 0 0 0Handler_read_first 17017 4 5 0 0Handler_read_key 1996075471 749774 371223 306984 482515Handler_read_next 4711314584 6367215 11771546 11189425 9284705Handler_read_prev 17741214 0 0 0 49Handler_read_rnd 975309222 251810 137849 193116 181085Handler_read_rnd_next 5576908550 1184208 362663 702245 1694545Handler_rollback 0 0 0 0 0Handler_savepoint 0 0 0 0 0Handler_savepoint_rollback 0 0 0 0 0Handler_update 4958546 4 2 0 52Handler_write 133956021 318 17390 26486 17745Innodb_buffer_pool_pages_data 508 0 0 0 0Innodb_buffer_pool_pages_dirty 0 0 0 0 0Innodb_buffer_pool_pages_flushed 1 0 0 0 0Innodb_buffer_pool_pages_free 0 0 0 0 0Innodb_buffer_pool_pages_misc 4 0 0 0 0Innodb_buffer_pool_pages_total 512 0 0 0 0Innodb_buffer_pool_read_ahead_rnd 40 0 0 0 0Innodb_buffer_pool_read_ahead_seq 0 0 0 0 0Innodb_buffer_pool_read_requests 39105 0 0 0 0Innodb_buffer_pool_reads 638 0 0 0 0Innodb_buffer_pool_wait_free 0 0 0 0 0Innodb_buffer_pool_write_requests 1 0 0 0 0Innodb_data_fsyncs 7 0 0 0 0Innodb_data_pending_fsyncs 0 0 0 0 0Innodb_data_pending_reads 0 0 0 0 0Innodb_data_pending_writes 0 0 0 0 0Innodb_data_read 18075648 0 0 0 0Innodb_data_reads 749 0 0 0 0Innodb_data_writes 7 0 0 0 0Innodb_data_written 35328 0 0 0 0Innodb_dblwr_pages_written 1 0 0 0 0Innodb_dblwr_writes 1 0 0 0 0Innodb_log_waits 0 0 0 0 0Innodb_log_write_requests 0 0 0 0 0Innodb_log_writes 2 0 0 0 0Innodb_os_log_fsyncs 5 0 0 0 0Innodb_os_log_pending_fsyncs 0 0 0 0 0Innodb_os_log_pending_writes 0 0 0 0 0Innodb_os_log_written 1024 0 0 0 0Innodb_pages_created 0 0 0 0 0Innodb_page_size 16384 0 0 0 0Innodb_pages_read 970 0 0 0 0Innodb_pages_written 1 0 0 0 0Innodb_row_lock_current_waits 0 0 0 0 0Innodb_row_lock_time 0 0 0 0 0Innodb_row_lock_time_avg 0 0 0 0 0Innodb_row_lock_time_max 0 0 0 0 0Innodb_row_lock_waits 0 0 0 0 0Innodb_rows_deleted 0 0 0 0 0Innodb_rows_inserted 0 0 0 0 0Innodb_rows_read 84 0 0 0 0Innodb_rows_updated 0 0 0 0 0Key_blocks_not_flushed 1114 0 3 0 0Key_blocks_unused 1553092 0 -3 -3 0Key_blocks_used 1158393 0 0 0 0Key_read_requests 8584306387 3528381 2413625 2218747 2754284Key_reads 1562990 0 0 3 0Key_write_requests 25326953 2 138 10 94Key_writes 10194 0 0 0 0Last_query_cost 0 0 0 0 0Max_used_connections 52 0 0 0 0Not_flushed_delayed_rows 0 0 0 0 0Opened_tables 4802 0 0 0 0Open_files 1917 0 2 0 1Open_streams 0 0 0 0 0Open_tables 1024 0 0 0 0Prepared_stmt_count 0 0 0 0 0Qcache_free_blocks 621 -103 -4 -3 0Qcache_free_memory 391715968 -13156128 -575592 -491032 -220672Qcache_hits 569854 115 136 84 68Qcache_inserts 288813 58 48 52 50Qcache_lowmem_prunes 203038 0 0 0 0Qcache_not_cached 10639 3 2 1 4Qcache_queries_in_cache 1912 48 41 52 45Qcache_total_blocks 8901 104 79 104 92Queries 1293718 256 268 240 183Questions 1293718 256 268 240 183Rpl_status 0 0 0 0 0Select_full_join 25379 10 9 6 4Select_full_range_join 30 0 0 0 0Select_range 42629 10 9 7 8Select_range_check 0 0 0 0 0Select_scan 55529 11 4 8 20Slave_open_temp_tables 0 0 0 0 0Slave_retried_transactions 0 0 0 0 0Slave_running 0 0 0 0 0Slow_launch_threads 0 0 0 0 0Slow_queries 336 0 1 0 0Sort_merge_passes 5541 3 1 0 2Sort_range 20327 3 1 2 4Sort_rows 2305797923 894557 344117 275728 782838Sort_scan 125090 31 27 23 22Table_locks_immediate 578353 132 106 103 106Table_locks_waited 172 0 0 0 0Tc_log_max_pages_used 0 0 0 0 0Tc_log_page_size 0 0 0 0 0Tc_log_page_waits 0 0 0 0 0Threads_cached 13 1 2 -1 -3Threads_connected 14 -3 -6 1 3Threads_created 730 0 0 0 0Threads_running 4 -1 -1 1 3Uptime 25417 5 5 5 5
xaprb
May 12, 2010, 6:45am
8
Great! Now this is much easier to understand. By the way, you are getting free consulting here )
In vmstat I see a lot of runnable and blocked processes. In Linux, processses waiting for IO are counted towards the loadavg. Look at the wa column (IO wait) – that is high. At the same time there is hardly any IO. This looks like a system with slow disks. How many processors / cores do you have?
In iostat I see very bad IO performance. The await for sda2, sda7, and sda8 is nearly a tenth of a second sometimes. The svctm is also very long. On server-class hard drives, I want to see that in the 3-5ms range. At the same time the queue is not long. Unfortunately iostat lumps reads and writes together so I can’t see whether this is due to reads or writes, but I can guess based on the volume of reads and writes. It looks like writes are very slow. So it looks like maybe you don’t have a system with a decent RAID controller and battery-backup unit on the write cache, and you probably have some 7200 RPM SATA disks or something like that.
The server doesn’t seem to be doing all that much work, based on the SHOW STATUS output. There are very few Com_ operations per second, and it looks like the Handler_ values are not that high either. However, the operations that are happening are causing a lot of Handler_ operations. You might need to optimize queries. It looks like the queries are creating temp tables, some of them on disk, which will be very bad on your disks.
Select_full_join should be ZERO. Those queries are probably your problem.
It is possible that something else is happening on this server that’s consuming all your resources. Check that.
In conclusion, I think you have a low-powered server with slow disks, and you are running very badly optimized queries on it. You need to log all queries with the slow query log (use a Percona version of the server so you can get more information about them) and use mk-query-digest to understand which are causing the most load on the server. And consider more powerful hardware, if you can.