On Jan 30, 2004, at 10:25 AM, Bruce Dembecki wrote:
<SNIP SNIP>On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff:
So.. My tips for you:
1) Consider a switch to InnoDB, the performance hit was dramatic, and it's
about SO much more than transactions (which we still don't do)!
Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb?
No changes needed to code/queries, except probably the daily table optimize/repair can go away... As far as how to do it...
Our switch to innodb was fairly smooth, but one table is unable to be converted due to a FullText Index. I see Innodb has only one drawback :)
However, the results so far are very worth it. We are still having some overloads, but, they are certainly not mysql's fault. Apache/php is taking up too much load and memory at a certain point, but the G5 doesn't break a sweat.
We still have some configuring to due, as we started with 6 x 2G ibdata files, which mysteriously are only 1G on disk. my.cnf settings below. We also kept some ram in the MyIsam portion of the config for the one remaining (large/important) MyIsam table. Do the settings look kosher?
One test of the speed difference has so far registered a 5-10X speed increase (max). These also depend on time of day and filesystem deletes of multiple files for each, there is a backlog of perhaps another million items left to archive that this is working on, so we'll have this script as a working test for a few more days, as we can only run this during low load hours.
Before Innodb: < START (07:00:00) > Done. 2279 archived. < STOP (07:50:07): 3005.91sec > < START (20:00:00) > Done. 5603 archived. < STOP (20:50:16): 3015.15sec > < START (22:00:00) > Done. 7265 archived. < STOP (22:50:04): 3002.85sec >
After Innodb: < START (18:00:00) > Done. 16092 archived. < STOP (18:50:03): 3002.25sec > < START (19:00:00) > Done. 19683 archived. < STOP (19:50:03): 3002.38sec > < START (22:00:00) > Done. 25370 archived. < STOP (22:50:04): 3003.6sec >
Under a simultaneous user/high load situation, would you suggest running with pconnects in php/mysql,
and with persistent connections in apache? We have been seeing 300-400 outbound mysql connections from the main app server (via netstat -n -t|grep -c ":3306 ", which include mostly TIME_WAIT) , 300-450 apache processes&outbound *:80 connections on the primary app server (we are researching/pricing 2-4 frontend 1U servers now.. roughly 2Gram/2Ghz+/gigabit boxes, either P4/Athlon/Athlon64 or Xserves).
We are still getting some odd results in stats, such as the same high 'change db' and 'connection' rates.
relevant(?) innodb status;
Per second averages calculated from the last 53 seconds (now, during low hours. I am not sure how many of these stats would change during high use hours, I will check tomorrow.)
5266530 OS file reads, 2492377 OS file writes, 448439 OS fsyncs
34.68 reads/s, 18790 avg bytes/read, 14.81 writes/s, 1.74 fsyncs/s
Ibuf for space 0: size 1, free list len 249, seg size 251,
970319 inserts, 970319 merged recs, 189753 merges
Hash table size 4980539, used cells 2737132, node heap has 3893 buffer(s)
9649.16 hash searches/s, 1424.65 non-hash searches/s
Total memory allocated 1654471880; in additional pool allocated 2385280 Buffer pool size 76800 Free buffers 124 Database pages 72783 Modified db pages 9798 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 8644115, created 38059, written 2814095 39.87 reads/s, 0.21 creates/s, 17.83 writes/s Buffer pool hit rate 1000 / 1000
Number of rows inserted 2353929, updated 1543175, deleted 1191888, read 547022884
6.98 inserts/s, 0.47 updates/s, 5.94 deletes/s, 17275.54 reads/s
relevant status;
| Aborted_clients | 3088 | | Aborted_connects | 1 | | Bytes_received | 2788318966 | | Bytes_sent | 1674966066 | | Com_change_db | 5245603 | | Com_delete | 1091654 | | Com_insert | 1933786 | | Com_insert_select | 440592 | | Com_lock_tables | 82167 | | Com_select | 5133100 | | Com_unlock_tables | 82172 | | Com_update | 1525300 | | Connections | 788173 | | Created_tmp_disk_tables | 350 | | Created_tmp_tables | 96399 | | Created_tmp_files | 27 | | Flush_commands | 1 | | Handler_commit | 82157 | | Handler_delete | 0 | | Handler_read_first | 38191 | | Handler_read_key | 1081224301 | | Handler_read_next | 3683264158 | | Handler_read_rnd | 70681449 | | Handler_read_rnd_next | 1174208910 | | Handler_rollback | 729518 | | Handler_update | 55200716 | | Handler_write | 70961992 | | Key_blocks_used | 498760 | | Key_read_requests | 3687053126 | | Key_reads | 4280919 | | Key_write_requests | 589851 | | Key_writes | 1006505 | | Max_used_connections | 600 | | Opened_tables | 2038 | | Questions | 17855994 | | Qcache_queries_in_cache | 888 | | Qcache_inserts | 5129477 | | Qcache_hits | 1520209 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2492 | | Qcache_free_memory | 267333256 | | Qcache_free_blocks | 326 | | Qcache_total_blocks | 2120 | | Slow_queries | 5405 | | Table_locks_immediate | 9339322 | | Table_locks_waited | 29543 | | Threads_cached | 14 | | Threads_created | 8504 | | Threads_connected | 68 | | Threads_running | 1 | | Uptime | 243958 |
Relevant my.cnf data:
skip-locking
key_buffer = 512M
max_allowed_packet = 8M
table_cache = 2500
sort_buffer_size = 8M
read_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache = 16
thread_concurrency = 8
max_connections = 1000
skip-name-resolve
skip-bdb
ft_min_word_len = 2
join_buffer_size = 8M
query_cache_size=256M
bulk_insert_buffer_size=256M
query_cache_size=256M
tmp_table_size=128M
read_rnd_buffer_size=8M
record_buffer=32M
open_files_limit=10000
log-slow-queries
log_warnings
log-slave-updates
slave-skip-errors=1062
tmpdir = /tmp/
log-bin = /Volumes/Sql-2/logs/raptor-bin.log
innodb_data_home_dir = /Volumes/Raptor-10k/data/
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M; ibdata6:2000M:autoextend
innodb_log_group_home_dir = /Volumes/Raptor-10k/data/
innodb_log_arch_dir = /Volumes/Raptor-10k/data/
innodb_buffer_pool_size = 1200M
innodb_additional_mem_pool_size = 256M
innodb_log_files_in_group=2
innodb_log_file_size = 250M
innodb_log_buffer_size = 20M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 30
-- Adam Goldstein White Wolf Networks http://whitewlf.net
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
