Skip to main content

Joel Liu's Library tagged Mysql →  View Popular

Choosing innodb_buffer_pool_size | MySQL Performance Blog

  • My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description.


    Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I’ve seen a lot of clients which came through extreme sufferings leaving it at default value (8M). So if you have dedicated MySQL Box and you’re only using Innodb tables you will want to give all memory you do not need for other needs for Innodb Buffer Pool.


    This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough. You also should account for growth of course. You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces) because it does not only contain data pages – it also contain adaptive hash indexes, insert buffer, locks which also take some time. Though it is not as critical – for most workloads if you will have your Innodb Buffer Pool 10% less than your database size you would not loose much anyway.


    You also may choose to set buffer pool as if your database size is already larger than amount of memory you have – so you do not forget to readjust it later. This is also valid approach as if it is Dedicated MySQL Server you may not have a good use for that memory anyway.

  • The next step would be to decide How Much Memory do you need for other needs. This needs would be OS needs – your system processes, page tables, socket buffers etc all need memory. I would put this to 256M for small sized boxes to 5% of memory size on the big boxes, though it can be even less than that. Besides Operating System needs you also have MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are also things like innodb additional memory pool (which can grow more than memory you allocated for it, especially in case you have large amount of tables).

InformIT: MySQL Query Optimization > Using Indexing

  • Match index types to the type of comparisons you perform.
    When you create an index, most storage engines choose the index implementation
    they Match index types to the type of comparisons you perform. When you
    create an index, most storage engines choose the index implementation they will
    use. For example, InnoDB always uses B-tree indexes. MySQL also uses B-tree indexes,
    except that it uses R-tree indexes for spatial data types. However, the MEMORY
    storage engine supports hash indexes and B-tree indexes, and allows you to select
    which one you want. To choose an index type, consider what kind of comparison
    operations you plan to perform on the indexed column:
  • If you use a MEMORY table only for exact-value lookups, a hash index is a
    good choice. This is the default index type for MEMORY tables, so you need
    do nothing special. If you need to perform range-based comparisons with a MEMORY
    table, you should use a B-tree index instead. To specify this type of index,
    add USING BTREE to your index definition. For example:
27 Feb 09

How FriendFeed uses MySQL to store schema-less data - Bret Taylor's blog

  • hi - joel on 2009-02-27
  • As our database has grown, we have tried to iteratively deal with the scaling issues that come with rapid growth. We did the typical things, like using read slaves and memcache to increase read throughput and sharding our database to improve write throughput. However, as we grew, scaling our existing features to accomodate more traffic turned out to be much less of an issue than adding new features.
29 Jul 07

Kitchen Soap » Blog Archive » Slides from ‘Capacity Planning for LAMP’ talk at MySQL Conf 2007

  • This was a fun talk. I saw a lot of nods in the audience when I mentioned things pertaining to social

O'Reilly Radar > Web 2.0 and Databases Part 1: Second Life

  • From my end, the worst MySQL moment was when, in the midst of a colo move we decided that we could bring the system back up before we had moved our slave database. After all, what are the odds of the primary going down in the 2 hours it would take to schlep the slave over and bring it up? Apparently the odds were 100%.
  • Like everybody else, we started with One Database All Hail The Central Database, and have subsequently been forced into clustering. However, we've eschewed any of the general purpose cluster technologies (mysql cluster, various replication schemes) in favor of explicit data partitioning. So, we still have a central db that keeps track of where to find what data (per-user, for instance), and N additional dbs that do the heavy lifting. Our feeling is that this is ultimately far more scalable than black-box clustering. Right now we're still in the transition process, so we remain vulnerable to overload. As Cory mentioned, we're moving to an HTTP-based internal communication model in order to improve our flexibility.
1 - 20 of 64 Next › Last »
Showing 20▼ items per page

Selected Tags

Related Tags

Diigo is about better ways to research, share and collaborate on information. Learn more »

Join Diigo