Hi Scott! We use MySQL on 9 Mac OS X machines. While we are looking at moving some of that back to big Sun boxes, that's a memory access/64 bit issue, not (directly) a performance issue.
Looking at the live stats one of the machines has an uptime of 55 days and has averaged 405.78 queries per second across the whole time (thatıs about 35M queries per day) - we run 8 in production, and one admin server which replicates from everyone and does our backups and feeds the reports servers and so on, not all 8 production machines are running this busy, but it gives you an idea of our traffic and throughput. In our experience the key here is configuration. Overall for our use (discussion boards) we find InnoDB tables are dramatically faster for us than MyISAM. The key thing will be setting your memory settings in my.cnf to be as generous as possible. Query Cache is great for us, typically we see about 30% of our SELECT queries going through query cache, so thatıs definitely worth turning on. Does the machine do anything else or is it just serving MySQL? How much memory does it have? my-huge.cnf in actual fact isn't that generous. Typically my-huge would take about 500M of ram, maybe a gig if you have LOTS of connections set. Works OK if you are running a machine that needs power for other things, such as running Apache and PHP and MySQL all in one box... But if your mysql box is just serving mysql and nothing else, you need to tune the machine as much as possible for mysql. Turn off system processes and options not really needed for running a database. And tune up that memory.... Due to memory limits on the Mac OS X quasi 64 bit emulation it is possible for the Operating System to access more than 2Gbytes of ram, but not possible for any process to do so (including, sort of) mysqld. The trick here is that innodb grabs it's own chunk of memory, so in actual fact we can get nearly 4Gbytes of memory allocated to MySQL on OS X... Settings of note here (if you are into InnoDB) are: key_buffer_size=1024M sort_buffer_size=4M read_buffer_size=4M read_rnd_buffer_size=8M myisam_sort_buffer_size=64M query_cache_size=128M innodb_buffer_pool_size=1850M innodb_additional_mem_pool_size=256M innodb_flush_log_at_trx_commit=0 This last one improved performance for us under InnoDB dramatically. We have a lot of connections, so the read buffers and so on are multiplied by the number of connections... If you are only using MyISAM and don't have so many connections in max_connections you can probably increase key_buffer_size to closer to 1500 or so... The query cache you should increase and monitor (using "SHOW STATUS LIKE 'Qcache_%';") and when your server has been running more than 48 hours and Qcache_free_memory is still giving you a comfortable overhead you can be happy with it... If there's not much left in Qcache_free_memory, increase it and try again. Total memory available under OSX with no InnoDB is 2Gbytes... So add up your key_buffer and the myisam_sort_buffer and the query_cache and thesort/read buffers multiplied by the number of connections and you get to where you can get to. Of course if your server has 2Gbytes or less you need to reduce this somewhat to leave room fro the OS to run and stay within the available memory of the machine. We are trying now to determine if we can wait with OS X for their true 64 bit operating system due sometime in the first 6 months of 2005 (which we have to assume is June) or if our new database server budget should go to Sun boxes which give us real 64 bit now, and thus let us throw a whole lot of memory at InnoDB, or if we stay with our Apple strategy and expand when we get the new OS. Problem, here being Apple has a great Storage situation, both loc al to the Xserver and using the Xserve Raid... Sun has a problem in storage land right now. Its not clear to us which way we should go. From a cost perspective once you load up a machine with 8Gbytes of ram and multi processors and lots of disk space and multi year onsite support contracts it doesn't make a lot of difference if we go Sun, Apple, Dell, HP etc etc... They all come out within a few $$ of each other. So for us it's 64 bit which is important, Sun leads the way here, but Apple should get there very soon. Anyway, it most certainly is possible to run mysqld under high load on OS X, we do it all day, every day. The servers have 8Gbytes of ram but really aren't using much more than 2 yet... (we have some memory settings for MyISAM caches but our MyISAM tables are really only the archived data, so we don't get much benefit from that. Once OS X 10.4 comes out and we can go 64 Bit, I expect we'll be a lot happier with our OS X G5s and can put enough through them that we'll start to see CPU use become significant... It'll be like getting two more servers for every server we already have. Best Regards, Bruce On 1/6/05 7:58 PM, "Scott Wilson" wrote: > Hello, > > I'm interested to hear peoples' experiences running mysql on OS X. > I've moved the database for a fairly heaily used website (~ 2M queries > a day) over to a new dual 2GHz XServe running OS X Server 10.3.7. > This database has run smoothly on an aging dual PIII machine running > freebsd for the past several years. > > My initial impression is that the performance gains aren't nearly what > I would have expected. For the most part the new machine is less > loaded, but at peak times it's arguably doing worse that it did the > old freebsd machine. > > The number a variables involved has hindered my creating comprehensive > benchmarks but some initial impressions from running stock mysql > benchmarks are that 4.0.23a on OS X performs around 10% faster than > 4.1.8a and that my old freebsd machine running 4.0.18 is less than a > factor of two slower. These are all using similar my.cnf settings > tuned along the lines of the my-huge.cnf sample config. > > Does anyone have any tips to offer for tuning OS X and mysql to play > well together? Is anyone running a heavily loaded mysql server in > production under OS X? > > Thanks for you help! > > scott > > > Thread > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
