From: Adam Goldstein (whitewlfwhitewlf.net)
Date: Wed Jan 28 2004 - 20:51:55 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff:

>> I don't think there would be any benefit to using InnoDB, at least not
>> from a transaction point of view
>
> For the longest time I was reading the books and listening to the
> experts
> and all I was hearing is InnoDB is great because it handles
> transactions.
> Having little interest in transactions per se I pretty much started
> tuning
> things out whenever people mentioned InnoDB.
>
> One day when talking to some MySQL AB folks they asked why I wasn't
> using
> InnoDB... I kind of looked at them blankly and replied that I don't
> need
> transactions, and they looked back as if I was mad.
>
> Turns out InnoDB is far better at handling large databases than
> MyISAM, we
> had a massive (and I do mean massive) increase in performance just by
> switching to InnoDB. Uses a little more disk space, but it's worth it,
> and
> with a 5GByte database and a G5 server you have room to spare, even if
> you
> only got the "smaller" disks.
>
> InnoDB is a major thing for us now, everything is InnoDB. If an
> Engineer
> complains something they have done is running slowly it usually turns
> out to
> be they made some new thing and didn't make the table InnoDB. The fix
> is
> easy and quick. I also suspect that you could do away with that nightly
> table repair that ties up the machine for hours at a time if you were
> using
> InnoDB.
>
> We have 4 G5 towers serving MySQL for us, all DP2GHz machines with
> 4GBytes
> of RAM. If your data is changing rapidly, as it appears from your
> samples
> most pages include some sort of insert, you will have limited benefit
> from
> the Query cache - every time a table receives any type of change to
> it's
> data any queries in the query cache that use that table are dumped. In
> February we are adding to the mix with 2 G5 XServes... These are for
> new
> projects, the current servers are handling their loads fine.
>
> On the Disk side we got the dual 250GBytes and mirrored them for
> redundancy,
> speed isn't an issue as far as we can tell.
>
> We chose to replace our old database servers with G5s. The old
> machines were
> quad processor Sun boxes, and one was an 8 CPU machine. The G5s left
> them
> all for dead in terms of performance, although I'd prefer a couple of
> extra
> processors, something inside me still feels better knowing that when a
> process goes AWOL it's not holding up 50% of the server's resources.
> The
> Application servers are still typically Sun, although new ones won't
> be.
>
> We average about 140 Queries per second per machine (of course the load
> isn't that well distributed... but it gives you an idea), and typical
> high
> points are about 400 - 500 qps on any given machine without stressing
> the
> machines (replication catch up can see 1500 - 2000 queries per second,
> but
> that's not so common and of course is mostly inserts).
>
> Before we did the upgrade to 4.0.17 during last Friday's maintenance
> window
> we were over 1.5 billion queries total for the 28 days the machines
> had been
> up.
>
> So.. My tips for you:
>
> 1) Consider a switch to InnoDB, the performance hit was dramatic, and
> it's
> about SO much more than transactions (which we still don't do)!
>
Consider it switched! as soon as I find the way to do so :)
Are there any changes necessary to his code/queries to use innodb?

> 2) Drop the query cache to something more practical, a gigabyte is
> fine if
> your data is static, if it's not it's way too much. We use 128MBytes
> and
> typically have about a 30% hit rate on the Query cache and the busiest
> server is showing 80MBytes unused memory in the query cache and a 41%
> hit
> rate, and our databases take about 40G of disk space. Remember having
> a big
> query cache doesn't help if it's mostly sitting unused (in fact if
> ours are
> still sitting with 80M free in a week I'll drop all of them 64MBytes).
>
we have an average of ~15-20%, with times sustaining 30+%

> 3) Give lots of memory to InnoDB, I'll share my settings below.
>
Thank You!

> 4) Take most of the non InnoDB memory settings and drop them down real
> low,
> InnoDB does well on it's own and if you convert all tables you don't
> need to
> leave much in the way of resources for MyISAM.
>
ok

> 5) Turn on and use the slow query log (and if need be change the time
> needed
> to qualify as a slow query, the default 10 seconds is a lifetime). You
> may
> not code the queries yourself, but you can identify the queries that
> are
> causing problems and from there you can advise the client on changes
> to the
> database structure (indexes etc) or at least tell him exactly what the
> problem queries are.
>
The slow log has helped us a lot in the past... with the current slow
log settings, only
about 0.1% are slow queries. 3K out of 4million in the past 18hours.
Currently the time appears to be set at 2 (From show variables:
slow_launch_time 2 ).

> 6) Go get MyTOP from Jeremy Zawodny at
> http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3
> but
> that may just be what I am used to... You may not be able to control
> the
> coding part but you can at least monitor the server and see what it's
> up to
> and quickly and easily see problems.
>
Great tool.. only recently started using it.

> 7) If you decide to stay with MyISAM and not InnoDB then you will want
> as
> much memory as you can in the Key Buffer while leaving some space in
> the
> sort/read/join buffers.. I'd up the sort/read/join buffers to maybe
> 10MBytes, or even 20Mbytes, if you need to drop Key buffer to 1500M to
> give
> you the space for the others. We got OKish results on MyISAM with the
> larger
> sort/read/join buffers - InnoDB made all the difference though.
I've only gone as high as 6M on those before.

