Re: [GENERAL] Queues Problem
2010/6/8 Oliver Kohll - Mailing Lists oliver.li...@gtwm.co.uk On 8 Jun 2010, at 20:12, uaca man uaca...@gmail.com wrote: 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. [snip] That is exactly what we are doing for the most part and was our first bet with the buildings, however since building can affect pretty much anything, anywhere on the game changing states as the user interacts is getting beyond comprehension of a human mind(al least for my mind) Might a rules engine be useful? http://en.wikipedia.org/wiki/Business_rule_management_system Drools is one example. Regards Oliver Kohll oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll www.agilebase.co.uk Oh Yeah! A BRMS might be just what I’m look for! I did not know that such thing existed, but, there is always a but!! I don’t think we have the buget to use one, will investigate further. Thanks
[GENERAL] Queues Problem
Hello my fellow postgreSQL gurus. I´m a user of postgresSQL of quite some time now, but most of my experience is consuming database, and for the current project we are without a proper DBA and they have to bear with me and so I must seek advice. I have a list of building and a queue and the user can start the construction of one or more building that will take some time to build. The problems lies with the fact this is a web browser game and the user can log in, star the construction and log off, to further aggravate the problem another user may take a look at the buildings, to add a little bit more, when a building is done it may have effect on the user population, gold and whatever the “imagination team” can come up with. Bottom line is: the construction has to be “concluded” with second’s precision. Lets say for a 20 thousand users server, it may have at most 20 thousand constructions started at the same time. To accomplish such behavior so far I could come up with two options: 1. Make a never ending function that will look at the BuildingQueue table every second and finish the construction. 2. Every time the user start a construction add a cron job for that construction to run 1 seconds after the construction is finished and call a function the will finish. For some reason I can not believe that a never ending function is a good idea and I don’t think cron jobs are meant to have 20 thousand jobs. Anyone care to share? Tables: Create table Building ( idBuilding Serial NOT NULL, description Varchar(200), time Integer, primary key (idBuilding) ) Without Oids; Create table BuildingQueue ( idBuilding Integer NOT NULL, start Timestamp, end Timestamp, primary key (idBuilding) ) Without Oids; Alter table BuildingQueue add foreign key (idBuilding) references Building (idBuilding) on update restrict on delete restrict;
Re: [GENERAL] Queues Problem
On Tue, Jun 8, 2010 at 12:53 PM, uaca man uaca...@gmail.com wrote: Lets say for a 20 thousand users server, it may have at most 20 thousand constructions started at the same time. To accomplish such behavior so far I could come up with two options: 1. Make a never ending function that will look at the BuildingQueue table every second and finish the construction. 2. Every time the user start a construction add a cron job for that construction to run 1 seconds after the construction is finished and call a function the will finish. You should investigate a proper queueing or job scheduling solution, such as RabbitMQ or Qpid or gearman. They are designed for this type of requirement. You will have to write your code to be more event driven, and make the web server just generate requests and view the results where they are stored. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queues Problem
On 6/8/2010 11:53 AM, uaca man wrote: Hello my fellow postgreSQL gurus. I´m a user of postgresSQL of quite some time now, but most of my experience is consuming database, and for the current project we are without a proper DBA and they have to bear with me and so I must seek advice. I have a list of building and a queue and the user can start the construction of one or more building that will take some time to build. The problems lies with the fact this is a web browser game and the user can log in, star the construction and log off, to further aggravate the problem another user may take a look at the buildings, to add a little bit more, when a building is done it may have effect on the user population, gold and whatever the “imagination team” can come up with. Bottom line is: the construction has to be “concluded” with second’s precision. Lets say for a 20 thousand users server, it may have at most 20 thousand constructions started at the same time. To accomplish such behavior so far I could come up with two options: 1. Make a never ending function that will look at the BuildingQueue table every second and finish the construction. 2. Every time the user start a construction add a cron job for that construction to run 1 seconds after the construction is finished and call a function the will finish. For some reason I can not believe that a never ending function is a good idea and I don’t think cron jobs are meant to have 20 thousand jobs. Anyone care to share? Tables: Create table Building ( idBuilding Serial NOT NULL, description Varchar(200), time Integer, primary key (idBuilding) ) Without Oids; Create table BuildingQueue ( idBuilding Integer NOT NULL, start Timestamp, end Timestamp, primary key (idBuilding) ) Without Oids; Alter table BuildingQueue add foreign key (idBuilding) references Building (idBuilding) on update restrict on delete restrict; How about you figure out when it should be finished, and if now() is after should be then mark the building as completed with a finish time of should be. Then you can run it whenever (every hour, on login, etc), and it'll catch up, and mark buildings as complete with the appropriate time. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Queues Problem
Hello my fellow postgreSQL gurus. I´m a user of postgresSQL of quite some time now, but most of my experience is consuming database, and for the current project we are without a proper DBA and they have to bear with me and so I must seek advice. I have a list of building and a queue and the user can start the construction of one or more building that will take some time to build. The problems lies with the fact this is a web browser game and the user can log in, star the construction and log off, to further aggravate the problem another user may take a look at the buildings, to add a little bit more, when a building is done it may have effect on the user population, gold and whatever the “imagination team” can come up with. Bottom line is: the construction has to be “concluded” with second’s precision. Lets say for a 20 thousand users server, it may have at most 20 thousand constructions started at the same time. To accomplish such behavior so far I could come up with two options: 1. Make a never ending function that will look at the BuildingQueue table every second and finish the construction. 2. Every time the user start a construction add a cron job for that construction to run 1 seconds after the construction is finished and call a function the will finish. For some reason I can not believe that a never ending function is a good idea and I don’t think cron jobs are meant to have 20 thousand jobs. Anyone care to share? Tables: Create table Building ( idBuilding Serial NOT NULL, description Varchar(200), time Integer, primary key (idBuilding) ) Without Oids; Create table BuildingQueue ( idBuilding Integer NOT NULL, start Timestamp, end Timestamp, primary key (idBuilding) ) Without Oids; Alter table BuildingQueue add foreign key (idBuilding) references Building (idBuilding) on update restrict on delete restrict;
Re: [GENERAL] Queues Problem
This would work except for one thing, the building may affect another buildings, Consider this: the user starts one construction that will finish in 10 minutes and the building will give a bonus of +5 gold each seconds for the user. This has to be available in the seconds that the build is done and not one hour late because the user will lose the bonus for one hour. 2010/6/8 Andy Colson a...@squeakycode.net On 6/8/2010 11:53 AM, uaca man wrote: Hello my fellow postgreSQL gurus. I´m a user of postgresSQL of quite some time now, but most of my experience is consuming database, and for the current project we are without a proper DBA and they have to bear with me and so I must seek advice. I have a list of building and a queue and the user can start the construction of one or more building that will take some time to build. The problems lies with the fact this is a web browser game and the user can log in, star the construction and log off, to further aggravate the problem another user may take a look at the buildings, to add a little bit more, when a building is done it may have effect on the user population, gold and whatever the “imagination team” can come up with. Bottom line is: the construction has to be “concluded” with second’s precision. Lets say for a 20 thousand users server, it may have at most 20 thousand constructions started at the same time. To accomplish such behavior so far I could come up with two options: 1. Make a never ending function that will look at the BuildingQueue table every second and finish the construction. 2. Every time the user start a construction add a cron job for that construction to run 1 seconds after the construction is finished and call a function the will finish. For some reason I can not believe that a never ending function is a good idea and I don’t think cron jobs are meant to have 20 thousand jobs. Anyone care to share? Tables: Create table Building ( idBuilding Serial NOT NULL, description Varchar(200), time Integer, primary key (idBuilding) ) Without Oids; Create table BuildingQueue ( idBuilding Integer NOT NULL, start Timestamp, end Timestamp, primary key (idBuilding) ) Without Oids; Alter table BuildingQueue add foreign key (idBuilding) references Building (idBuilding) on update restrict on delete restrict; How about you figure out when it should be finished, and if now() is after should be then mark the building as completed with a finish time of should be. Then you can run it whenever (every hour, on login, etc), and it'll catch up, and mark buildings as complete with the appropriate time. -Andy
Re: [GENERAL] Queues Problem
You should investigate a proper queueing or job scheduling solution, such as RabbitMQ or Qpid or gearman. They are designed for this type of requirement. All of those(RabbitMQ , Qpid and gearman) are messages queue and are used to exchange message between different process, system, applications or whatever you like, much like IBM MQSeries Plataform. I don’t see how this is related to problem. Did is miss something? You will have to write your code to be more event driven, and make the web server just generate requests and view the results where they are stored. What do you mean? That is what I think I am trying to do. No? 2010/6/8 Vick Khera vi...@khera.org On Tue, Jun 8, 2010 at 12:53 PM, uaca man uaca...@gmail.com wrote: Lets say for a 20 thousand users server, it may have at most 20 thousand constructions started at the same time. To accomplish such behavior so far I could come up with two options: 1. Make a never ending function that will look at the BuildingQueue table every second and finish the construction. 2. Every time the user start a construction add a cron job for that construction to run 1 seconds after the construction is finished and call a function the will finish. You should investigate a proper queueing or job scheduling solution, such as RabbitMQ or Qpid or gearman. They are designed for this type of requirement. You will have to write your code to be more event driven, and make the web server just generate requests and view the results where they are stored. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queues Problem
On Tue, Jun 8, 2010 at 1:00 PM, uaca man uaca...@gmail.com wrote: This would work except for one thing, the building may affect another buildings, Consider this: the user starts one construction that will finish in 10 minutes and the building will give a bonus of +5 gold each seconds for the user. This has to be available in the seconds that the build is done and not one hour late because the user will lose the bonus for one hour. Sounds like you need to take a big step back and figure out what overall systems architecture makes sense for a real time gaming platform Couple of suggestions: 1) If you need real time events you do them where you need them: on the front end, where the user interaction is happening. Do not attempt to code gaming rules and logic into database triggers and back end relationships (though you might store gaming rules etc. in a table). 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. For example, it is unlikely the user needs the amount of gold updated every 5 seconds. Rather, they need to know how much they have on hand when they go to use it. At that point, you query for the old balance, find the last updated time, how many buildings have been completed since then and for how long and figure out what the new gold balance is. Update the new balance at that point (with a timestamp), and the front end goes on it's merry way... -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queues Problem
2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. For example, it is unlikely the user needs the amount of gold updated every 5 seconds. Rather, they need to know how much they have on hand when they go to use it. At that point, you query for the old balance, find the last updated time, how many buildings have been completed since then and for how long and figure out what the new gold balance is. Update the new balance at that point (with a timestamp), and the front end goes on it's merry way... That is exactly what we are doing for the most part and was our first bet with the buildings, however since building can affect pretty much anything, anywhere on the game changing states as the user interacts is getting beyond comprehension of a human mind(al least for my mind) and that was when I had the super idea, lest put the queue on the crontab! Looks like we are going to cut off a few options of the game. ps: do i top post or bottom post here? 2010/6/8 Peter Hunsberger peter.hunsber...@gmail.com On Tue, Jun 8, 2010 at 1:00 PM, uaca man uaca...@gmail.com wrote: This would work except for one thing, the building may affect another buildings, Consider this: the user starts one construction that will finish in 10 minutes and the building will give a bonus of +5 gold each seconds for the user. This has to be available in the seconds that the build is done and not one hour late because the user will lose the bonus for one hour. Sounds like you need to take a big step back and figure out what overall systems architecture makes sense for a real time gaming platform Couple of suggestions: 1) If you need real time events you do them where you need them: on the front end, where the user interaction is happening. Do not attempt to code gaming rules and logic into database triggers and back end relationships (though you might store gaming rules etc. in a table). 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. For example, it is unlikely the user needs the amount of gold updated every 5 seconds. Rather, they need to know how much they have on hand when they go to use it. At that point, you query for the old balance, find the last updated time, how many buildings have been completed since then and for how long and figure out what the new gold balance is. Update the new balance at that point (with a timestamp), and the front end goes on it's merry way... -- Peter Hunsberger
Re: [GENERAL] Queues Problem
On 6/8/2010 1:26 PM, uaca man wrote: 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. For example, it is unlikely the user needs the amount of gold updated every 5 seconds. Rather, they need to know how much they have on hand when they go to use it. At that point, you query for the old balance, find the last updated time, how many buildings have been completed since then and for how long and figure out what the new gold balance is. Update the new balance at that point (with a timestamp), and the front end goes on it's merry way... That is exactly what we are doing for the most part and was our first bet with the buildings, however since building can affect pretty much anything, anywhere on the game changing states as the user interacts is getting beyond comprehension of a human mind(al least for my mind) and that was when I had the super idea, lest put the queue on the crontab! Looks like we are going to cut off a few options of the game. ps: do i top post or bottom post here? 2010/6/8 Peter Hunsberger peter.hunsber...@gmail.com mailto:peter.hunsber...@gmail.com On Tue, Jun 8, 2010 at 1:00 PM, uaca man uaca...@gmail.com mailto:uaca...@gmail.com wrote: This would work except for one thing, the building may affect another buildings, Consider this: the user starts one construction that will finish in 10 minutes and the building will give a bonus of +5 gold each seconds for the user. This has to be available in the seconds that the build is done and not one hour late because the user will lose the bonus for one hour. Sounds like you need to take a big step back and figure out what overall systems architecture makes sense for a real time gaming platform Couple of suggestions: 1) If you need real time events you do them where you need them: on the front end, where the user interaction is happening. Do not attempt to code gaming rules and logic into database triggers and back end relationships (though you might store gaming rules etc. in a table). 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. For example, it is unlikely the user needs the amount of gold updated every 5 seconds. Rather, they need to know how much they have on hand when they go to use it. At that point, you query for the old balance, find the last updated time, how many buildings have been completed since then and for how long and figure out what the new gold balance is. Update the new balance at that point (with a timestamp), and the front end goes on it's merry way... -- Peter Hunsberger bottom. This is a much harder question than I'd first thought. If you go with the program that runs every second, I see one problem: what if the update takes more than a second to do? I have an image of a spreadsheet in my mind, where there is a huge interdependence on the cells. Have no idea what I'm talking about here, but how about some kind of state machine? Then you can roll the state forward (or backward). If the formula had some kind of time element then the cells could be recalculated for any given point in time. ahh, well, in any case, I really dont know. Sounds like an interesting problem though. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queues Problem
On Tue, Jun 8, 2010 at 1:26 PM, uaca man uaca...@gmail.com wrote: 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. For example, it is unlikely the user needs the amount of gold updated every 5 seconds. Rather, they need to know how much they have on hand when they go to use it. At that point, you query for the old balance, find the last updated time, how many buildings have been completed since then and for how long and figure out what the new gold balance is. Update the new balance at that point (with a timestamp), and the front end goes on it's merry way... That is exactly what we are doing for the most part and was our first bet with the buildings, however since building can affect pretty much anything, anywhere on the game changing states as the user interacts is getting beyond comprehension of a human mind(al least for my mind) and that was when I had the super idea, lest put the queue on the crontab! Then each thing the building interacts with has it's own unique set of states. The only ones you need worry about are the ones a _user_ is actually interacting with at any given point. Looks like we are going to cut off a few options of the game. ps: do i top post or bottom post here? Bottom post. -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queues Problem
2010/6/8 Peter Hunsberger peter.hunsber...@gmail.com On Tue, Jun 8, 2010 at 1:26 PM, uaca man uaca...@gmail.com wrote: 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. For example, it is unlikely the user needs the amount of gold updated every 5 seconds. Rather, they need to know how much they have on hand when they go to use it. At that point, you query for the old balance, find the last updated time, how many buildings have been completed since then and for how long and figure out what the new gold balance is. Update the new balance at that point (with a timestamp), and the front end goes on it's merry way... That is exactly what we are doing for the most part and was our first bet with the buildings, however since building can affect pretty much anything, anywhere on the game changing states as the user interacts is getting beyond comprehension of a human mind(al least for my mind) and that was when I had the super idea, lest put the queue on the crontab! Then each thing the building interacts with has it's own unique set of states. The only ones you need worry about are the ones a _user_ is actually interacting with at any given point. Looks like we are going to cut off a few options of the game. ps: do i top post or bottom post here? I guess that there is no easy solution, will try the first bet and calculate the past every user request. -- Peter Hunsberger
Re: [GENERAL] Queues Problem
On Tue, Jun 8, 2010 at 2:01 PM, uaca man uaca...@gmail.com wrote: You will have to write your code to be more event driven, and make the web server just generate requests and view the results where they are stored. What do you mean? That is what I think I am trying to do. No? You have work you need done. You want to send that work to some worker (a cron job or never-ending loop in your description) to do the work and get back an answer either directly or posted to some place (the database). Seems to me like gearman and or AMQP would be ideal solutions, and you can then have as many workers as you want and not worry about contention of querying for the next job in the table which inherently requires some form of locking. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
