Resource issue

[tvi]

We have a database (size 650 MB) containing 800K people records. We are observing the following issue with a search in Info / Notes:

With performing a search in all those records after a very long (several minutes) wait

• either (in very few cases) the result is a list which can’t be worked with
• or (in most cases) the server responds with a "504 Gateway Timeout"

This seems to be an indication of some resource constraints on the server. Which of the server resources could be causing this?

TNG 13.1.2

[bhemph]

This sounds like it is an SQL query that needs to have some performance improvement.  If you have a custom report, or are doing the SQL in something like phpMyAdmin then asking on the TNG maillist may get you better answers with more SQL people there.  If you are doing a search through a standard TNG search page, Darrin would be the person who would need to improve the efficiency of the query.

Brent

[tvi]

The performance issue is with the latter.

[Darrin Lythgoe]

You have a lot more notes than the average person, so you may be pushing the limits of what TNG can do, but the main query on that page should already be very simple. It only searches the tng_notelinks and tng_xnotes tables with a "right" join between them. Info for specific notes (like who they're linked to) is performed in a separate query for each note. It's also a "full text" search, which should make it easier to index and search large blocks of text. Have you tried optimizing your tables?