> Before giving you our settings I do want to point out one thing... We
> haven't fine tuned the memory settings since we did the G5 switch. At
> the
> time I was bringing the machines up they needed to be up quickly, and
> when
> it didn't work correctly with my original settings I had to make
> adjustments
> to get it to run at all. Ideally I want InnoDB holding about 3GBytes
> of ram,
> I'll make adjustments next month when I can play on an offline server
> and
> figure out what works best.
>
We've got 4G and dual 2Ghz like you.. and, it appears that the G5 may
be best
used as a dedicated mysql box, so, whatever it can best use of the 4G,
the better.

> This one worked and got the machines up inside the maintenance window,
> so I
> went with it (we get 2 hours a week max. and before we can touch the
> database servers we have to bring down 120 application servers and we
> have
> to finish with the database servers in enough time to bring up the 120
> application servers before the two hours is over). When we get the G5
> XServes I will have some more time to run tests on just how far I can
> push
> the InnoDB Buffer pool... As you can understand we don't like to bring
> down
> our servers for this type of testing. Clearly our performance isn't
> too bad
> or I would have focused on this sooner.
>
> Best Regards, Bruce
Unfortunately, while we would love to have similar down times to that,
we obviously
have overloads on a daily basis, bringing the machine down, or bogging
it out, for vast periods of the day... This is only recently (past 2-3
months) before that, it bogged out only
a couple time a day, and the slowness was, at least, functional-
though, thats a very relative term, especially when dealing with users
who may have shorter attention spans.

How are you spanning your queries over multiple DB servers? and are all
writes being done on one master server, or have you found a way to do
2 way replication? Currently we have only the main appserver
apache2/php/thttpd, and are trying a secondary apache2/php server (dual
PIII/850/2G) which doesn't appear to be handling the load well at
all... We're looking, obviously, at adding several front end
appservers, though we want to ensure the G5 will be able to handle it's
job if there are 5-6+ frontends on it.

Here's a few stats from today. If you see anything oddball, please let
me know. I see the Qcache_free_memory is awfully large, I assume that
means it's being tremendously wasted.

| Aborted_clients | 342 |
| Aborted_connects | 0 |
| Bytes_received | 1112989096 |
| Bytes_sent | 3260525070 |
| Com_admin_commands | 249 |
| Com_alter_table | 6 |
| Com_change_db | 1435919 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_delete | 92572 |
| Com_insert | 383955 |
| Com_insert_select | 0 |
| Com_lock_tables | 24 |
| Com_select | 1479741 |
| Com_show_databases | 4 |
| Com_show_fields | 70 |
| Com_show_keys | 20 |
| Com_show_processlist | 147 |
| Com_show_status | 163 |
| Com_show_tables | 378 |
| Com_show_variables | 166 |
| Com_unlock_tables | 24 |
| Com_update | 295677 |
| Connections | 242200 |
| Created_tmp_disk_tables | 136 |
| Created_tmp_tables | 29929 |
| Created_tmp_files | 75 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 125303 |
| Handler_read_first | 3373 |
| Handler_read_key | 232942786 |
| Handler_read_next | 794839470 |
| Handler_read_prev | 136750 |
| Handler_read_rnd | 20966398 |
| Handler_read_rnd_next | 1139120127 |
| Handler_rollback | 0 |
| Handler_update | 18227273 |
| Handler_write | 45761607 |
| Key_blocks_used | 551336 |
| Key_read_requests | 3064825478 |
| Key_reads | 512829 |
| Key_write_requests | 1253988 |
| Key_writes | 1167677 |
| Max_used_connections | 231 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 |
| Open_files | 584 |
| Open_streams | 0 |
| Opened_tables | 867 |
| Questions | 4212802 |
| Qcache_queries_in_cache | 1492 |
| Qcache_inserts | 1477799 |
| Qcache_hits | 249702 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2129 |
| Qcache_free_memory | 1071307256 |
| Qcache_free_blocks | 218 |
| Qcache_total_blocks | 3257 |
| Select_full_join | 11 |
| Select_full_range_join | 0 |
| Select_range | 72508 |
| Select_range_check | 0 |
| Select_scan | 7041 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 3124 |
| Sort_merge_passes | 40 |
| Sort_range | 75703 |
| Sort_rows | 23428492 |
| Sort_scan | 24968 |
| Table_locks_immediate | 2049162 |
| Table_locks_waited | 90513 |
| Threads_cached | 6 |
| Threads_created | 16528 |
| Threads_connected | 6 |
| Threads_running | 4 |
| Uptime | 66585 |

>
> Here are our memory related settings from our G5s:
>
> set-variable = max_connections=1000
> #
> # Remember some memory is allocated to each connection,
> # so this can be a factor
>
> set-variable = key_buffer_size=16M
> set-variable = table_cache=4096
> set-variable = sort_buffer_size=2M
> set-variable = read_buffer_size=2M
> set-variable = read_rnd_buffer_size=2M
> set-variable = thread_cache_size=32
> set-variable = myisam_sort_buffer_size=2M
> set-variable = query_cache_size=128M
> #
> # Really here the query cache is the main thing, we tend
> # to leave small buffers for the rest for those times when
> # we use a MyISAM table, like when an engineer forgets to
> # make a table InnoDB for example (and remember to ALWAYS
> # leave the mysql database as MyISAM tables)
> #
>
> set-variable = innodb_buffer_pool_size=1500M
> set-variable = innodb_additional_mem_pool_size=256M
> set-variable = innodb_log_buffer_size=20M
> innodb_flush_log_at_trx_commit=0
> #
> # I include this last one here because it really helped us,
> # it's not memory related, but it made a big performance
> # difference in our case - check innodb.com for what it
> # does and decide if it will help your situation
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=whitewlfwhitewlf.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]