Regarding system IDs... you couldn't give each system it's own system ID, otherwise they would function as distinct systems (each system wouldn't be able to act on tickets generated by the other etc.). Shared systems would have to share the same system ID. And OTRS sessions can be stored either in the DB (by default) or the filesystem, so not sure why that would be an issue? With the "does last DB write always win", if the write was committed then yes, it's committed to the database, I don't really understand what your question is, that is how a database works? whether or not the last commit was successfully synchronised to other database servers will entirely depend on your database clustering configuration. I don't see why you couldn't have multiple front end "web" servers and a clustered database in the backend, and I'm not aware of any hard and fast rules that dictate you can't store articles in the DB once you reach a certain size, yes you are possibly going to run into some performance issues (maybe, depends on how tuned your DB is) and it will be quite a chunk of data to backup. Generally for the front end you will need at least 4 servers (2 load balancers sharing an additional virtual IP and the 2 web servers you want to load balance) queries hit the virtual IP and the LB node proxies the connection to one of the backend web servers. Steve On 29 January 2013 16:58, Bogdan Iosif wrote:

Hi,

While I appreciate the general advice, please note I'm not trying to reinvent anything. Instead, I want to prepare for natural problems that OTRS will run into when reaching a size that requires load balancing.

For example, articles can't be stored in the database for installations where articles size exceeds a couple of gb. In my case, the initial OTRS installation has 25 GB of attachments. Storing those in the db would blow query performance and lead me to having daily backups of unmanageable size. So shared storage is a must. The problem is what needs to be shared and what needs to *never* be shared.

I don't know about the code in Linux-HA but the load balancers I worked with require some minimal support from applications behind them when the app requires sticky app sessions (not http sessions, OTRS keeps a user session alive across multiple http sessions).

Do you have load balanced OTRS installation?

/bogdan

On Tue, Jan 29, 2013 at 6:34 PM, David Boyes wrote:

...

Thoughts: ****

** **

Rather than invent a application-specific solution, look at Linux-HA ( www.linux-ha.org). They’ve solved most of these problems in a neatly packaged way. ****

There’s existing code to handle session affinity and most of the request distribution process. ****

** **

If you store everything in the database (including attachments), you can easily separate the application logic from the database server; that introduces a bit more database management, but it easily allows multiple otrs instances to use the same data safely. It also lets you take advantage of the clustering features in the dbms software. You also eliminate any need for shared storage. ****

** **

If you use shared storage, you MUST use a cluster-aware filesystem like GFS2 or OCFS. NFS won’t work reliably. ****

** **

*From:* otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] *On Behalf Of *Bogdan Iosif *Sent:* Tuesday, January 29, 2013 5:21 AM *To:* OTRS User Mailing List *Subject:* [otrs] NLB (load balancing) OTRS****

** **

Hi,****

Can anyone help with some obvious issues around setting up a load balanced OTRS?****

- Does last db write always win? I imagine there's no built in protection against it.****

- Are HTTP sticky sessions required and if so, how can they be configured? I imagine OTRS needs some built in support to allow identification of user sessions in the balancer so that it maps them on the same app server. ****

** **

- To what extent is shared storage required? ****

An older mailing list message proposes sharing the whole /var dir through a storage that support file locks (mainly to safely use TicketCounter.log but this could be worked around by setting up different SystemIDs (via SysConfig) on each app server). While sharing storage is required for /var/article (when attachments are stored on disk) I don't know if it's required or even safe for the other subfolders in /var (especially /tmp).****

Thanks,****

Bogdan****

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

On 29 January 2013 19:29, Bogdan Iosif wrote:

bogdan: Thanks for this info. I was under the impression the SystemID is used by OTRS only when deciding which emails to process from an assigned inbox via PostMaster

It is, but that would mean it wouldn't be able to process incoming emails from the other OTRS system, the system ID gets embedded in the full ticket "number", the system ID is primarily used to allow separate OTRS systems to send emails back and forth (assuming the system IDs are different) without screwing up other tickets already in your database.

bogdan: Let me just clarify what I mean. I assumed OTRS *requires* "sticky user sessions" (aka all http request/responses from a given user session need to pass through the same app server). This is required by web apps that hold user session data in memory or on disk between http request/responses. I assumed OTRS requires "sticky user sessions" because I saw a lot of generated files in /var subdirs.

Can I conclude OTRS actually stores *all* user session specific data in the database, between response/requests? My impression was that it stores just a list of sessions so that an admin can see them in the admin UI.

That is what I have always assumed, if you look in the sessions table in the DB in the session_value column you can see that there is all of the user's configuration in one big string.

