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]
