Hi, I have setup a MYSQL database to log web traffic traffic visits. The daily records added to the DB is about 100K, the database is increasing size very rapidly and thus i wanted to ask how many records a MySQL Db can hold... FYI, each record has no more than 100 Characters. It increases in size about 5MB per day. And yes, theres nothing to worry about the host, as i am the host myself. Any comments would be highly appreciated. Talker.
I have been working with Mysql database that are storing about 50 millions records in good conditions However this is NOT what I would normally do Why don't you try to split data in multiple tables using merge tables OR separate them per days or groups or something like this Another option would be to store stats only and not raw data (store raw data only for past few days)
Hey, Thanks for replying... Its a good idea to store the records in different tables. But how would i select a recordset from multiple tables? Do you have an example SQL Query? I used to store stats only (update the counter for traffic only). But i now require to store some detailes... which are 1) account 2) date 3) time 4) IP 5) referURL (kept private) Thats all the info that i have to log. But as the records inserted per day are too many, i dont want my php app to takes ages to select appropiate data for reporting.. What do you advice?
Try using merge tables in MySQL and that would allow you to select from one single table even data is stored in multiple ones... You need to study first this MySQL table format for sure On the other side I had problems using this format - some MySQL bugs.. SO I would simply organise it in a such way to allow you to retrieve needed records easily.. For example.. you need data for an account... Create one table for all accounts from a certain group and create groups of 100 accounts Then you need to organize the application in a such way to take this into consideration
Thanks for the reply.. I dont think i should go for merge tables as it may cause trouble for me.. I am thinking of making 7 tables... one for each day. Can you think of any way to select data from all 7 tables at once.. such that i get 1 recordset instead of 7 ? Thanks for the help so far
SELECT * FROM TABLE1 UNION SELECT * FROM TABLE1 or at least something like this BUT remember that this is not really going to help you retrieveing data from 7 tables instead of one... The ideea is to have smaller tables well optimised in the places where they are mostly accessed but you can use this for example in the admin section It also depends on how you have the table indexes and how you need to retrieve data
mmmm right.. What would you mean by well optimised in the places where they are mostly accessed.. I have not yet set any indexes for the talble.. can you help me with that? I would mostly select data based on the `date` column and `account` column becase i have to deliver reports based on month, day for a specific account.
What I mean is that in some places you do not care about how long a query will take but in other places you do because you get many hits on those pages In your case I would suggest using InnoDB tables and I would add a composed index on account AND date.. this will also be usefull for querying by account and date separatelly
Currently the table is MyISAM...i may not be able to change the Table format now.. however.. Do you mean that i should add indexes to the 2 columns? date and account?
I just created indexes on both columns but i had to give it a new keyname. the first on "PRIMARY" has an index on the `id` column which is auto_increment.. Now i can see 2 indexes, PRIMARY for `id` newindex for `date` and `account` Am i doing this right?
Yes this is the right way to do it.. You can test variopus queries now with or without index to see th performance differences
Because there are lots of inserts going on into your tables, i would rebuild indexes regularly so that your select statements retrieve data fast. However, i would be cautious on anything to do with UNION as it involves lot of work tables activity with in temp database. Make sure you have large enough temp db. I don't know much about mysql and don't know if tempdb exists for mysql database. Best regards link dev
how long do you log each days data ? you could use a 'delete old records' routine once in a while that would help your db fetch time
yes, you could delete, but you could also archive the tables... just select 1 month's worth of records, create a new table for that month, push the records to the archive table, then delete them from your current table. Doesn't matter how big your tables are for archives, but you want to keep your actual tables lean if you can (Just make sure everything is indexed properly so if you go on vacation and it grows to millions of records that your queries will still be fast)
Thanks for all the replies. I wont be keeping the logs for very long... Logs for each account will be deleted after 15 days of storage. I have about 70 accounts running concurrently .. each having max 50,000 log records... When the records touch 50K for that account .. the log stops inserting data .. i plan to delete the account + logs after 15 days. So .. if im not wrong, 70 accounts X 50K logs each = 3500K log records will be stored in the table at all times, new ones will be added and previous ones will be deleted... I think i will have to use a cronjob to search for accounts that have touch their limit and are 15 days old at the same time. Any comments?
This sounds just fine and in those conditions with only that many records you should not even worry about separating data in multiple tables If you think there will be more than that then you should consider it so the operations are faster