bogdan: I'll explain what I meant. Many apps supporting heavy concurrent usage implement entity versioning. This means that when the app reads a document from the db (such as a ticket) it also reads its version. The user changes the entity in the web UI and when the user saves the change the app first checks the version of the entity to ensure it is still the same as when it was initially read. If the versions differ, the app either throws an error or does something more elegant to reconcile the differences. What's important is that in this way the app protects against silently losing changes. I was aiming to find if OTRS does entity versioning when I asked "Does last db write always win?" and I mainly asked because I saw many tables saving a change time for each row. The change time could be used for entity versioning.

OTRS isn't that "clever" and certainly doesn't do versioning. Many tables are updated when the data is added to a ticket as it's not stored in one big flat table.

bogdan: I think it's self obvious it's not practical to store attachments in the db when you obtain a db that's 90% attachments and 10% everything else. I would like to store this db on fast storage and it's hard to obtain budget for high capacity ssds when most of the data on them is dead weight. Backup management would become a pain, some queries would be heavily impacted and all db operations (moving to qa/dev envs, etc) would be unreasonably slowed down by such a large db. OTRS's official docs also acknowledge this practical matter.

Yep, but like I said there is nothing stopping you from doing it, but it does have it's drawbacks. Like David said if you are going to store this on disk then you will need to use a cluster filesystem, and I'm not entirely sure how this will work with multiple front end servers, hence why storing it in the DB (even given its "issues") would be the easier option as the frontend doesn't need to worry about being able to access the filesystem, it's all in the one place. It might be worth contacting OTRS support directly to see if they do have load balanced implementations and whether they would share any of the implementation details (can't help but to ask), but if you are going to have such a large installation anyway then it may be worthwhile taking out official support should you run into any issues with the implementation, at least then you are guaranteed help on the end of a phone. Steve

| That is what I have always assumed, if you look in the sessions table in the DB in the session_value column you can see that there is all of the user's configuration in one big string. bogdan: Efficient and simple. I'm surprised. I assumed OTRS stores some user session data in memory/disk to process successive requests faster. | OTRS isn't that "clever" and certainly doesn't do versioning. Many tables are updated when the data is added to a ticket as it's not stored in one big flat table. bogdan: I imagined that when there's only one app server node some magic would prevent these race conditions and the magic would go away in a multi node config. Good to know there's no magic :). | Yep, but like I said there is nothing stopping you from doing it, but it does have it's drawbacks. Like David said if you are going to store this on disk then you will need to use a cluster filesystem, and I'm not entirely sure how this will work with multiple front end servers, hence why storing it in the DB (even given its "issues") would be the easier option as the frontend doesn't need to worry about being able to access the filesystem, it's all in the one place. bogdan: I think if the shared file system would be required just for attachments, it wouldn't need to be very fancy. I think OTRS doesn't modify what it has already written on disk for articles / attachments. So it only does reads and additional writes. No editing. | It might be worth contacting OTRS support directly to see if they do have load balanced implementations and whether they would share any of the implementation details (can't help but to ask), but if you are going to have such a large installation anyway then it may be worthwhile taking out official support should you run into any issues with the implementation, at least then you are guaranteed help on the end of a phone. bogdan: I'm certain they have. Right now, my installation is not monstrous. It will handle ~30 agents and 100 daily tickets. For a while, I hope I'll be able to keep things in check with a single app server node. The bigger problem is that I have to get it off the ground with a db injected with 75000 tickets, 200000 articles and 1000 customer accounts from our custom legacy system. But I also want to be prepared for "unexpected success" and that's why I ask about load balancing. If that comes to be, maybe I'll have the budget for official support. Thanks, bogdan Steve

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

On topic of big databases: databases store data. Binary data is still data. I do understand that large databases means large backups and thus long backup time but if you have the data on disk you also have the data and you need to back it up as well. So to some extent it is: your database is large, so this is a problem. You extract the attachments to the file system. Now you have two problems... So it doesn't really matter all that much where you keep it. With all DBMSses you can move certain tables to other data locations, on other, slower partitions if you want. Also for synchronizing data you can decide to not synchronize the couple of tables containing attachments. But of course you could use a file system as well. Then there is the topic of the ticket number generator. This currently uses a file lock to make sure it does not generate duplicates. I implemented for a custom project a generate that uses the database instead of file locking. Also, there is caching. The fist time OTRS builds a service list, or any other list, this comes from an SQL query to the database. Then this is stored on a disk cache to offload the database. The next time this list is needed, OTRS checks the disk first and uses this result. Caching is done on the application server side. If someone adds a service, or in general updates a record, the cache is expired. Now if you cache on disk, cache on your app servers will go out of sync. We have developed a memcached back end for these kind of scenarios, it will provide a distributed cache for use by multiple frontend servers. You can get it as a Feature Add On for subscription customers. About sessions, you should use sticky sessions and choose a time bigger than the OTRS max session time for this. There are some more topics to OTRS on multiple frontend servers. We are available for help, if needed! -- Mike Thanks for the input. I placed my replies inline. Have a look please. On Tue, Jan 29, 2013 at 7:29 PM, Steven Carr wrote:

