wolfmanp Posted January 23, 2007 Share Posted January 23, 2007 Hi there, I'm writing a counter for pages on a high traffic website. My diea is to have text files for every item and then update thise files by adding a 1 to it so the size increases by 1 byte every hit. Then when the filesize goes over say 15 bytes then I do an update to the DB adding 15 to the hits column to save having to do a DB update call on every hit.My question is if I'm doing a file IO every page display will this bog down the sytem moreso then doing a mysql update? Do you guys think my idea is a good one?Actually writing this out I think I've thought of a better idea. What about a cron job every 10 minutes or so which scans the db and adds all the hits to the db every 10 minutes. This would save on extremely popular pages that get over a thousand hits in that time. Rather than doing around 60 updates in 10 minutes it would do 1 update. What do you guys think? Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/ Share on other sites More sharing options...
linuxdream Posted January 23, 2007 Share Posted January 23, 2007 I'm not sure of the specifics, but I would think that the filesystem I/O would really bog down your machine. Even if the MySQL server was on the same box as the web server, I believe that any data coming in back-to-back like it would on a very busy site would utilize memory and not as much system resources as a file write. Even the cron idea seems to be a bit much. MySQL servers are designed for rapid read and write data so why not use it for what it was designed for? But again....I'm not exactly sure of the numbers. Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167650 Share on other sites More sharing options...
Caesar Posted January 23, 2007 Share Posted January 23, 2007 I don't see why you wouldn't just use a MySQL update. One of my sites gets approx 4500 hits a day with multiple queries being done, and speed or server resources are never an issue. If you see less traffic than that, don't even worry about it. Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167654 Share on other sites More sharing options...
wolfmanp Posted January 23, 2007 Author Share Posted January 23, 2007 Dude, I'm talking about 50k uniques a day with 500k hits. that's 500k mysql updates a day, you think that's better than 500k file i/o's? I'm trying to find info on the web on the cost of doing such file i/os but am not finding anything. Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167679 Share on other sites More sharing options...
Caesar Posted January 23, 2007 Share Posted January 23, 2007 [quote author=wolfmanp link=topic=123734.msg511886#msg511886 date=1169595855]you think that's better than 500k file i/o's? [/quote]Short answer, yes. Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167685 Share on other sites More sharing options...
wolfmanp Posted January 23, 2007 Author Share Posted January 23, 2007 Do you have a long answer> Honestly I'd liek to understand why. I'd imagine that a mysql query should take more resources than a file io. I mean everything in linux is done through file i/o such as error logging, etc. I'm looking for some logical reasons why. Thanks for any help you can give. Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167693 Share on other sites More sharing options...
Caesar Posted January 23, 2007 Share Posted January 23, 2007 I am at work and don't have the will to elaborate so I will take this reply I found randomly[quote]flat files are NOT faster. [b]they can only be read from top to bottom, and usually they have to be read all the way through.[/b]A good database engine will take a query (Select field from table where field = '#*$!x') and first determine the best way to go into the database to solve the query, then leave the processing when the query is solved.Another main reason to use databases is the ability to minimize the repetitions of data. Think about having multiple spreadsheets for a project. If you have an ID number assigned to a particular item, you only need to reference the ID number between different sets of data, rather than replicating all of the data for each sheet.[/quote]Hope that helps answer your questions as to why. Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167700 Share on other sites More sharing options...
wolfmanp Posted January 23, 2007 Author Share Posted January 23, 2007 Yeah, that only applies to reads. That's if I was storing information in a file. I'm just apending a byte to a file and then taking the filesize as the number of hits since last update. I'm not searching through the file for something. just appending a byte to it. So taht doesn't really satisfy my curiosity ::) Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167704 Share on other sites More sharing options...
Caesar Posted January 24, 2007 Share Posted January 24, 2007 [quote author=wolfmanp link=topic=123734.msg511912#msg511912 date=1169596679]Yeah, that only applies to reads. That's if I was storing information in a file. I'm just apending a byte to a file and then taking the filesize as the number of hits since last update. I'm not searching through the file for something. just appending a byte to it. So taht doesn't really satisfy my curiosity ::)[/quote]I suppose if you're insisting on using flat-files, run some tests and output the amount of time it takes to execute. • database can handle tons of INSERTs a second, a flat-file might not (Depends on server load, traffic etc)• database is more reliable• database is more secure• If your site truly sees those numbers, then either you will need to optimize your code to use flat-files efficiently, or you are better off using a MySQL database.Good luck Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167729 Share on other sites More sharing options...
wolfmanp Posted January 24, 2007 Author Share Posted January 24, 2007 OK, I did some benchmarking using the following method: http://blog.rompe.org/node/85According to that when I do an update using mysql every call the time it takes to do it is 39000 microseconds to the lowest of around 7000 microseconds. While doing a file append of one byte takes 0 - 1000 microseconds with it being most of the time rating at 0 and jumping to the same range as previously when it does the rare update. I guess this works for me. File I/O done right beats mysql in this application. Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167825 Share on other sites More sharing options...
Hypnos Posted January 24, 2007 Share Posted January 24, 2007 You mean INSERT? Or are you using an SQL UPDATE?Was it InnoDB or MyISAM?Aren't you worried about data collission with that much writting to a flat file? Quote Link to comment https://forums.phpfreaks.com/topic/35438-file-write-vs-db-update/#findComment-167876 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.