22 Aug 14:58
Data Migration - MySQL Optimization
From: Ryan Whitney <rwhitney@...>
Subject: Data Migration - MySQL Optimization
Newsgroups: gmane.comp.finance.mifos.devel
Date: 2008-08-22 12:59:45 GMT
Subject: Data Migration - MySQL Optimization
Newsgroups: gmane.comp.finance.mifos.devel
Date: 2008-08-22 12:59:45 GMT
Hi all,
We’re working steadily on the data migrations and I’ve been doing some early looking into optimizing MySQL. As we get closer to completing our first set of scripts for customer data, be interested to revisit some of these. I believe some of them, while they didn’t have much impact for this research, may have more once we start working with more tables or the size of inserts (and possibly adding in selects), gets bigger.
For now, the most dramatic improvement was setting innodb_buffer_pool_size to 50% of the available RAM (recommendations online say 50-80%, so I shot low) and set innodb_log_file_size to 25% of the buffer pool size.
Anyways, tests results below
Performed on:
MacBook Pro
- 2.33 GHz Intel Core 2 Duo
- 3 GB 667 MHz DDR2 SDRAM
Test sets:
10k Dummy Customer inserts (Names are real, rest of data are placeholders)
110k (approx) Dummy Customer Inserts
Initial Tests:
- Baseline, No optimizations made:
10k - 10 seconds
110k - 120 seconds
Second Set of Tests:
- innodb_buffer_pool_size=1610612736 # Set to 50% of the Ram
- innodb_log_file_size=402653184 # Set to 25% of innodb_buffer_pool_size
10k - 6 seconds (166% improvement)
110k - 87 seconds (138% improvement)
Iteration 3
110k - 90 seconds (97% of previous iteration)
Iteration 4
110k - 88 seconds (99% of iteration 2)
Iteration 5
- Removed changes in iteration 4
- Try setting innodb_flush_log_at_trx_commit=0 in the config
10k - 7 seconds (86% of iteration 2)
110k - 92 seconds (95% of iteration 2)
Ryan Whitney
Mifos Technical Program Manager
rwhitney-DnEsXXvMQJxj/JC5u5XAOISLwOllVvif@public.gmane.org
Mifos - Technology that Empowers Microfinance (www.mifos.org)
We’re working steadily on the data migrations and I’ve been doing some early looking into optimizing MySQL. As we get closer to completing our first set of scripts for customer data, be interested to revisit some of these. I believe some of them, while they didn’t have much impact for this research, may have more once we start working with more tables or the size of inserts (and possibly adding in selects), gets bigger.
For now, the most dramatic improvement was setting innodb_buffer_pool_size to 50% of the available RAM (recommendations online say 50-80%, so I shot low) and set innodb_log_file_size to 25% of the buffer pool size.
Anyways, tests results below
Performed on:
MacBook Pro
- 2.33 GHz Intel Core 2 Duo
- 3 GB 667 MHz DDR2 SDRAM
Test sets:
10k Dummy Customer inserts (Names are real, rest of data are placeholders)
110k (approx) Dummy Customer Inserts
Initial Tests:
- Baseline, No optimizations made:
10k - 10 seconds
110k - 120 seconds
Second Set of Tests:
- innodb_buffer_pool_size=1610612736 # Set to 50% of the Ram
- innodb_log_file_size=402653184 # Set to 25% of innodb_buffer_pool_size
10k - 6 seconds (166% improvement)
110k - 87 seconds (138% improvement)
Iteration 3
- Ran10k - 7 seconds (86% of previous iteration)
"SET AUTOCOMMIT=0;"
Run queries
"COMMIT;"
110k - 90 seconds (97% of previous iteration)
Iteration 4
- Removing the changes in iteration 310k - 7 seconds (86% of iteration 2)
- Ran
SET UNIQUE_CHECKS=0;
run queries
SET UNIQUE_CHECKS=1;
110k - 88 seconds (99% of iteration 2)
Iteration 5
- Removed changes in iteration 4
- Try setting innodb_flush_log_at_trx_commit=0 in the config
10k - 7 seconds (86% of iteration 2)
110k - 92 seconds (95% of iteration 2)
Ryan Whitney
Mifos Technical Program Manager
rwhitney-DnEsXXvMQJxj/JC5u5XAOISLwOllVvif@public.gmane.org
Mifos - Technology that Empowers Microfinance (www.mifos.org)
------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/
RSS Feed