This Help forum is for issues relates to the latest release (1.7.8). For issues related to beta or github version please use their own Help forum.
Before asking for help please read "How to request help" by clicking on that tab above here."

TOPIC: Huge wait time creating first new individual in

Huge wait time creating first new individual in 2 years 7 months ago #1

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
I find that there is a huge delay in creating the first new individual (e.g. the father of Joe BLOGGS) in each new tree on my site. When I click “Save”, webtrees freezes totally as if it had crashed, but after 20 minutes it resumes and works perfectly as if nothing had happened. If I delete the new individual, I can recreate him or some other new individual with no delay whatsoever. The very same thing happens for the first media object which I upload to a new tree.

I first noticed this problem a year or two. The delay was only a minute or two then, but it has been getting progressively worse, and is now up to about 20 minutes for the first new individual, and about 10 minutes for the first media object. I use webtrees for teaching a genealogy course and now have 100 family trees on the site with about 16,000 individuals in total and about 8000 media objects. I started off with PhpGedView in 2010, and converted to webtrees almost as soon as it first appeared. All the early family trees share XREFs running I1, I2, I3... - I don’t know whether that has anything to do with the problem? I am running the latest version of Webtrees.

Other than possibily starting off a completely new installataion of Webtrees, with a new clean database, and importing all 100 GEDCOMs, I have run out of ideas. And I don’t know whether a fresh installation would cure things. The XREFs would presumably be the same, reimported from the GEDCOMs?

All ideas and suggestions welcomed!
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #2

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
100 trees / 16000 individuals is small. I know a site with 10000 trees and I know sites with millions of individuals.

There is nothing special about the first individual.

Is this your own server? If so, perhaps enable slow-query-logging, and see if there are any MySQL queries that are taking a long time.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #3

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
Thanks for the advice, Greg. Yes, it is my own site. I don’t know anything at all about slow-query logging, but I’ll find out and I’ll try it. Thanks again.
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #4

  • ToyGuy
  • ToyGuy's Avatar
  • Offline
  • Moderator
  • Live like it's Christmas every day - Santa Stephen
  • Posts: 4925
Why 100 trees? Are you trying to host for others using one installation of webtrees?
TMK, while running multiple trees is OK, it wasn't designed for hundreds, maybe not even tens of trees.
I do use one installation for my private investigation business and I do have about 15 trees there - all small ones and few media items - but a new tree takes about 1-2 seconds to create.

How much memory are you devoting to webtrees? 3-4gb would help a lot, assuming not all the trees are active at one time. If the latter, you'll need lots of memory and a really big pipe.
Santa Stephen the Fabled Santa
Latest webtrees at MyArnolds.com
Hosted by webtreesonline.com, a division of GeneHosts LLC
MacOS 10.6.8, Apache 2.2+, PHP 5.4.16, MySQL 5.5.28
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #5

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
> TMK, while running multiple trees is OK, it wasn't designed for hundreds, maybe not even tens of trees.

I worked on a site that has 10000+ trees and performance is fine. I don't think the number of trees is causing this issue.

The control panel gets a bit unmanagable with 1000s of trees, but does not affect server performance.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #6

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
The 100 trees are for my students and ex-students, many of whom continue to maintain and develop their trees from time to time. I don’t know how much memory I am devoting, but the server has 16GB of RAM and I have not changed any defaults. The amount of concurrent usage of webtrees is very small and should not be a problem. Neither the server itself, nor webtrees in everyday use are slow. Creating a new tree is not slow. The only things which are (extremely!) slow are: adding the first new individual to a tree; adding the first media object; and to a lesser extent, adding the first source. And I think I have very occasionally seen the same thing happening with a tree which is not new but which had not been edited for a long time. The kind of possibly cause which springs to my mind is some sort of reindexing running and somehow locking up, but as Greg says, I should find out about slow-query-logging and try it.
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #7

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
You could try rebuilding each of the tables in the database. e.g.

ALTER TABLE <table_name> ENGINE=InnoDB;
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #8

  • ToyGuy
  • ToyGuy's Avatar
  • Offline
  • Moderator
  • Live like it's Christmas every day - Santa Stephen
  • Posts: 4925
In the control panel, look at the PHP information and see your MASTER memory and webtrees memory.
You can set the memory higher. Control Panel > Website > Website Preferences. If you can, set to 2gb+ (2046M)
Santa Stephen the Fabled Santa
Latest webtrees at MyArnolds.com
Hosted by webtreesonline.com, a division of GeneHosts LLC
MacOS 10.6.8, Apache 2.2+, PHP 5.4.16, MySQL 5.5.28
The administrator has disabled public write access.
Do you need a web hosting solution for your webtrees site?
If you prefer a host that specialises in webtrees, the following page lists some suppliers able to provide one for you: 

Huge wait time creating first new individual in 2 years 7 months ago #9

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
Thanks for all the suggestions. I have tried them, but still no joy yet.

I ran ALTER TABLE <table_name> ENGINE=InnoDB; on each of the tables. (They were already in InnoDB format.) I switched on “log slow filter” in phpMyAdmin, and set “log output” to TABLE. I then created a new family tree, with no problems as usual. Then I created Joe BLOGGS senior and he took 20 minutes to appear as usual, but nothing appeared in the table mysql.slow_log.

In the webtrees control pannel, I checked that the memory limit was set to 2048M (although the server default is 1024M). I spotted the “PHP time limit” parameter and changed this from the server default of 1200 seconds (20 minutes) to 60 seconds. I then uploaded a first (tiny) media file to the new family tree. As usual, this took at least 10 minutes after clicking Save. Still nothing appeared in the mySQL (or mariaDB to be exact) slow_log.

