Re: [otrs] Line wrap problems
At 10/26/04 10:06 PM, Robert Kehl
Robert L Mathews schrieb:
(The same goes for bug 518: OTRS is almost unusable with PostgreSQL as the backend with a large number of tickets, so I submitted a patch that fixes the schema to speed up OTRS dramatically and make OTRS work properly. But the patch appears to be languishing and is in danger of getting out of date so that it doesn't apply cleanly any more.)
ACK, ACK, ACK!
When it comes to a few 1000 tickets, a PostgreSQL database is merely not usable anymore due to the schema used. I changed my schema without a problem the last days and am experiencing a great improvement in queriying the db. Durations of the queries are lowered to a third in general, at least for the cost-intensive ones.
[kind words snipped]
Additionally OTRS should provide a script to convert an existing PG database to a new, 'integered' version.
If the patch is accepted to change the schema, I will gladly provide a script that can be used to alter the existing database in place, speeding up existing PostgreSQL installations dramatically. (In my case -- now at about 70,000 tickets in the system -- the changes sped up the "show me my [small number of] locked tickets" view almost 20-fold, from about ten seconds to about half a second.) -- Robert L Mathews, Tiger Technologies http://www.tigertech.net/ "Ignorance more frequently begets confidence than does knowledge." -- Darwin
Robert L Mathews schrieb:
If the patch is accepted to change the schema, I will gladly provide a script that can be used to alter the existing database in place, speeding up existing PostgreSQL installations dramatically.
(In my case -- now at about 70,000 tickets in the system -- the changes sped up the "show me my [small number of] locked tickets" view almost 20-fold, from about ten seconds to about half a second.)
Same goes for one of my customers installations with about 100000 articles and 10000 tickets. Yes, it comes in that fast. The customer is running OTRS on a Xeon-based 4-processor machine with a 16Gig chunk of ram and pleeenty of storage space available. Times were not bearable anymore, due to the numerous and highly cost-intensive seuqential scans, a QueueView was up to 20 seconds. So long for the story. We solved this by altering the schema and especially implementing a few new indexes. These were: CREATE INDEX index_article_ticket_id ON public.article USING btree (ticket_id); CREATE INDEX index_article_plain_article_id ON public.article_plain USING btree (article_id); CREATE INDEX index_group_user_group_id ON public.group_user USING btree (group_id); CREATE INDEX index_group_user_user_id ON public.group_user USING btree (user_id); VACUUM ANALYZE; Afterwards, working speed was bearable again. Additionally, I created the following indexes in my PostgreSQL installation (with much fewer tickets/articles) and am experiencing speed improvements, or, better: No laps anymore when it comes to FAQ, Attachments and stuff. CREATE INDEX article_attachment_filename_index ON public.article_attachment USING btree (filename); CREATE INDEX article_attachment_content_type_index ON public.article_attachment USING btree (content_type); CREATE INDEX article_attachment_content_size_index ON public.article_attachment USING btree (content_size); CREATE INDEX article_plain_article_id_index ON public.article_plain USING btree (article_id); CREATE INDEX generic_agent_jobs_job_name_index ON public.generic_agent_jobs USING btree (job_name); CREATE INDEX group_customer_user_user_id_index ON public.group_customer_user USING btree (user_id); CREATE INDEX group_customer_user_group_id_index ON public.group_customer_user USING btree (group_id); CREATE INDEX group_role_role_id_index ON public.group_role USING btree (role_id); CREATE INDEX group_user_user_id_index ON public.group_user USING btree (user_id); CREATE INDEX group_user_group_id_index ON public.group_user USING btree (group_id); CREATE INDEX notifications_notification_type_index ON public.notifications USING btree (notification_type); CREATE INDEX personal_queues_user_id_index ON public.personal_queues USING btree (user_id); CREATE INDEX personal_queues_queue_id_index ON public.personal_queues USING btree (queue_id); CREATE INDEX postmaster_filter_f_name_index ON public.postmaster_filter USING btree (f_name); CREATE INDEX queue_group_id_index ON public.queue USING btree (group_id); CREATE INDEX queue_system_address_id_index ON public.queue USING btree (system_address_id); CREATE INDEX queue_auto_response_queue_id_index ON public.queue_auto_response USING btree (queue_id); CREATE INDEX queue_auto_response_auto_response_id_index ON public.queue_auto_response USING btree (auto_response_id); CREATE INDEX queue_standard_response_queue_id_index ON public.queue_standard_response USING btree (queue_id); CREATE INDEX queue_standard_response_standard_response_id_index ON public.queue_standard_response USING btree (standard_response_id); CREATE INDEX role_user_role_id_index ON public.role_user USING btree (role_id); CREATE INDEX role_user_user_id_index ON public.role_user USING btree (user_id); CREATE INDEX search_profile_login_index ON public.search_profile USING btree (login); CREATE INDEX search_profile_name_index ON public.search_profile USING btree (profile_name); CREATE INDEX session_session_id_index ON public.session USING btree (session_id); CREATE INDEX standard_response_attachment_standard_attachment_id_index ON public.standard_response_attachment USING btree (standard_attachment_id); CREATE INDEX standard_response_attachment_standard_response_id_index ON public.standard_response_attachment USING btree (standard_response_id); CREATE INDEX system_address_queue_id_index ON public.system_address USING btree (queue_id); CREATE INDEX ticket_queue_id_index ON public.ticket USING btree (queue_id); CREATE INDEX ticket_group_id_index ON public.ticket USING btree (group_id); CREATE INDEX ticket_history_article_id_index ON public.ticket_history USING btree (article_id); CREATE INDEX ticket_index_queue_id_index ON public.ticket_index USING btree (queue_id); CREATE INDEX ticket_index_group_id_index ON public.ticket_index USING btree (group_id); CREATE INDEX ticket_link_ticket_id_master_index ON public.ticket_link USING btree (ticket_id_master); CREATE INDEX ticket_link_ticket_id_slave_index ON public.ticket_link USING btree (ticket_id_slave); CREATE INDEX ticket_state_for_type_id_index ON public.ticket_state USING btree (type_id); CREATE INDEX time_accounting_article_id_index ON public.time_accounting USING btree (article_id); VACUUM ANALYZE; Robert, what do you think of these? It may well be I'm doing too much here, but I guess too much indexes won't hurt. With kind regards, Robert Kehl
participants (2)
-
Robert Kehl -
Robert L Mathews