1) Edit /etc/my.cnf change following parameters as nessary
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
2) Use indexes (Will see more on indexes later.....)
1 comment:
We have 3 areas to consider when developing Data Warehouse (with regard
to Database)
1) Populating the event tables (using a ETL process)
2) Populating summary tables from event tables (using a stored
procedure)
3) Query the summary tables for reports (BO reporting)
* for 1) It would be better to have Innodb tables
* and for 2) and 3) we can have Myisam tables
1) We have to find out what is the best 'engine' out of INNODB and
MYISAM for above cases.
2) INNODB is transactional
3) myisam is having the best query performance,but it is not
transactional. (that means make table locks)
4) Best thing is use INNODB for event tables. They only make row level
locks
5) If server crashed for big myisam tables, u will have to manually
repair and it takes hours
6) Any innodb table auto recover, if server crashed happen.
7) If u can use myisam for summary table or daily cache table it is good
8) myisam backup also very easy and can do online backup and just
copying table files only.
9) There ar some disadvantages keeping both the engine types. because
you need to set parameters separately for both. for example, INNODB
memory usage you have to increase BUFFER_POOL_SIZE, for MYISAM its
key_buffer have to increase. Also FOREIGN KEY keeping issue is there,
only inoodb provides the foreign keys.
10) You will get additional advantage from myisam. If there is an IO
performance issue, for example, if you try to get a DB dump to same disk
or big IO operation related insert running in partition, all the inserts
will be slow. innodb is having the above issues. But still you can work
with myisam without any issue and using myisam we can avoid above issue.
11) Design level optimization - There are ways it can be avoided in
table design level, or dumping things in to separate partition and
create several data files.
12) Best thing is to to create daily cache table (Myisam tables), and u
can do any indexing when u do that to fast the query, because indexing
big table is always slow. huge table like VOICE_CALL_SUMMARY running in
live table it takes 2 hours to index, but in daily cache table it is 20
secs
13) innodb is fast when inserting or loading data to table, but in
querying myisam is still fast
14) Anyway myisam is very fast without indexing, comparing to INNODB.
because without indexing INNODB always does full table scanning
15) Anyway in summary, for event things we must go for INNODB. Otherwise
big issue comes when do purging using deleted
16) If u ar planning to use files per table, that IO issues will not
come, but performance is relatively slow for normal queries, but bottle
necks will never come
17) Also consider primary keys for tables, use bigint as data type. Also
plan backup and purging
Post a Comment