Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  1. should we create 3 summary tables based on summary_monthly,summary_weekly,summary_daily and run the reports query on these summary tables??

  2. will it improve the performance as described in point 1?

share|improve this question

1 Answer 1

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.

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.