I cannot seem to allocate any large amounts of memory to Mysql on our
system...
Can anyone suggest any settings/changes/etc to get this running to the
best of it's ability?
Dual 2Ghz G5, 4G ram, OSX 10.3.2, 73G-10Krpm Sata Raptor drives
Using both the 'Complete Mysql4.0.15" and Standard binary package
4.0.17 I cannot seem to get the daemon to accept using a large
Key_buffer setting, such as 1900M or 2600M (though 1600M seemed to work
the first startup).
I get this error:
Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306
040126 05:40:16 mysqld started
*** malloc: vm_allocate(size=2597892096) failed (error code=3)
*** malloc[14345]: error: Can't allocate region
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306
040126 05:43:00 mysqld started
*** malloc: vm_allocate(size=1984614400) failed (error code=3)
*** malloc[14378]: error: Can't allocate region
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306
This is a dedicated mysql backend server using MyISAM tables
(currently) and we need it to run a fairly heavy load.
This is only the relevant conf data:
[mysqld]
skip-locking
key_buffer = 1990M
max_allowed_packet = 8M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 3M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 8
max_connections = 1200
skip-name-resolve
skip-bdb
skip-innodb
ft_min_word_len = 2
tmpdir = /tmp/
log-bin = /var/log/mysql/raptor-bin.log
These are the largest tables in the db (other clipped):
Raptor: ../data root# ls -lhS /Library/MySQL/var/db1/
total 9177432
-rw-rw---- 1 mysql mysql 975M 21 Jan 20:30 axxx_search.MYD
-rw-rw---- 1 mysql mysql 619M 21 Jan 20:30 axxx_search.MYI
-rw-rw---- 1 mysql mysql 571M 21 Jan 20:05
td_visitor_archive.MYD
-rw-rw---- 1 mysql mysql 492M 21 Jan 20:37 message.MYD
-rw-rw---- 1 mysql mysql 435M 21 Jan 20:30 axxx_description.MYD
-rw-rw---- 1 mysql mysql 412M 21 Jan 20:37 enxxx.MYD
-rw-rw---- 1 mysql mysql 336M 21 Jan 20:37 enxxx.MYI
-rw-rw---- 1 mysql mysql 200M 23 Dec 09:05
axxx_title_images.MYD
-rw-rw---- 1 mysql mysql 97M 21 Jan 20:06 rating.MYD
-rw-rw---- 1 mysql mysql 81M 21 Jan 20:06 rating.MYI
-rw-rw---- 1 mysql mysql 49M 21 Jan 20:24 bxx.MYI
-rw-rw---- 1 mysql mysql 28M 21 Jan 20:24 bxx.MYD
...clip...
These are our best benchmarks:
alter-table: Total time: 6 wallclock secs ( 0.03 usr 0.04 sys + 0.00
cusr 0.00 csys = 0.07 CPU)
ATIS: Total time: 21 wallclock secs (17.20 usr 3.37 sys + 0.00 cusr
0.00 csys = 20.57 CPU)
big-tables: Total time: 15 wallclock secs ( 4.30 usr 3.60 sys + 0.00
cusr 0.00 csys = 7.90 CPU)
connect: Total time: 4 wallclock secs ( 0.61 usr 0.29 sys + 0.00
cusr 0.00 csys = 0.90 CPU)
create: Total time: 98 wallclock secs (11.63 usr 3.02 sys + 0.00 cusr
0.00 csys = 14.65 CPU)
insert: Total time: 1405 wallclock secs (312.96 usr 78.26 sys + 0.00
cusr 0.00 csys = 391.22 CPU)
select: Total time: 122 wallclock secs (33.21 usr 7.03 sys + 0.00
cusr 0.00 csys = 40.24 CPU)
wisconsin: Total time: 8 wallclock secs ( 5.00 usr 0.49 sys + 0.00
cusr 0.00 csys = 5.49 CPU)
--
Adam Goldstein
White Wolf Networks
http://whitewlf.net