Regarding system IDs... you couldn't give each system it's own system ID, otherwise they would function as distinct systems (each system wouldn't be able to act on tickets generated by the other etc.). Shared systems would have to share the same system ID.

bogdan: Thanks for this info. I was under the impression the SystemID is used by OTRS only when deciding which emails to process from an assigned inbox via PostMaster

And OTRS sessions can be stored either in the DB (by default) or the filesystem, so not sure why that would be an issue?

bogdan: Let me just clarify what I mean. I assumed OTRS *requires* "sticky user sessions" (aka all http request/responses from a given user session need to pass through the same app server). This is required by web apps that hold user session data in memory or on disk between http request/responses. I assumed OTRS requires "sticky user sessions" because I saw a lot of generated files in /var subdirs. Can I conclude OTRS actually stores *all* user session specific data in the database, between response/requests? My impression was that it stores just a list of sessions so that an admin can see them in the admin UI. With the "does last DB write always win", if the write was committed then

yes, it's committed to the database, I don't really understand what your question is, that is how a database works? whether or not the last commit was successfully synchronised to other database servers will entirely depend on your database clustering configuration.

bogdan: I'll explain what I meant. Many apps supporting heavy concurrent usage implement entity versioning. This means that when the app reads a document from the db (such as a ticket) it also reads its version. The user changes the entity in the web UI and when the user saves the change the app first checks the version of the entity to ensure it is still the same as when it was initially read. If the versions differ, the app either throws an error or does something more elegant to reconcile the differences. What's important is that in this way the app protects against silently losing changes. I was aiming to find if OTRS does entity versioning when I asked "Does last db write always win?" and I mainly asked because I saw many tables saving a change time for each row. The change time could be used for entity versioning.

I don't see why you couldn't have multiple front end "web" servers and a clustered database in the backend, and I'm not aware of any hard and fast rules that dictate you can't store articles in the DB once you reach a certain size, yes you are possibly going to run into some performance issues (maybe, depends on how tuned your DB is) and it will be quite a chunk of data to backup. Generally for the front end you will need at least 4 servers (2 load balancers sharing an additional virtual IP and the 2 web servers you want to load balance) queries hit the virtual IP and the LB node proxies the connection to one of the backend web servers.

bogdan: I think it's self obvious it's not practical to store attachments in the db when you obtain a db that's 90% attachments and 10% everything else. I would like to store this db on fast storage and it's hard to obtain budget for high capacity ssds when most of the data on them is dead weight. Backup management would become a pain, some queries would be heavily impacted and all db operations (moving to qa/dev envs, etc) would be unreasonably slowed down by such a large db. OTRS's official docs also acknowledge this practical matter.

Steve

On 29 January 2013 16:58, Bogdan Iosif wrote:

...

Hi,

While I appreciate the general advice, please note I'm not trying to reinvent anything. Instead, I want to prepare for natural problems that OTRS will run into when reaching a size that requires load balancing.

For example, articles can't be stored in the database for installations where articles size exceeds a couple of gb. In my case, the initial OTRS installation has 25 GB of attachments. Storing those in the db would blow query performance and lead me to having daily backups of unmanageable size. So shared storage is a must. The problem is what needs to be shared and what needs to *never* be shared.

I don't know about the code in Linux-HA but the load balancers I worked with require some minimal support from applications behind them when the app requires sticky app sessions (not http sessions, OTRS keeps a user session alive across multiple http sessions).

Do you have load balanced OTRS installation?

/bogdan

On Tue, Jan 29, 2013 at 6:34 PM, David Boyes wrote:

...

Thoughts: ****

** **

Rather than invent a application-specific solution, look at Linux-HA ( www.linux-ha.org). They’ve solved most of these problems in a neatly packaged way. ****

There’s existing code to handle session affinity and most of the request distribution process. ****

** **

If you store everything in the database (including attachments), you can easily separate the application logic from the database server; that introduces a bit more database management, but it easily allows multiple otrs instances to use the same data safely. It also lets you take advantage of the clustering features in the dbms software. You also eliminate any need for shared storage. ****

** **

If you use shared storage, you MUST use a cluster-aware filesystem like GFS2 or OCFS. NFS won’t work reliably. ****

** **

*From:* otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] *On Behalf Of *Bogdan Iosif *Sent:* Tuesday, January 29, 2013 5:21 AM *To:* OTRS User Mailing List *Subject:* [otrs] NLB (load balancing) OTRS****

