SAN --- I think that's the key piece to this puzzle. It doesn't matter how
fat your network pipes are, you will always encounter some network lag when reading or writing data from and to a SAN. There is an abstraction layer that must bundle your request to the disk then translate it from the device that will translate into additional CPU overhead PER DISK TRANSACTION. Most people won't notice the difference when they are moving their personal files but a database certainly does. The number of disk reads and writes per second normally handled by hardware translates into network round-trips per second when you are using a SAN. Multiply each round trip (thousands, millions?) by the average lag and you have one source of your slowdown. Add just 1 millisecond to each read request and it quickly adds up to several seconds per query for tables your size. I just became really pressed for time today so must I defer to others on the list to interpret your settings. Please everyone, pitch in on this one, OK? Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "J S" <[EMAIL PROTECTED]> wrote on 09/10/2004 10:55:23 AM: > How fast do your disks rotate? What kind of disk controller are you using > (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what > RAID scheme are you using? > > > I had to ask about this one because I'm not too sure about these sort of > things. Apparently the box is using ESS disks on SAN, and they're RAID 5. > > > How big are your buffers and other working areas? (show variables like > '%buff%'; show variables like '%size%';). > > > mysql> show variables like '%buff%'; > +-------------------------+-----------+ > | Variable_name | Value | > +-------------------------+-----------+ > | bdb_log_buffer_size | 262144 | > | bulk_insert_buffer_size | 8388608 | > | innodb_buffer_pool_size | 8388608 | > | innodb_log_buffer_size | 1048576 | > | join_buffer_size | 131072 | > | key_buffer_size | 402653184 | > | myisam_sort_buffer_size | 67108864 | > | net_buffer_length | 16384 | > | read_buffer_size | 2093056 | > | read_rnd_buffer_size | 262144 | > | sort_buffer_size | 2097144 | > +-------------------------+-----------+ > > mysql> show variables like '%size%'; > +---------------------------------+----------------------+ > | Variable_name | Value | > +---------------------------------+----------------------+ > | bdb_cache_size | 8388600 | > | bdb_log_buffer_size | 262144 | > | binlog_cache_size | 32768 | > | bulk_insert_buffer_size | 8388608 | > | delayed_queue_size | 1000 | > | innodb_additional_mem_pool_size | 1048576 | > | innodb_buffer_pool_size | 8388608 | > | innodb_log_buffer_size | 1048576 | > | innodb_log_file_size | 5242880 | > | join_buffer_size | 131072 | > | key_buffer_size | 402653184 | > | max_binlog_cache_size | 4294967295 | > | max_binlog_size | 1073741824 | > | max_heap_table_size | 16777216 | > | max_join_size | 18446744073709551615 | > | max_relay_log_size | 0 | > | myisam_max_extra_sort_file_size | 268435456 | > | myisam_max_sort_file_size | 2147483647 | > | myisam_sort_buffer_size | 67108864 | > | query_alloc_block_size | 8192 | > | query_cache_size | 33554432 | > | query_prealloc_size | 8192 | > | range_alloc_block_size | 2048 | > | read_buffer_size | 2093056 | > | read_rnd_buffer_size | 262144 | > | sort_buffer_size | 2097144 | > | thread_cache_size | 8 | > | tmp_table_size | 33554432 | > | transaction_alloc_block_size | 8192 | > | transaction_prealloc_size | 4096 | > +---------------------------------+----------------------+ > 30 rows in set (0.01 sec) > > Your CPUs are probably up to > the task but we need to make sure you are using as much available memory > as you can. I see PowerPC in your system description... how fast is your > system bus (or does anyone else think that would make that much of a > difference here)? > > > Not sure how to check the system bus but this is the info on the CPUs: > > RS/6000 & pSeries Details > CPU Architecture=PowerPC Implementation=RS64-III, 64 bit > Machine has 2 CPUs (2 CPUs activated) > CPU Level 1 Cache is Combined Instruction=131072 bytes & Data=131072 bytes > Level 2 Cache size=4194304 > AIX 4.3.3.86 > > > On another thought....I was under the impression that you were trying to > create a single "daily" table for testing purposes (hence the date in the > table name) yet you seem to have 25M+ entries? Are you sure that's just > one day's worth of information? In fact from the time you sent the > earliest message in this post until you posted your table status, you > added about 10M new rows. > > > I was tailing the proxies from about lunchtime yesterday and piping the > output into the database. By the end of the day there were 25m row. > > _________________________________________________________________ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
