Hi Greg,
my update was running into timeout after 60 secs (large database). I have set a higher timeout value in my gedcom configs. So I was wondering, if this is not taken?
But clicking the page again seem to finish the process and all is fine.
My first impression is an significant performance improvement.
Thanks for this great piece of work.
Veit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
23 secs reported by the main page execution stats the first time. Subsequent time was 0.110. OSX running MySQL 5.0.83 on 3 GHz iMac.
new table pgv_site_setting
But on the PC also running 5.0.83, I got the new table created as InnoDB rather than MyISAM as the my.ini had the default engine set differently (must have missed that recently). But a
ALTER TABLE `pgv_site_setting` ENGINE = MYISAM
did the trick.
Mark
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2009-06-13
Thanks for the heads-up Greg. Didn't have stats turned on, but the delay was minimal for me. Everything went smooth apart from that though. Neat job. This will be a great bonus on the forum pages - one major issue less to worry about.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2009-06-13
One series of questions, from a novice perspective, related to Marks comment.
All my PGV tables are MYISAM, but I have other tables in the same DB (mostly gallery2) that are InnoDB.
Should I be concerned?
Will I do more harm than good if I change them to also be MYISAM?
Is there any benefit to doing them?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
MyISAM and InnoDB have different characteristics, and both have their advantages and disadvantages, Google will provide millions of pages where the relative merits are compared.
PGV doesn't specify - you'll get whatever your database is set to use by default.
InnoDB is ACID compliant and a bit more "rugged" than MyISAM. MyISAM is faster with simple queries (and sqlite can be faster still)
InnoDB has lots of parameters and tuning options, although unless you run your own server, you'll probably just have the installation defaults.
You don't even need to use the same engine on all tables.
Your best bet might be to try different combinations and compare the results. You can convert the tables using phpMyAdmin, and you can see the query timings by setting PGV_DEBUG_SQL to true in session.php. Just don't do this on a live site.
My general approach is to use InnoDB as the default, and use MyISAM when circumstances warrant.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Lots of providers don't support InnoDB, only MYISAM. But InnoDB is the more advanced engine type and should not cause problems.
I'm unsure what happens if you try to make joins between InnoDB Table and MYISAM table. So it could be better to have a unique engine setting at least for one app.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2009-06-14
Thanks Viet and Greg for that InnoDB / MYISAM info. Very helpful.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
When you next update to the latest SVN, you may notice a delay of several seconds while the first page loads.
This is normal ;-)
We can now perform database updates without reimporting gedcoms.
After the update, you will find that many of your column definitions have changed, which should help performance.
SImilar updates will happen over the next few days, as I review the various table indexes.
I have tested this with sqlite2, sqlite3, mysql4.1 and mysql5.
Please report any issues.
Hi Greg,
my update was running into timeout after 60 secs (large database). I have set a higher timeout value in my gedcom configs. So I was wondering, if this is not taken?
But clicking the page again seem to finish the process and all is fine.
My first impression is an significant performance improvement.
Thanks for this great piece of work.
Veit
IIRC you have a very large database.
The updates are done one at a time, so if you do hit a timeout, you can press F5.
<<I have set a higher timeout value in my gedcom configs. So I was wondering, if this is not taken?>>
Yes. The time limit is set on a per-gedcom basis (who knows why???). It is set in index/foo.ged_conf.php of all places!
But we can't select gedcoms/settings until after we have updated the database.
Therefore we have to update the database with the default time-out settings.
23 secs reported by the main page execution stats the first time. Subsequent time was 0.110. OSX running MySQL 5.0.83 on 3 GHz iMac.
new table pgv_site_setting
But on the PC also running 5.0.83, I got the new table created as InnoDB rather than MyISAM as the my.ini had the default engine set differently (must have missed that recently). But a
ALTER TABLE `pgv_site_setting` ENGINE = MYISAM
did the trick.
Mark
Thanks for the heads-up Greg. Didn't have stats turned on, but the delay was minimal for me. Everything went smooth apart from that though. Neat job. This will be a great bonus on the forum pages - one major issue less to worry about.
One series of questions, from a novice perspective, related to Marks comment.
All my PGV tables are MYISAM, but I have other tables in the same DB (mostly gallery2) that are InnoDB.
Should I be concerned?
Will I do more harm than good if I change them to also be MYISAM?
Is there any benefit to doing them?
MyISAM and InnoDB have different characteristics, and both have their advantages and disadvantages, Google will provide millions of pages where the relative merits are compared.
PGV doesn't specify - you'll get whatever your database is set to use by default.
InnoDB is ACID compliant and a bit more "rugged" than MyISAM. MyISAM is faster with simple queries (and sqlite can be faster still)
InnoDB has lots of parameters and tuning options, although unless you run your own server, you'll probably just have the installation defaults.
You don't even need to use the same engine on all tables.
Your best bet might be to try different combinations and compare the results. You can convert the tables using phpMyAdmin, and you can see the query timings by setting PGV_DEBUG_SQL to true in session.php. Just don't do this on a live site.
My general approach is to use InnoDB as the default, and use MyISAM when circumstances warrant.
Lots of providers don't support InnoDB, only MYISAM. But InnoDB is the more advanced engine type and should not cause problems.
I'm unsure what happens if you try to make joins between InnoDB Table and MYISAM table. So it could be better to have a unique engine setting at least for one app.
Thanks Viet and Greg for that InnoDB / MYISAM info. Very helpful.