** **

Hi,****

Can anyone help with some obvious issues around setting up a load balanced OTRS?****

- Does last db write always win? I imagine there's no built in protection against it.****

- Are HTTP sticky sessions required and if so, how can they be configured? I imagine OTRS needs some built in support to allow identification of user sessions in the balancer so that it maps them on the same app server. ****

** **

- To what extent is shared storage required? ****

An older mailing list message proposes sharing the whole /var dir through a storage that support file locks (mainly to safely use TicketCounter.log but this could be worked around by setting up different SystemIDs (via SysConfig) on each app server). While sharing storage is required for /var/article (when attachments are stored on disk) I don't know if it's required or even safe for the other subfolders in /var (especially /tmp).****

Thanks,****

Bogdan****

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

While I appreciate the general advice, please note I'm not trying to reinvent anything. Instead, I want to prepare for natural problems that OTRS will run into when reaching a size that requires load balancing. OK, I was jotting down some thoughts – sorry if it came off wrong. More details inline. We have a two app node, 3 DB server system supporting one of our labs. The OTRS application runs on the app nodes, MySQL 5.1 on the DB servers in cluster mode. The app nodes are behind a set of servers that provide a general load distribution tool for HTTP/HTTPS traffic, running Linux Virtual Server (LVS), roughly equivalent to the old Cisco content switching devices. We use the same system id on both nodes. For example, articles can't be stored in the database for installations where articles size exceeds a couple of gb. In my case, the initial OTRS installation has 25 GB of attachments. Storing those in the db would blow query performance and lead me to having daily backups of unmanageable size. So shared storage is a must. The problem is what needs to be shared and what needs to *never* be shared. As I said, there’s a bit more database maintenance involved. Our DBA runs a script periodically that vacuums the attachments out of the database, stores them in our document management system, and replaces the entry in OTRS with a URL reference to the archived copy in the DMS. We find that keeps the DB size manageable (and keeps clear custodial records of the attachment data, along with providing a globally unique identifier for each attachment) while not requiring shared storage which is a gigantic PITA. Probably not a universal solution, but it leverages a lot of other stuff in terms of data management and storage management (the DMS has hierarchical storage management and automatic recall capabilities, as well as much more robust data management classifications that we can rely on to make sure we keep data as long as it’s required, but no longer than that). I don't know about the code in Linux-HA but the load balancers I worked with require some minimal support from applications behind them when the app requires sticky app sessions (not http sessions, OTRS keeps a user session alive across multiple http sessions). The Linux-HA stuff is the second tier for managing the application processing cluster. The LVS stuff does most of the sticky session processing. Re: SSDs. I’m not sure I’d go that route for the data tables. Indexes, maybe. That’s mighty expensive storage for dead data, as you mentioned.

I'm not familiar with LVS or Linux-HA (mostly used MS platforms until ~ recently) so the next question may be born out of confusion: You have the load balancing performed by machines runnings LVS and the Linux-HA is running on the app nodes? Correct. LVS handles session distribution and session affinity. Linux-HA handles resilience for the application nodes (if one node fails or goes unresponsive, Linux-HA can do things like move the workload to the other server, shoot the unresponsive node in the head, and take over). In the future, I plan to have it be able to provision an entirely new server node in one of the virtual machine pools once I get a decent grasp on the VMWare orchestration API. Just to make sure I understood some of your points: a. You have a load balancer in front of your app nodes using a generic sticky sessions algorithm that you didn't have to configure in any way for the OTRS nodes. Correct? Correct. We took the simple approach of assigning affinity based on incoming IP address because that’s how the Cisco boxes we had before did it. We had to do a bit of fiddling and measuring of our traffic load to tell LVS how often to time out the affinity relationship (how long a IP is associated with a specific OTRS server instance). You also have to accept that SSL protection of the session ends at the LVS systems, but in our case, that’s acceptable (if you have physical access to the wires, we’re already screwed). I suspect that OTRS session data in the database is sufficient nowadays, but we’ve never bothered to change the LVS setup. Ain’t broke, don’t fix it. b. In addition, because of your clever attachment juggling and because the user session is stored in the db you did not have to set up shared storage between the app nodes. Correct? Correct. Data management really shouldn’t be OTRS’ job. One last question: If you have no shared storage, how did you solve the default ticket number generator's dependency of TicketCounter.log file? A custom generator? Yes. Simple piece of code that uses a autoincrement field in a database table. I’ll see if I can get approval to post it. OTRS mainline really should move to something like it. One thing that is a bit weird if you use DB replication (rather than cluster mode) is that your autoincrement values are a function of the number of replicated nodes (ie, our ticket #s increment by 3, not one). That’s a MySQL thing, though.