Announcement

Announcement Module
Collapse
No announcement yet.

Comments on my.cnf for high insert volume db?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Comments on my.cnf for high insert volume db?

    So we're looking to start testing our configuration for a new project that is essentially an ultra high insert volume database. We had an extremely thin budget for the project, so we had to go with MySQL. We're dealing with a lot of data (somewhere in the range of 750Gb size on disk increase per year, with the potential of doubling after a year).

    We've never really had to tune a MySQL server like this before, where performance was such a large concern. Before we'd just adjust a few simple things and leave it at that.

    We're satisfied that we can hit the insert volume we need, and are now working on tuning things to get every last bit of performance before we start a series of tests we expect to take 3 weeks that will test every aspect of the system under every scenario we could concoct.

    The hardware is:

    2x
    Dell PowerEdge 1950
    2x Quad Core 2.66 Ghz Xeon
    16Gb RAM
    4x 10k RPM 73GB drives (raid-10)


    2x
    Dell PowerVault MD1000
    15 750Gb SATA drives in a raid-10


    We have each 1950 connected to a PowerVault, in a master-slave replication scheme.

    Currently the PowerVaults have 2x2TB partitions for data and 1x2TB partition for binary logs. This is configured in a single raid 10 virtual disk.

    The work load is VERY low connections (4-16 realistically) but extremely high insert volume. All inserts are happening locally over a Gigabit link. All of our tables are InnoDB and transactions are a requirement. We have no referential integrity because FKs were too slow/big on disk. Also some of our tables are (necessarily) extremely wide, one table in particular contains 300 columns (this is a denormalized table that contains data that absolutely must be instantaneously selected and is directly index by a single unique integer key).

    If there is ANY sort of information that anyone would like to know to help them comment intelligently, please ask!

    I am appending our my.cnf to the end of this post and would really appreciate any comments, as we've never had to tune a MySQL config that had any sort of real volume. FYI - We have already been told that it is stupid to do this on MySQL, so if that is your comment - it is not necessary, we know Smile

    Specific questions:
    Thread Concurrency, too low?
    We have not specified the innodb_commit_concurrency or the innodb_thread_concurrency, recommendations?
    Starting with gigantic innodb data files, or autoextending?
    Binary log file size, too small, too big?
    Innodb log file size, log buffer size comments?

    [mysqld]
    max_connections = 40
    skip-locking
    key_buffer = 256M
    max_allowed_packet = 32M
    table_cache = 512
    sort_buffer_size = 32M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    thread_cache_size = 8
    query_cache_size= 32M
    thread_concurrency = 16

    tmp_table_size=1000MB
    max_heap_table_size=1000MB

    log-bin=/pv/p4/mysql/logs/mysql-bin.log

    expire-logs-days = 20
    max_binlog_size = 504857600

    innodb_data_home_dir = /
    innodb_data_file_path = /pv/p1/mysql/data/ibdata1:2000000M;/pv/p2/mysql/data/ibdata1 :2000000M
    innodb_log_group_home_dir = /pv/p4/mysql/innodb_logs
    innodb_log_arch_dir = /pv/p4/mysql/innodb_logs

    innodb_buffer_pool_size = 14000M
    innodb_additional_mem_pool_size = 20M

    innodb_log_file_size = 500M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 0
    innodb_lock_wait_timeout = 20

  • #2
    The thread_concurrency setting doesn't really do anything except _hinting_ to solaris how many threads that you are going to use. So you can basically forget about this setting with good consciens.

    And as for the:
    innodb_thread_concurrency
    innodb_commit_concurrency
    I would leave them as default to begin with and then run som test and check the SHOW ENGINE INNODB STATUS to see if you possibly have any threads waiting for semaphores before changing it.

    InnoDB table spaces should be large to begin with.
    Or change the innodb_autoextend_increment to be much bigger than the default 8MB so that you avoid that it has to auto increase the table space all the time.

    The only other thing I can think of if you really want top performance is to disable the InnoDB double write buffer.
    As an estimation it will give you about 10% increase in performance.
    But then you will have to be willing to sacrifice robustness in case of a crash.
    And normally people aren't willing to do this for that little gain unless you are running some performance test in a lab environment.


    Apart from that I think that your settings look good.

    And for the record I don't think that it's stupid to do this on MySQL.

    Comment

    Working...
    X