So I am still stuck. It is possible that I have not set up the slow logging properly, since I have no previous experience of it. I need to check this. I could also play with the other logging parameters. Other than that, and the rather drastic option of reinstalling webtrees and reloading all 100 family trees from GEDCOD, I have no ideas.
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #10

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
> It is possible that I have not set up the slow logging properly, since I have no previous experience of it. I need to check this.

Suppose you set the slow query log to record all queries that take more than 10 seconds (the default).

You can then run a query that takes longer than this. e.g. SELECT SLEEP(11);

If you don't see it in the log, you haven't set it up correctly!

By default, it writes to a file. If you want it to write to a table, you must enable this.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #11

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
Thanks! That test worked a treat and created a record in slow_log. So the logging is working. I see, though, that there are some kinds of database operations which are not slow-logged by default. Maybe I should look at that?
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #12

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
If you want to log all queries, use the "general-log"
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #13

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
Thanks. I did that - switched on general query logging to table. Then I set up a new database. I could change Joe BLOGGS’s name, give him a death record, add and delete notes no problem. But when I tried to give him a father, I had a wait of pretty well exactly 20 minutes after I clicked Save before I got a response and the father appeared.

Meanwhile the log showed that it was not one single query which was holding things up, but rather continual cycling between the same two queries:

SELECT i_id FROM `wt_individuals` WHERE i_id = 'I1...

and

UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n...

with the cycle repeated about 21,000 times. (Unfortunately, the log table has only preserved the start of the queries.)
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #14

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
> with the cycle repeated about 21,000 times.

This is very helpful! I know what is happening here.

I think you can improve performance on your server by changing the MySQL configuration parameter innodb_flush_log_at_trx_commit

I guess it is at the default of 1. Try setting it to 0 (and restarting the MySQL server process).

Meanwhile, I will look at the code to see if there's a way to avoid triggering this issue.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #15

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #16

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
[Thanks, Greg, for your reply this morning, which I spotted just before submitting this message. I’ll try what you suggest, but the following additional information might be useful.]

I have looked at the general query log in more detail in the light of day. Here is how the looping starts (and the preceding few queries). It seems very strange that it is looking for i_id='F1' in wt_individuals.

SELECT i_gedcom FROM `wt_individuals` WHERE i_id =...
SELECT xref, new_gedcom FROM `wt_change` WHERE sta...
SELECT SQL_CACHE setting_name, setting_value FROM
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n..
INSERT INTO `wt_next_id` (gedcom_id, record_type,

SELECT i_id FROM `wt_individuals` WHERE i_id = 'F1...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n..

SELECT i_id FROM `wt_individuals` WHERE i_id = 'F2...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n...

SELECT i_id FROM `wt_individuals` WHERE i_id = 'F3...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n...

SELECT i_id FROM `wt_individuals` WHERE i_id = 'F4...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n...

SELECT i_id FROM `wt_individuals` WHERE i_id = 'F5...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n...

(and similarly for about another 4240 query pairs)

SELECT i_id FROM `wt_individuals` WHERE i_id = 'F4...
INSERT INTO `wt_change` (gedcom_id, xref, old_gedc...
INSERT INTO `wt_log` (log_type, log_message, ip_ad...
SELECT change_id, gedcom_name, old_gedcom, new_ged.
SELECT pl_p_id FROM `wt_placelinks` WHERE pl_gid=
DELETE FROM `wt_placelinks` WHERE pl_gid='F4212'
(and about another 30 assorted queries)
INSERT INTO `wt_log` (log_type, log_message, ip_ad...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n...
INSERT INTO `wt_next_id` (gedcom_id, record_type, ..

(before the looping begins again, this time with I1,I2... instead of F1,F2...

SELECT i_id FROM `wt_individuals` WHERE i_id = 'I1...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n..

SELECT i_id FROM `wt_individuals` WHERE i_id = 'I2...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n..

SELECT i_id FROM `wt_individuals` WHERE i_id = 'I3...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n..

SELECT i_id FROM `wt_individuals` WHERE i_id = 'I4...
UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n..

(and similarly for about another 12920 query pairs)

UPDATE `wt_next_id` SET next_id = LAST_INSERT_ID(n...
SELECT i_id FROM `wt_individuals` WHERE i_id = 'I1..
INSERT INTO `wt_change` (gedcom_id, xref, old_gedc..
INSERT INTO `wt_log` (log_type, log_message, ip_ad...
SELECT change_id, gedcom_name, old_gedcom, new_ged..
SELECT pl_p_id FROM `wt_placelinks` WHERE pl_gid='...
DELETE FROM `wt_placelinks` WHERE pl_gid='I12909'
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #17

  • fisharebest
  • fisharebest's Avatar
  • Offline
  • Administrator
  • Posts: 11102
webtrees is looking for a new XREF. It searches for F1, F2, F3, ... until it finds one that is available.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 7 months ago #18

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
Thanks for the tip on setting innodb_flush_log_at_trx_commit=0. I experimented, and that cut the wait time from 20 minutes to 8 minutes, so it will be worthwhile all round.
The administrator has disabled public write access.

Huge wait time creating first new individual in 2 years 5 months ago #19

  • caoimhin
  • caoimhin's Avatar
  • Offline
  • New
  • Posts: 21
Just to say that I tested this in the new release, 1.7.9, and the problem has gone. Hurray! Many thanks.
The administrator has disabled public write access.
Powered by Kunena Forum