I've recently gotten an AMD Opteron 64-bit machine for MySQL testing and eventual deployment. I have a web server with MySQL and 48 databases of roughly 36GB total, stored as INNODB. The current server is a dual Xeon 2.8Ghz machine with 4GB RAM. Since it's 32-bit about 1.2GB is the highest I can get the innodb_buffer_pool_size set to. On the Opteron I have 8GB RAM total and have the innodb_buffer_pool_size set to 6GB successfully. The problem is that it's slower on the machine with more memory and more of the database(s) cached. Hmm... In both cases I downloaded MySQL 4.0.16 source RPM and built with --target i686 and x86_64 respectively. Are there build options or parameters which will help or is this sufficient? The Xeon box runs RedHat 9 and the Opteron has RedHat Enterprise 3 WS.
Xeon: 1 row in set (55.04 sec) 1 row in set (4.38 sec) 1 row in set (0.17 sec) 1 row in set (12.33 sec) 1 row in set (3.02 sec) Opteron: 1 row in set (2 min 34.02 sec) 1 row in set (17.66 sec) 1 row in set (0.39 sec) 1 row in set (38.02 sec) 1 row in set (1.06 sec) Same queries with same data on each. The first 3 were against the same database and the 4th was after changing to a different database. On the 5th I changed back to the database the first 3 queries were performed against, I think the 8GB and larger cache was paying off there. The queries themselves were fairly complex with a left join and "like 'abc%'" and other such things against tables in the one database of ~5M and ~1M records in size. I'm just wondering if there's some reason why performance on AMD64 is to be expected to be like this or did I compile MySQL without a correct option or something like that. Maybe I've got something in my.cnf set _way_ off for this type of machine? Here's the my.cnf used on each with only the innodb buffer pool value being 1200M on the 32-bit box: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock default-table-type=innodb innodb_data_file_path = ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:2G;ibdata6:2G;ibdata7:2G;ibdata8:2G;ibdata9 :2G;ibdata10:2G;ibdata11:2G;ibdata12:2G;ibdata13:2G;ibdata14:2G;ibdata15:2G;ibdata16:2G;ibdata17:2G;ibdata18:2G innodb_data_home_dir = /var/lib/innodb/ set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /var/lib/iblogs set-variable = innodb_log_files_in_group=5 set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=0 innodb_log_arch_dir = /var/lib/iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=6200M set-variable = innodb_additional_mem_pool_size=40M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = max_connections=300 set-variable = query_cache_size=16777216 set-variable = key_buffer_size=16777216 tmpdir = /data/tmp basedir=/var/lib Thanks in advance! PS. After only loading 1 of the databases (albeit a large one ~5GB) on the Opteron machine it was consistently faster than the other machine. It should have been even faster than it was in my mind, 5GB of database data should all have been cached in RAM and been _very_ fast. It was faster, but not as much as I was expecting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
