MySQL and Monthly Log Files

[captzero]

Hi all,
I've used the ImportCumulusFile script (and cumulus toolbox) to import the dayfile.txt file into a mySQL database for a while without any problems. I've even managed some graphs (TNET's modified JpGraph package) and a Top 10 page from the data. I'd like to import my monthly logs and use the data from those but I have confused myself a bit so a few questions before I get too carried away with it.

1. I have a database is called 'cumulus' and has a table called 'dayfile'. Should I be creating an additional table called 'monthly" in the same database or create a whole new database?
2. Can I use the ImportCumulusFile script to upload both the dayfile.txt and the latest monthly log file (Jul14log.txt) at the same time or should I use two versions of the script.
3. I can see there is currently 1599 rows of data in my dayfile table (one for each day). Am I right in thinking that the 'monthly' table will create a new row in the table for each line of data in each monthly log file? With 1 minute logging interval, my Jul14log file has (at time of posting) 35,011 lines of data and Jun14 has 46,282 lines of data. That's something like 2 million lines for the 4 and a half years of data I have. Will the table have that many lines?

I'd like to eventually use highcharts and graph some historic data so is it a bit of overkill to use the monthly logs anyway. What are other database users using at the moment, the dayfile, monthly logs or both?

Thanks in advance.

[tobyspond]

1. I have a database is called 'cumulus' and has a table called 'dayfile'. Should I be creating an additional table called 'monthly" in the same database or create a whole new database?

You should create a separate table for the monthly records. This can be done in the same database or a different one - it's your choice.

2. Can I use the ImportCumulusFile script to upload both the dayfile.txt and the latest monthly log file (Jul14log.txt) at the same time or should I use two versions of the script.

I use two separate versions.

3. I can see there is currently 1599 rows of data in my dayfile table (one for each day). Am I right in thinking that the 'monthly' table will create a new row in the table for each line of data in each monthly log file? With 1 minute logging interval, my Jul14log file has (at time of posting) 35,011 lines of data and Jun14 has 46,282 lines of data. That's something like 2 million lines for the 4 and a half years of data I have. Will the table have that many lines?

A mysql table will handle that many lines.

I'd like to eventually use highcharts and graph some historic data so is it a bit of overkill to use the monthly logs anyway. What are other database users using at the moment, the dayfile, monthly logs or both?

I primarily use the dayfile and the monthly file to fill in the gaps.

Kerry

[mcrossley]

Like Kerry 98% of my data comes from the dayfile table, there are odd bits I have to calculate myself from the raw log data that either Cumulus does not provide, or cannot be derived from daily summaries.

My full data table currently has just over 3 million rows, MySQL copes with no problems at all.

[captzero]

Thanks Kerry and Mark. I'll get started...

[duke]

I was curious about the first post - how many rows a database could have? A quick Google turned up similar answers. It appears a database can have hundreds of millions of rows without any particular impact on request times as long as:

1/ they are indexed properly
2/ and your queries are done properly (i.e. using the right indexes, for instance)