No problem:

Firstly, how are you measuring your updates on a single table?  I took
a few binary logs, grepped out for things that changed the table,
counting the lines (using wc) and then dividing by the # of seconds
the binary logs covered.  The average for one table was 108 updates
per second.

I'm very intrigued as to how you came up with 2-300 updates per second
for one table. . . did you do it that way?  If not, how did you do it?
 (We are a VERY heavily trafficked site, having 18,000 people online
and active, and that accounts for the 108 updates per second.  So if
you have more traffic than that. .  .wow!)

my.cnf:

[mysqld]
old-passwords
tmpdir                  = /tmp/
datadir                 = /var/lib/mysql
socket                  = /var/lib/mysql/mysql.sock
port                    = 3306
key_buffer              = 320M
max_allowed_packet      = 16M
table_cache             = 1024
thread_cache            = 80
ft_min_word_len         = 3

# Log queries taking longer than "long_query_time" seconds
long_query_time = 4
log-slow-queries = /var/lib/mysql/slow-queries.log
log-error = /var/lib/mysql/mysqld.err

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 12

interactive_timeout = 28800
wait_timeout = 30

max_connections         = 2200
max_connect_errors      = 128

# Replication Master Server (default)
# binary logging is required for replication
log-bin
server-id       = 15
binlog-do-db    = manhunt
binlog-do-db    = phpAdsNew
binlog-do-db    = mobile
max_binlog_size = 2G

# InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:3G;ibdata2:3G;
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_files_in_group = 2
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 5G
innodb_additional_mem_pool_size = 40M
innodb_log_file_size = 160M
innodb_log_buffer_size = 80M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_file_io_threads = 4

# Query Cache Settings
query_cache_size = 32M
query_cache_type = 2
--------------------------------------------------------------------------
table info for the table in question:
          Name: Sessions
         Engine: InnoDB
        Version: 9
     Row_format: Dynamic
           Rows: 10600
 Avg_row_length: 792
    Data_length: 8404992
Max_data_length: NULL
   Index_length: 24297472
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2005-12-01 15:04:52
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 317440 kB
--------------------------------------------------------------------------

We're running MySQL Version 4.1.12 on Fedora Core 3 x86_64.
-------------------------------------------------------------------------
The hardware is a Dell 2850 with 4 Intel(R) Xeon(TM) CPU 3.40GHz
processors, 1024 KB cache size.  Total RAM:  8199448 kB (8G)
-------------------------------------------------------------------------

We're quite beefy because of the amount of queries per second (3000)
and updates.  We are not aware of locking issues.  Our customers are
quite vocal (there are 8 folks in IT, and over 20 in Customer
service!) so when things are slow we know about it.

Sincerely,

Sheeri Kritzer

On 1/27/06, Jan Kirchhoff <[EMAIL PROTECTED]> wrote:
> that sounds interesting since we couldn't get good performance using
> innodb in our case - but thats a few years ago. things may have changed?
> I'll definitely give it a try next week, too.
> Could you give me more information on your system? hardware, size of the
> table, average number of updates/sec?
>
> thanks for your suggestions
> Jan
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to