Hello again, I am runing PGV 4.2.2 at Baxgen.com:5080 and have noticed that on occasion searching for the same text twice in the main search box will sometimes be very fast the first time and then the next time take all the way to the 90 second execution limit to return.
I have a very similar installation at Baxgen.com that always returns the search results in about the same short time. It never takes more than 30 seconds on that installation to return from a search. Any ideas?
-Jeff
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2009-11-10
Jeff, can you give us an example that reproduces the issue consistently, so we can see it ourselves?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Kiwi, I am working with Jeff on this search performance issue. You can go to www.baxgen.com:5080 and enter the text "Brent" into the search box. It may return almost immediately, or it may take up to 90 seconds to return. Sometimes the long delay occurs after two or three previous searches.
As Jeff explained, we have the same data on another server at www.baxgen.com that never experiences the long delay. The server on port 5080 is running Ubuntu 9.10, whereas the one that never hits the snag is running Ubuntu 9.04
Brent
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Brent
Sounds like a couple of issues, but we can't see anything as the URL you provided is private and requires a login to search.
1) caching ?
2) server load and available RAM ?
3) collation ?
-Stephen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I can search without loggin in. Logging in does not seem to affect the behavior.
The server is running on a dedicated box with 4 GB of RAM and a fast Intel Core Duo processor with lots of disk space and a reasonably fast internet connection.
When the problem occurs the CPU utilization goes to 100% and the top process is MYSQL.
Clearing the FireFox cache has no apparent effect.
Can you suggest some things I might investigate?
Brent
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2009-11-11
Brent, what stephen means is that the url you gave above (Baxgen.com:5080) only goes to a login page, not to PGV at all. The correct URL is:
It looks to me like there may be problems with the table /field collations. Chack that all tables AND all their fields have the same collation. That is the most common cause of slow searching.
If necessary refer to this patch for more assistance:
Thanks for the suggestion. I am looking to this possibility
One other clue: Searches initiated using the search tool on the main toolbar do not encounter the delays we experience using the search box in the far upper right of the screen. Does this suggest anything?
Brent
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Here is the update: This installation of the latest version of Phpgedview is now at www.baxgen.com/phpgedview I converted the tables to UTF8 and the periodic search hanging issue remains. For the following test I searched for "test" and the browser returned the results in less than 1 second several times. I then searched for "Test" and it too 60 seconds to return the results. Other search criteria also occasionally hang the search tool. Here is a snapshot of what the MYSQL Process list had in it during the query. Notice that when I took the snapshot the query had been running for 57 seconds and was about to time out.
Command: Query
Time: 57
State: Copying to tmp table
Info: SELECT DISTINCT 'FAM' AS type, f_id AS xref, f_file AS ged_id, f_gedcom AS gedrec, f_husb, f_wife, f_chil, f_numchil FROM pgv_families LEFT OUTER JOIN pgv_name husb ON f_husb=husb.n_id AND f_file=husb.n_file LEFT OUTER JOIN pgv_name wife ON f_wife=wife.n_id AND f_file=wife.n_file WHERE ((husb.n_full LIKE '%Test%' OR wife.n_full LIKE '%Test%')) AND f_file IN (4,3,2) ORDER BY ged_id
Any ideas? I greatly appreciate all the help that I have received.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2009-11-20
I don't see any response to the earlier suggestion regarding table and field collation settings. Have you checked that ALL tables and ALL their fields have the same collation setting?
Have you used the patch tool recommended?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I set the collation on the database to UTF-8 and the config.php. I was following the instructions on the patch tool page. Perhaps I have not followed it completely.
By the way what is an explain plan?
-Jeff
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Jeff
When you are experiencing problems with your DB, and ir you don't understand the mechanics of a SQL DB, and someone suggests you try a method to examine the DB, why not 'google' the terminology:
BTW, just setting the collation of the DB to UTF-8 (what version?) and if you changed the config.php without going through the optimization process and verification that the DB is properly collated (setting it to UTF-8 only means new tables will be created with those parameters), you have perhaps inadvertently added to the confusion. There are several threads here on resetting a DB collation and links to the patch Greg posted previously. Use a DB management program, like phpMyAdmin, and actually examine how your genealogy DB is collated, assuring yourself that each one is precisely matching all the others. If not, then use the patch. You can also read the EXPLAIN PLAN discussion at the link I provided. There are others too, from Google.
-Stephen
Hello again, I am runing PGV 4.2.2 at Baxgen.com:5080 and have noticed that on occasion searching for the same text twice in the main search box will sometimes be very fast the first time and then the next time take all the way to the 90 second execution limit to return.
I have a very similar installation at Baxgen.com that always returns the search results in about the same short time. It never takes more than 30 seconds on that installation to return from a search. Any ideas?
-Jeff
Jeff, can you give us an example that reproduces the issue consistently, so we can see it ourselves?
Kiwi, I am working with Jeff on this search performance issue. You can go to www.baxgen.com:5080 and enter the text "Brent" into the search box. It may return almost immediately, or it may take up to 90 seconds to return. Sometimes the long delay occurs after two or three previous searches.
As Jeff explained, we have the same data on another server at www.baxgen.com that never experiences the long delay. The server on port 5080 is running Ubuntu 9.10, whereas the one that never hits the snag is running Ubuntu 9.04
Brent
One correction. Both servers are running Ubuntu 9.0
Brent
Sounds like a couple of issues, but we can't see anything as the URL you provided is private and requires a login to search.
1) caching ?
2) server load and available RAM ?
3) collation ?
-Stephen
Thanks for taking time to look into this.
I can search without loggin in. Logging in does not seem to affect the behavior.
The server is running on a dedicated box with 4 GB of RAM and a fast Intel Core Duo processor with lots of disk space and a reasonably fast internet connection.
When the problem occurs the CPU utilization goes to 100% and the top process is MYSQL.
Clearing the FireFox cache has no apparent effect.
Can you suggest some things I might investigate?
Brent
Brent, what stephen means is that the url you gave above (Baxgen.com:5080) only goes to a login page, not to PGV at all. The correct URL is:
http://baxgen.com:5080/phpgedview/
It looks to me like there may be problems with the table /field collations. Chack that all tables AND all their fields have the same collation. That is the most common cause of slow searching.
If necessary refer to this patch for more assistance:
https://sourceforge.net/tracker/?func=detail&aid=2318005&group_id=55456&atid=477081
Thanks for the suggestion. I am looking to this possibility
One other clue: Searches initiated using the search tool on the main toolbar do not encounter the delays we experience using the search box in the far upper right of the screen. Does this suggest anything?
Brent
Have you looked at your MySQL slow-query-log?
Have you looked at MySQL's processlist while the search is taking place?
Can I do those with PhpMyAdmin?
The latter is a PMA menu option.
The former is (generally) a file on your server. You can find its location in PMA, under the list of system variables.
Hello again,
Here is the update: This installation of the latest version of Phpgedview is now at www.baxgen.com/phpgedview I converted the tables to UTF8 and the periodic search hanging issue remains. For the following test I searched for "test" and the browser returned the results in less than 1 second several times. I then searched for "Test" and it too 60 seconds to return the results. Other search criteria also occasionally hang the search tool. Here is a snapshot of what the MYSQL Process list had in it during the query. Notice that when I took the snapshot the query had been running for 57 seconds and was about to time out.
Command: Query
Time: 57
State: Copying to tmp table
Info: SELECT DISTINCT 'FAM' AS type, f_id AS xref, f_file AS ged_id, f_gedcom AS gedrec, f_husb, f_wife, f_chil, f_numchil FROM pgv_families LEFT OUTER JOIN pgv_name husb ON f_husb=husb.n_id AND f_file=husb.n_file LEFT OUTER JOIN pgv_name wife ON f_wife=wife.n_id AND f_file=wife.n_file WHERE ((husb.n_full LIKE '%Test%' OR wife.n_full LIKE '%Test%')) AND f_file IN (4,3,2) ORDER BY ged_id
Any ideas? I greatly appreciate all the help that I have received.
I don't see any response to the earlier suggestion regarding table and field collation settings. Have you checked that ALL tables and ALL their fields have the same collation setting?
Have you used the patch tool recommended?
I've found that some versions of MySQL have radically different execution plans of the same query, some making use of indexes and some not.
Have you tried running an explain plan on this SQL?
I set the collation on the database to UTF-8 and the config.php. I was following the instructions on the patch tool page. Perhaps I have not followed it completely.
By the way what is an explain plan?
-Jeff
Jeff
When you are experiencing problems with your DB, and ir you don't understand the mechanics of a SQL DB, and someone suggests you try a method to examine the DB, why not 'google' the terminology:
http://www.dbtuna.com/article.asp?id=14
BTW, just setting the collation of the DB to UTF-8 (what version?) and if you changed the config.php without going through the optimization process and verification that the DB is properly collated (setting it to UTF-8 only means new tables will be created with those parameters), you have perhaps inadvertently added to the confusion. There are several threads here on resetting a DB collation and links to the patch Greg posted previously. Use a DB management program, like phpMyAdmin, and actually examine how your genealogy DB is collated, assuring yourself that each one is precisely matching all the others. If not, then use the patch. You can also read the EXPLAIN PLAN discussion at the link I provided. There are others too, from Google.
-Stephen
: http://www.dbtuna.com/article.asp?id=14 "Explain Plan in MySQL"