Question :
Folks
I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records files)
I have a table(billing_datas) with about 200 million records.( myisam table). ( basically we store all the cdr files data with the ids of carriers etc) which keeps on growing at a rate of 90 million rows per month
We need to generate reports based on selected “date from” and “date to” criteria. I m confused with the approach
The current scenario that we have is we are firing the query on the main table i.e billing_datas and creating the temporary table with “date from” and “date to” parameters and performing the aggregate calculations on this temp table. But the real performance hit is creating the temporary table is taking about 18 seconds for 1 month records.
-
should we create 3 summary tables based on
summary_monthly,summary_weekly,summary_daily and run the reports
query on these summary tables?? -
will it improve the performance as described in point 1?
Answer :
- Increase size of tmp_table_size
The maximum size of internal in-memory temporary tables. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.
You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
- Based on
tmp_table_sizevalue adjust max_heap_table_size
This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.
This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables.
After setting proper tmp_table_size and max_heap_table_size restart your server and try those reporting queries and observe performance.
Please refer How to optimize Group By queries from here:
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html