|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is it acceptable to...the database? For instance i have a situation where any number of players could be playing a game. I can do this: GameTbl GameId GameName linked to a table: GameDetail GameDetailId GameId PlayerId And then have loads of rows for each player with the associated game id. But since i could have 800-8000 players per game this table could get very big very fast. So ithought why not store a file which has all the players listed in it and the db can just give me a reference to that file such as GameTbl GameId GameDetailLogLoc GameName Then i can use the GameDetailLogLoc to find the logfile and get data when i need it? Is this good methodology or am i being stupid? It would mean i cant query who played the game at any time without parsing the logfile :( But i could do this with a front end interface, or by storing the details in an xml file and parsing that Generally speaking, if you have business need of the data, especially in
order to corelate it to other data held in your data server, it is wise to store it in the data server. Depending upon hardware, SQL Server is capable of accepting thousands of inserts per second. However, there may be reasonable consideration of individual file storage -in which case I would recommend xml -if only for the flexibility it provides. Then of course storing the log file and location in the db seems appropriate. -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "Daniel" <Dani***@vestryonline.com> wrote in message news:ewdtFgDqGHA.3584@TK2MSFTNGP03.phx.gbl... > ....store some data in a logfile and a reference to the logfile location > in the database? > > For instance i have a situation where any number of players could be > playing a game. I can do this: > > GameTbl > GameId > GameName > > > linked to a table: > > GameDetail > GameDetailId > GameId > PlayerId > > And then have loads of rows for each player with the associated game id. > But since i could have 800-8000 players per game this table could get very > big very fast. So ithought why not store a file which has all the players > listed in it and the db can just give me a reference to that file such as > > GameTbl > GameId > GameDetailLogLoc > GameName > > Then i can use the GameDetailLogLoc to find the logfile and get data when > i need it? > > Is this good methodology or am i being stupid? It would mean i cant query > who played the game at any time without parsing the logfile :( But i could > do this with a front end interface, or by storing the details in an xml > file and parsing that > > Daniel (Dani***@vestryonline.com) writes:
> ....store some data in a logfile and a reference to the logfile location And then that file would grow just as fast as the table would. True, you> in the database? > .... > And then have loads of rows for each player with the associated game id. > But since i could have 800-8000 players per game this table could get > very big very fast. So ithought why not store a file which has all the > players listed in it and the db can just give me a reference to that > file such as would escape the transaction log. But working with the data would not be fun at all, not even if you make it into XML. Then again, there is an example when SQL Server does this itself. When you set up a server-side trace, the best option is to save to file, to reduce the load on the server. But a trace with many events and few filters could generate over 1000 entries per second and more than so. Is your game logging equally frequently? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks guys. Sorry Erland, obviously a new log file would be created
whenever a log hits say 80kb etc so there wouldnt be an issue of file size. Eitherway immense data growth at an exponential rate can't be avoided unfortunately :( It just seems to me that either way the data will grow very very fast. If i use separate files then there is no impact on performance everytime i search through this data rather than a huge table of data. Considering how often i would need to look at this information i think using the database as a means to find the appropriate logfile at speed is the best option? I just want to be certain i am not missing any better alternative. A log would be created at a minimum of every 2 minutes, and updated throughout those 2 minutes probably at 5sec intervals i would expect. With a lot of players you can imagine how fast it will build up. i have never had to deal with such a requirement so am interested in other peoples experience here. I have to have every piece of data recorded for legal reasons and it will all no doubt just get kept on hard disks and stored on the off chance it may be needed. With that in mind i think the logfile route is best yeah? I may need to replay a game to monitor it, so i was going to make a version of the game that is passed one of these logfiles and it then just plays it as the log will basically be a transcript of the games events. So are we agreed that a logfile is the best route with just its location stored in the db? Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9801DECEC3850Yazorman@127.0.0.1... > Daniel (Dani***@vestryonline.com) writes: >> ....store some data in a logfile and a reference to the logfile location >> in the database? >> .... >> And then have loads of rows for each player with the associated game id. >> But since i could have 800-8000 players per game this table could get >> very big very fast. So ithought why not store a file which has all the >> players listed in it and the db can just give me a reference to that >> file such as > > And then that file would grow just as fast as the table would. True, you > would escape the transaction log. But working with the data would not > be fun at all, not even if you make it into XML. > > Then again, there is an example when SQL Server does this itself. When > you set up a server-side trace, the best option is to save to file, to > reduce the load on the server. But a trace with many events and few > filters > could generate over 1000 entries per second and more than so. Is your > game logging equally frequently? > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Nope, no agreement on that. But the business decision is yours, and after
weighing the alternatives, you conclude that individual log files is the best choice, that is your decision. Every business decision has unique inputs and is itself, unique One more consideration. If you have legal requirements to maintain complete interaction history, what are the provisions to be sure, with 100% certainty (criminal penalties included), that the log files don't 'disappear', become corrupt, or are somehow 'altered'. (In a database, with proper controls, you have that certainty.) (You may have this one well covered.) It seems that you really have two separate issues. Immediate and near past access for current/recent activity. And archival access for regulatory purposes. Individual activity log files is certainly a reasonable option. Make every effort to mitigate the potential risks of file 'disruption'. With the activity level you've indicated, you are going to want a lot of power and security on the NAS/SAN that holds the log files (the LUN should span as many spindles as possible.) I would consider some form of RAID 10 equivalence to be mandatory. -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "Daniel" <Dani***@vestryonline.com> wrote in message news:eum7l5EqGHA.3280@TK2MSFTNGP03.phx.gbl... > Thanks guys. Sorry Erland, obviously a new log file would be created > whenever a log hits say 80kb etc so there wouldnt be an issue of file > size. Eitherway immense data growth at an exponential rate can't be > avoided unfortunately :( > > It just seems to me that either way the data will grow very very fast. If > i use separate files then there is no impact on performance everytime i > search through this data rather than a huge table of data. Considering how > often i would need to look at this information i think using the database > as a means to find the appropriate logfile at speed is the best option? I > just want to be certain i am not missing any better alternative. > > A log would be created at a minimum of every 2 minutes, and updated > throughout those 2 minutes probably at 5sec intervals i would expect. With > a lot of players you can imagine how fast it will build up. > > i have never had to deal with such a requirement so am interested in other > peoples experience here. > > I have to have every piece of data recorded for legal reasons and it will > all no doubt just get kept on hard disks and stored on the off chance it > may be needed. With that in mind i think the logfile route is best yeah? > > I may need to replay a game to monitor it, so i was going to make a > version of the game that is passed one of these logfiles and it then just > plays it as the log will basically be a transcript of the games events. > > So are we agreed that a logfile is the best route with just its location > stored in the db? > > > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9801DECEC3850Yazorman@127.0.0.1... >> Daniel (Dani***@vestryonline.com) writes: >>> ....store some data in a logfile and a reference to the logfile location >>> in the database? >>> .... >>> And then have loads of rows for each player with the associated game id. >>> But since i could have 800-8000 players per game this table could get >>> very big very fast. So ithought why not store a file which has all the >>> players listed in it and the db can just give me a reference to that >>> file such as >> >> And then that file would grow just as fast as the table would. True, you >> would escape the transaction log. But working with the data would not >> be fun at all, not even if you make it into XML. >> >> Then again, there is an example when SQL Server does this itself. When >> you set up a server-side trace, the best option is to save to file, to >> reduce the load on the server. But a trace with many events and few >> filters >> could generate over 1000 entries per second and more than so. Is your >> game logging equally frequently? >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Daniel (Dani***@vestryonline.com) writes:
> Thanks guys. Sorry Erland, obviously a new log file would be created But as I understand it, you are saving all those files? So you> whenever a log hits say 80kb etc so there wouldnt be an issue of file > size. Eitherway immense data growth at an exponential rate can't be > avoided unfortunately :( still get a lot of data on disk. > It just seems to me that either way the data will grow very very fast. I'm not sure if I understand this. If you have a lot of files, then> If i use separate files then there is no impact on performance everytime > i search through this data rather than a huge table of data. you have a lot to search. Keep in mind that you can index a table, so that lookups what happened between 14:00:00 and 14:00:10 on a certain day, can be a simple query. Likewise, an index on user name permits you to quickly find what a certain user has been up to. > A log would be created at a minimum of every 2 minutes, and updated Well, I don't know your application, so my imagination fails. :-) But> throughout those 2 minutes probably at 5sec intervals i would expect. > With a lot of players you can imagine how fast it will build up. if you are only writing every five seconds, then I don't think see any reason for a file. To wit, the one case I could make for a file, is that you produce log entries at such speed that writing directly to the database would incur a too heavy load. Above I mentioned indexes. I should add here, that the more indexes there are, the more power does an INSERT require. But the speed we are talking about here is really a high number, rather a million entries per second than thousand. There are also lot of variations on this theme. You could write to files, and bulk-load these files later, possibly to a secondary database. In the same vein the log table could be flushed to a secondary databases every now and then, and the log table you write to, does not have to be indexed. One thing that is worth mentioning is that in the general case, this design would break down by the fact that there are many clients, and they cannot write all to the same file. I guess that in your case that you have an application server, so there is really only one process that connects to SQL Server. One yet thing worth mention is backup. If you have it all in the database, the backup and restore is a simple thing. Keeping track on a bunch of log files + database is more work, and can easier go wrong. > So are we agreed that a logfile is the best route with just its location Nah, I don't want to agree about a system that I know almost nothing about.> stored in the db? What I'm trying to say is that, yes, there could be a case for it, but my gut feeling is that it is not the right step. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks guys this is giving me food for thought.
For legal reasons on alteration a log file would be made client end and server end. So they should always match as a precaution. As Erland said tho backing up would be far simpler if the data were in a database rather than logfile. However i still cant think of a database design that could take into account every players move as it happens. And yes there is an application server so that would do all the updating, e.g. player moves, message sent to server server logs move and tells all other clients so they can update etc etc Its also important the order of moves is kept the same. The more it think about it i just cant think of a better way that using logfiles but i agree i dont like the idea...hence why i am posting suggestions on here, its a last resort for me really, I posted this earlier which explains my actual app better. "Hey guys I have to do a database for a poker site and it requires storing everything. I noticed other places such as party poker have hand numbers and table numbers and i presume all these come from a database. However they use logfiles for the actual data of what happened on each hand. As you can imagine this gets huge very quickly. So i was trying to decide what is the best way to store this data? Do i: a) store all hand information and table state information at every point in the database or b:)Create log files such as party pokers on the server and have the database store a location to this log file. I am thinking option b and then code some form of parser for the log file to extract data i need for replaying a hand and so on. Then my table for the hand would be something like: HandId TableId TimeStart TimeEnd LogFileLoc May not even need the timestart time end as this could be in the logfile too. Of course this would mean i have lots and LOTS of hand logs, i mean ppl can conceivably play 30-40 hands in one game, and thats a lot of disk IO as it needs to store every detail at every stage. If someone 'checks' it needs to be stored so the whole hand can be played back exact. Its a real challenge and not one i have faced before, does anyone have any ideas or comments on how best to approach storing data such as this in an optimal way? " > For legal reasons on alteration a log file would be made client end and And what happens when there is a dispute and the log files don't match. > server end. So they should always match as a precaution. Which one is the 'unadulterated' one? The one on the client, or the one on the server. And can you prove that when there is a complaint and the regulators com-a-knockin'. We may know in our hearts what is 'right' or correct, but when it gets down to 'he said'/'i said', it can be very expensive to prove oneself right -hopefully. If I'm having to plan for legalities, regulators, and/or potential litigation, I will have a database with a full audit trail for alterations. This is just my parania nattering on... No 'lost' files. No file discrepancies. Everything in one compact package. > ... However i still cant think of a database design that could take into How would streaming entries into a log file be any different than streaming > account every players move as it happens. inserts into a table? You may wish to do some timing tests. Loop 10k log file open/write/close and loop the same 10k db inserts. compare total time. You may be surprised. > ... Its also important the order of moves is kept the same. First come, first served. Whether writing to a log file initiated from serveral clients, or inserting into a database from serveral clients. Sequencing is still first received, first logged -either to log file or db insert. The database can provide sequencing (timestamp datatype) with NO abilitiy to alter the sequence indicator; log file sequence can be altered with notepad. Scalibility. Log files written on the application server will not be scalable). Writing log files on a file server will expose file contention issues when scaled to multiple application servers. Database server, if designed correctly, will not care which application server is submitting the data inserts. Even under load, everything will be cached and written out to disk in proper sequence. The only rely con about using the database may the the robustness of the required hardware. The pros abound. -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "Daniel" <Dani***@vestryonline.com> wrote in message news:eyMqJ2HqGHA.2256@TK2MSFTNGP03.phx.gbl... > Thanks guys this is giving me food for thought. > > For legal reasons on alteration a log file would be made client end and > server end. So they should always match as a precaution. > > As Erland said tho backing up would be far simpler if the data were in a > database rather than logfile. However i still cant think of a database > design that could take into account every players move as it happens. And > yes there is an application server so that would do all the updating, e.g. > player moves, message sent to server server logs move and tells all other > clients so they can update etc etc Its also important the order of moves > is kept the same. > > The more it think about it i just cant think of a better way that using > logfiles but i agree i dont like the idea...hence why i am posting > suggestions on here, its a last resort for me really, I posted this > earlier which explains my actual app better. > > "Hey guys > > I have to do a database for a poker site and it requires storing > everything. > I noticed other places such as party poker have hand numbers and table > numbers and i presume all these come from a database. However they use > logfiles for the actual data of what happened on each hand. As you can > imagine this gets huge very quickly. > > So i was trying to decide what is the best way to store this data? Do i: > > a) store all hand information and table state information at every point > in > the database or > > b:)Create log files such as party pokers on the server and have the > database > store a location to this log file. > > I am thinking option b and then code some form of parser for the log file > to > extract data i need for replaying a hand and so on. > > Then my table for the hand would be something like: > > HandId > TableId > TimeStart > TimeEnd > LogFileLoc > > May not even need the timestart time end as this could be in the logfile > too. Of course this would mean i have lots and LOTS of hand logs, i mean > ppl > can conceivably play 30-40 hands in one game, and thats a lot of disk IO > as > it needs to store every detail at every stage. If someone 'checks' it > needs > to be stored so the whole hand can be played back exact. > > Its a real challenge and not one i have faced before, does anyone have any > ideas or comments on how best to approach storing data such as this in an > optimal way? " > > Daniel wrote:
> In that case get someone else to do the database design. It isn't that> As Erland said tho backing up would be far simpler if the data were in a > database rather than logfile. However i still cant think of a database > design that could take into account every players move as it happens. difficult, in fact many people reading this have probably built similar audit trails. What I don't understand is why you think this would be easier outside the database where you have to build your own file and transaction management. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Daniel (Dani***@vestryonline.com) writes:
> As Erland said tho backing up would be far simpler if the data were in a I assumed that your game was some multi-user-dungeon type of game, so> database rather than logfile. However i still cant think of a database > design that could take into account every players move as it happens. when I read "every move" I thought OK, a file may be correct. After all, if you need to track every move from half-analogue device like a game stick, then there will be a lot of data. But then you tell us that the game is poker. Which turns the table entirely. I completely fail to see that a couple of poker players would generate events that fast that you would not be able to log them. Au contraire, using files in this case, appears to be a really bad choice. Using files, means that you take a gamble, and are prepared lose them. But with a game like poker, there is enough gamble in the game to rule out that option. I would go as far as saying that you *must* write the log to the database. I entirely agree with the points raised by Arnie and David, so I will not repeat those. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi
I agree with you guys rgearding the amount of data. But now lets take 10 players, on 1 table. each player moves say every 5 seconds or so. Now take 100 tables and the data inserts just increased substantially. I estimated it would take 23 days to hit 1million rows on my database table at the rate of inserts i could easily expect.. The table is going to get huge very very fast which was my biggest concern. I have made my design to use the database to log it all and i will just have to archive the data every month or so i guess depending on the speed it builds up. Thanks for all the comments, even the condescending ones. It has helped to bounce my views of others, thanks for your time guys. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9802EE76444C9Yazorman@127.0.0.1... > Daniel (Dani***@vestryonline.com) writes: >> As Erland said tho backing up would be far simpler if the data were in a >> database rather than logfile. However i still cant think of a database >> design that could take into account every players move as it happens. > > I assumed that your game was some multi-user-dungeon type of game, so > when I read "every move" I thought OK, a file may be correct. After all, > if you need to track every move from half-analogue device like a game > stick, then there will be a lot of data. > > But then you tell us that the game is poker. Which turns the table > entirely. I completely fail to see that a couple of poker players would > generate events that fast that you would not be able to log them. Au > contraire, using files in this case, appears to be a really bad choice. > Using files, means that you take a gamble, and are prepared lose them. But > with a game like poker, there is enough gamble in the game to rule out > that option. I would go as far as saying that you *must* write the log to > the database. > > I entirely agree with the points raised by Arnie and David, so I will > not repeat those. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I'm glad we could be of help in your process of evaluating and weighing the
options. Decisions like these should never be done lightly. I only wish more of my clients would expose their thinking to this kind of rigor. Their projects would be the stronger as a result. Good luck. p.s., I would never play on a table with 10 players! (Too many cards exposed...) -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "Daniel" <Dani***@vestryonline.com> wrote in message news:ul3cwGTqGHA.4960@TK2MSFTNGP04.phx.gbl... > Hi > > I agree with you guys rgearding the amount of data. But now lets take 10 > players, on 1 table. each player moves say every 5 seconds or so. Now take > 100 tables and the data inserts just increased substantially. > > I estimated it would take 23 days to hit 1million rows on my database > table at the rate of inserts i could easily expect.. > > The table is going to get huge very very fast which was my biggest > concern. > > I have made my design to use the database to log it all and i will just > have to archive the data every month or so i guess depending on the speed > it builds up. > > Thanks for all the comments, even the condescending ones. It has helped to > bounce my views of others, thanks for your time guys. > > > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9802EE76444C9Yazorman@127.0.0.1... >> Daniel (Dani***@vestryonline.com) writes: >>> As Erland said tho backing up would be far simpler if the data were in a >>> database rather than logfile. However i still cant think of a database >>> design that could take into account every players move as it happens. >> >> I assumed that your game was some multi-user-dungeon type of game, so >> when I read "every move" I thought OK, a file may be correct. After all, >> if you need to track every move from half-analogue device like a game >> stick, then there will be a lot of data. >> >> But then you tell us that the game is poker. Which turns the table >> entirely. I completely fail to see that a couple of poker players would >> generate events that fast that you would not be able to log them. Au >> contraire, using files in this case, appears to be a really bad choice. >> Using files, means that you take a gamble, and are prepared lose them. >> But >> with a game like poker, there is enough gamble in the game to rule out >> that option. I would go as far as saying that you *must* write the log to >> the database. >> >> I entirely agree with the points raised by Arnie and David, so I will >> not repeat those. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Daniel (Dani***@vestryonline.com) writes:
> I agree with you guys rgearding the amount of data. But now lets take 10 No, adding one million rows in 23 days does not qualify as "huge very fast".> players, on 1 table. each player moves say every 5 seconds or so. Now take > 100 tables and the data inserts just increased substantially. > > I estimated it would take 23 days to hit 1million rows on my database > table at the rate of inserts i could easily expect.. > > The table is going to get huge very very fast which was my biggest > concern. You will get 15 million rows in a year - and that is not a huge table. At least not if the log entries are moderately sized. (And for a poker game, I fail to see that you more than, say, 50 bytes per entry.) As long as you own the system, do as you I like. If you are working for someone else, you are irresponsible if you carry out the design with a log file. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics