Skip navigation links

MySQL Forums :: InnoDB :: Innodb configuration optimal for my workload?


Advanced Search

Innodb configuration optimal for my workload?
Posted by: Fernando Perez ()
Date: October 03, 2011 09:14AM

Hi, i have a GPS tracking application, we use MySQL to store the data sent by devices, my application receives the data from the devices via TCP sockets and then launch a thread to insert/update the data in two innodb tables for each device (one table, eventdatalast, just to store the last report for every device and the other, eventdata, to store all the historical data). Due to the number of devices allready reporting to this server im doing between 15 and 20 inserts/updates per second. For my customers i have a java web application to track the units in a map, doing selects over eventdatalast every time i select a unit from a list.

We used MyISAM in the past but we changed to Innodb because with MyISAM i get my tables corrupted very often, and the table eventdata have 15M rows. Now with Innodb everything seems ok execept i get deadlocks in eventdatalast table.

The point of my post is, if someone is so kind, verify that the configuration of MySQL is the optimum for the type of load that the server has.

Server Configuration:
Windows 2003 Server Enterprise x64
Dual Xeon E5405(Harpertown) 2.00Ghz
8GB RAM
System HD 500GB SATA 7200RPM
HD for database 150GB Velociraptor SCSCI 10K RPM
MySQL Version: 5.5.16-log

my.ini
# CLIENT SECTION
[client]
port=3306

[mysql]
default-character-set=latin1

# SERVER SECTION
[mysqld]
port=3306

basedir="E:/MySQL/MySQL Server 5.5/"
datadir="E:/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB

max_connections=3000
query_cache_size=0M
query_cache_type=0
table_cache=2048
tmp_table_size=1M
max_heap_table_size=1M
thread_cache_size=1500

#*** MyISAM Specific options

myisam_max_sort_file_size=100G
myisam_sort_buffer_size=20M
key_buffer_size=33554432
read_buffer_size=1048576
read_rnd_buffer_size=256K
delay_key_write=OFF
sort_buffer_size=64K


#*** INNODB Specific options ***
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout = 300
innodb_support_xa=0
innodb_buffer_pool_size=5000M
innodb_log_file_size=1900M
innodb_file_per_table
innodb_open_files=2048
innodb_thread_concurrency=16
transaction_isolation = REPEATABLE-READ

autocommit=1
wait_timeout=60
connect_timeout=120
max_prepared_stmt_count=2000
max_allowed_packet = 1M
log_warnings=2
log_bin=CL-T078-110CN-bin
flush_time=0
binlog_format=row
innodb_locks_unsafe_for_binlog=1

Thanks in advance.

Options: ReplyQuote


Subject Views Written By Posted
Innodb configuration optimal for my workload? 2408 Fernando Perez 10/03/2011 09:14AM
Re: Innodb configuration optimal for my workload? 705 Rick James 10/04/2011 08:19AM
Re: Innodb configuration optimal for my workload? 591 Fernando Perez 10/04/2011 08:22AM
Re: Innodb configuration optimal for my workload? 651 Greg Kemnitz 10/04/2011 12:10PM
Re: Innodb configuration optimal for my workload? 613 Rick James 10/07/2011 04:38AM
Re: Innodb configuration optimal for my workload? 637 Fernando Perez 10/11/2011 08:11AM
Re: Innodb configuration optimal for my workload? 581 James Day 10/14/2011 12:45PM
Re: Innodb configuration optimal for my workload? 1296 Fernando Perez 10/14/2011 12:39PM
Re: Innodb configuration optimal for my workload? 762 Fernando Perez 10/14/2011 12:38PM
Re: Innodb configuration optimal for my workload? 872 Rick James 10/15/2011 11:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.