| Friday, May 29th, 2015, 00:09 UTC | ||
| [00:09:40] | andreaz (andreaz!~andre_000@p5DD1506E.dip0.t-ipconnect.de) has quit (Read error: Connection reset by peer) | |
| [00:26:54] | stichnot (stichnot!~stichnot@mythtv/developer/stichnot) has quit (Ping timeout: 245 seconds) | |
| [00:43:59] | Warped (Warped!~Warped@unaffiliated/warped) has quit (Read error: Connection reset by peer) | |
| [00:46:01] | Warped (Warped!~Warped@unaffiliated/warped) has joined #mythtv | |
| [02:28:26] | tgm4883 (tgm4883!uid23806@ubuntu/member/tgm4883) has quit (Quit: Connection closed for inactivity) | |
| [02:39:40] | arescorpio (arescorpio!~arescorpi@223-39-16-190.fibertel.com.ar) has joined #mythtv | |
| [02:42:47] | SteveGoodey (SteveGoodey!~steve@host86-151-177-34.range86-151.btcentralplus.com) has joined #mythtv | |
| [02:43:12] | Horntel (Horntel!~eric@24-158-7-26.static.mdsn.wi.charter.com) has joined #mythtv | |
| [02:43:53] | Horntel (Horntel!~eric@24-158-7-26.static.mdsn.wi.charter.com) has quit () | |
| [03:29:09] | stichnot (stichnot!~stichnot@mythtv/developer/stichnot) has joined #mythtv | |
| [03:42:34] | fetzerch_ (fetzerch_!~quassel@unaffiliated/fetzerch) has joined #mythtv | |
| [03:43:30] | tgm4883 (tgm4883!uid23806@ubuntu/member/tgm4883) has joined #mythtv | |
| [03:44:10] | SteveGoodey (SteveGoodey!~steve@host86-151-177-34.range86-151.btcentralplus.com) has quit (Quit: Konversation terminated!) | |
| [03:46:28] | fetzerch (fetzerch!~quassel@unaffiliated/fetzerch) has quit (Ping timeout: 255 seconds) | |
| [03:56:06] | stuartm (stuartm!~gbee@mythtv/developer/stuartm) has joined #mythtv | |
| [04:06:51] | arescorpio (arescorpio!~arescorpi@223-39-16-190.fibertel.com.ar) has quit (Quit: Leaving.) | |
| [04:55:05] | dekarl (dekarl!~dekarl@mythtv/developer/dekarl) has quit (Read error: Connection timed out) | |
| [04:57:05] | dekarl (dekarl!~dekarl@mythtv/developer/dekarl) has joined #mythtv | |
| [05:50:52] | Tobbe5178 (Tobbe5178!~asdf@2001:2002:51e1:d8ee:5555:ee3f:5dd7:e2fc) has joined #mythtv | |
| [05:52:39] | stichnot (stichnot!~stichnot@mythtv/developer/stichnot) has quit (Ping timeout: 250 seconds) | |
| [05:53:09] | stichnot (stichnot!~stichnot@adsl-68-125-53-199.dsl.pltn13.pacbell.net) has joined #mythtv | |
| [05:53:09] | stichnot (stichnot!~stichnot@mythtv/developer/stichnot) has joined #mythtv | |
| [05:53:09] | stichnot (stichnot!~stichnot@adsl-68-125-53-199.dsl.pltn13.pacbell.net) has quit (Changing host) | |
| [06:12:58] | FabriceMG (FabriceMG!~Thunderbi@LCaen-656-1-100-212.w80-11.abo.wanadoo.fr) has joined #mythtv | |
| [06:43:12] | peper03 (peper03!~peper03@mythtv/developer/peper03) has quit (Ping timeout: 265 seconds) | |
| [06:43:39] | peper03 (peper03!~peper03@mythtv/developer/peper03) has joined #mythtv | |
| [06:48:25] | peper03 (peper03!~peper03@mythtv/developer/peper03) has quit (Ping timeout: 264 seconds) | |
| [07:16:31] | dekarl: | man, we need a scheduled maintanance job of "ALTER TABLE `recordedseek` ORDER BY chanid, starttime;" (Query took 248.3842 sec). It makes loading the seektable on playback start performant again on an old database. (Until we migrate that beast to InnoDB) |
| [07:17:04] | dekarl: | found that hint in the comment section of http://www.psce.com/blog/2012/07/09/data-frag . . . ysql-myisam/ |
| [07:54:39] | seld (seld!~seld@h168n8-rny-a12.ias.bredband.telia.com) has quit (Ping timeout: 272 seconds) | |
| [07:57:19] | seld (seld!~seld@h168n8-rny-a12.ias.bredband.telia.com) has joined #mythtv | |
| [08:09:54] | stuartm: | dekarl: so what is that actually doing? |
| [08:10:25] | ** stuartm reads the webpage ** | |
| [08:10:52] | stuartm: | oh, that's interesting ... |
| [08:13:53] | dekarl: | stuartm, it reduces the number of seeks to collect all data. reduced the startup time for recording playback in 100% of the cases from 10+ seconds back to the old sub-second. (ignoring the first test with cold caches) |
| [08:14:18] | dekarl: | The "issue" started when recorded seek outgrew my memory cache |
| [08:16:12] | SteveGoodey (SteveGoodey!~steve@host86-151-177-34.range86-151.btcentralplus.com) has joined #mythtv | |
| [08:17:34] | dekarl: | obviously moving to recordedid as primary key will help by shrinking the table to one third of its size (or thereabout, I did the math months ago, maybe I remember wrong) |
| [08:18:05] | dekarl: | But that "defragmentation" query was news to me |
| [08:19:41] | Merlin83b (Merlin83b!~Daniel@office.34sp.com) has joined #mythtv | |
| [08:26:49] | dekarl: | The contained link to the InnoDB article looks interesting, too. Have to keep it in mind once we migrate http://www.psce.com/blog/2012/04/04/how-impor . . . performance/ |
| [08:32:05] | stuartm: | moving to recordedid will help both with speed and size |
| [08:56:26] | stuartm (stuartm!~gbee@mythtv/developer/stuartm) has quit (Ping timeout: 258 seconds) | |
| [08:58:01] | Roklobsta (Roklobsta!~Roklobsta@ppp118-209-112-237.lns20.mel4.internode.on.net) has joined #mythtv | |
| [08:59:30] | dekarl: | stuartm, do you think running it daily is to often? I'm wondering if the defrag could be run after cleaning out orphaned seek tables in CleanupTask::CleanupRecordedTables() |
| [08:59:57] | dekarl: | the article hints that *the first* run will take a long time, but repeated sorts will be faster |
| [09:00:30] | dekarl: | Would be nice to schedule it at a time without recordings. |
| [09:05:07] | stuarta (stuarta!sauchter@nat/redhat/x-uwbbtlysqcdujluy) has joined #mythtv | |
| [09:08:26] | stuarta: | dekarl: interesting observation on that mysql table |
| [09:10:31] | dekarl: | ThreadedFileWriter complains with even a single recording running in parallel to mysql defragmenting |
| [09:11:18] | stuarta: | those defrag queries are heavy, it's spending the whole time rebuilding stuff on disk |
| [09:11:44] | stuarta: | did similar stuff at my old place of work, some of the stuff took 5hrs to run |
| [09:12:26] | dekarl: | ouch, that test recording is going to the database disk. no wonder its complaining :) |
| [09:12:34] | stuarta: | doh |
| [09:12:59] | dekarl: | need to shuffle the stuff around so I only have videos on the database/os disk |
| [09:14:18] | dekarl: | query took 208s this time, not so much faster compared to 248s on the first run... |
| [09:15:44] | stuarta: | with innodb the index you decide to use is crucial |
| [09:16:19] | stuarta: | also how many, and what they contain |
| [09:16:48] | stuarta: | like you never create indexes (A,B) and (A,B,C), just (A,B,C) |
| [09:17:04] | stuarta: | AB queries will still use the ABC index |
| [09:47:09] | ghoti (ghoti!~paul@hq.experiencepoint.com) has quit (Ping timeout: 256 seconds) | |
| [10:04:57] | ghoti (ghoti!~paul@hq.experiencepoint.com) has joined #mythtv | |
| [10:10:04] | ghoti (ghoti!~paul@hq.experiencepoint.com) has quit (Ping timeout: 265 seconds) | |
| [10:15:46] | peper03 (peper03!~peper03@mythtv/developer/peper03) has joined #mythtv | |
| [10:20:14] | peper03 (peper03!~peper03@mythtv/developer/peper03) has quit (Ping timeout: 245 seconds) | |
| [10:20:52] | peper03 (peper03!~peper03@mythtv/developer/peper03) has joined #mythtv | |
| [10:23:29] | stuartm (stuartm!~gbee@mythtv/developer/stuartm) has joined #mythtv | |
| [10:50:59] | ghoti (ghoti!~paul@hq.experiencepoint.com) has joined #mythtv | |
| [11:53:26] | amessina (amessina!~amessina@unaffiliated/amessina) has quit (Quit: Konversation terminated!) | |
| [12:54:17] | stichnot (stichnot!~stichnot@216.239.45.130) has joined #mythtv | |
| [12:54:17] | stichnot (stichnot!~stichnot@216.239.45.130) has quit (Changing host) | |
| [12:54:17] | stichnot (stichnot!~stichnot@mythtv/developer/stichnot) has joined #mythtv | |
| [13:09:31] | stuartm_ (stuartm_!~gbee@mythtv/developer/stuartm) has joined #mythtv | |
| [13:12:09] | stuartm (stuartm!~gbee@mythtv/developer/stuartm) has quit (Ping timeout: 276 seconds) | |
| [13:17:13] | Seeker` (Seeker`!~cjo20@unaffiliated/seeker) has quit (Ping timeout: 264 seconds) | |
| [13:22:59] | Seeker` (Seeker`!~cjo20@unaffiliated/seeker) has joined #mythtv | |
| [13:42:22] | jheizer: | dekarl, I was curious about the query so some comparison numbers with 6705317 rows, 9 year DB, on a pretty fast SSD, 26.61sec first run, 27.56 sec second run. |
| [13:47:26] | taylorr: | anyone know where the script is that generates the mythtv cppcheck webpage? https://code.mythtv.org/cppcheck |
| [13:47:42] | stuarta: | extras iirc |
| [13:48:01] | skd5aner (skd5aner!~skd5aner@195.sub-70-198-68.myvzw.com) has quit (Ping timeout: 250 seconds) | |
| [13:48:28] | stuarta: | hmmm no |
| [14:06:35] | FabriceMG (FabriceMG!~Thunderbi@LCaen-656-1-100-212.w80-11.abo.wanadoo.fr) has quit (Quit: FabriceMG) | |
| [14:22:27] | taylorr: | stuarta: looks like it used to live here on github -> http://lists.mythtv.org/pipermail/mythtv-fire . . . /001616.html |
| [14:26:20] | stuarta: | i don't have access to my server to check at the moment, but it may be in a private git repo |
| [14:32:02] | stuarta: | dekarl: shouldn't we just invest time in determining the best schema change here? |
| [14:51:40] | taylorr: | stuarta: cool, thanks! |
| [14:52:54] | dekarl1 (dekarl1!~dekarl@mythtv/developer/dekarl) has joined #mythtv | |
| [14:54:51] | dekarl (dekarl!~dekarl@mythtv/developer/dekarl) has quit (Ping timeout: 258 seconds) | |
| [15:05:08] | stuartm_ (stuartm_!~gbee@mythtv/developer/stuartm) has quit (Quit: Arrividerci) | |
| [15:07:44] | jpabq (jpabq!~quassel@mythtv/developer/jpabq) has quit (Ping timeout: 256 seconds) | |
| [15:08:07] | jpabq (jpabq!~quassel@mythtv/developer/jpabq) has joined #mythtv | |
| [15:24:46] | SteveGoodey (SteveGoodey!~steve@host86-151-177-34.range86-151.btcentralplus.com) has quit (Ping timeout: 252 seconds) | |
| [15:25:27] | SteveGoodey (SteveGoodey!~steve@host109-159-59-143.range109-159.btcentralplus.com) has joined #mythtv | |
| [15:39:26] | dekarl1: | jheizer: mine is 21m rows. How long does it take to load a seek table from disk? In theory doing lots of small reads vs. some big reads helps with SSDs, too |
| [15:39:54] | dekarl1 is now known as dekarl | |
| [15:41:25] | dekarl: | stuarta, I found the cppcheck configuration and scripts in the (private) buildbot-config repo |
| [15:41:48] | jheizer: | easy way to test that? just do a select in mysql? |
| [15:42:09] | dekarl: | stuarta, I agree that moving to innodb with recordedid (or similar) is very attractive. But its lots more work :) |
| [15:43:54] | dekarl: | jheizer: do you still have the fragmented table around? select * from recordedseek where chanid=x and starttime=y; should do it. (or if you have slow queries log enabled they might already be there) or just start playback of one recording after another. |
| [15:48:29] | dekarl: | someone posted about slow playback start in the last days, but I can't find it atm. Would be a good test candidate if the defragmentation helps there |
| [15:49:14] | jheizer: | 24GB h264, 5hr recording |
| [15:49:15] | jheizer: | 8484 rows in set (0.01 sec) |
| [15:49:37] | joki (joki!~joki@p5B36D855.dip0.t-ipconnect.de) has quit (Ping timeout: 264 seconds) | |
| [15:52:03] | jheizer: | restoring last nights backup, give me 5. |
| [15:54:53] | joki (joki!~joki@p5B36EBA3.dip0.t-ipconnect.de) has joined #mythtv | |
| [15:55:08] | jheizer: | Before defrag 8484 rows in set (0.03 sec) |
| [15:56:33] | dekarl: | still not bad |
| [15:56:37] | dekarl: | Maybe https://github.com/MythTV/mythtv/blob/master/ . . . ze_mythdb.pl is a better place to put the defragmentation. It runs long already (on recordedseek) and we suggest the user hooks it into a daily/weekly/monthly cron job or make it part of their regular maintenance. |
| [15:57:08] | stuarta: | that would get my vote over the housekeeper |
| [16:02:00] | dekarl: | that script just took 3m20s with mysqld pumping 100+MB/s peak read/write. So adding it there does not add an additional heady workload where there is non now |
| [16:03:36] | jheizer: | wow, that script takes 18 seconds here |
| [16:04:52] | jheizer: | I already run that nightly, not sure if you do or if that would even matter. |
| [16:07:55] | stuarta: | dekarl: is that alter table statement still valid for innodb, or only myisam tables? |
| [16:21:58] | stuarta (stuarta!sauchter@nat/redhat/x-uwbbtlysqcdujluy) has quit (Quit: bye bye) | |
| [16:40:45] | Merlin83b (Merlin83b!~Daniel@office.34sp.com) has quit (Quit: Leaving) | |
| [16:43:59] | jheizer (jheizer!~jheizer@2601:d:3a00:370:5909:30e2:8ac4:753d) has quit (Ping timeout: 265 seconds) | |
| [16:44:34] | jheizer (jheizer!~jheizer@c-73-51-93-177.hsd1.il.comcast.net) has joined #mythtv | |
| [16:49:21] | jerryr (jerryr!~androirc@2600:1002:b02c:d5a9:0:2:b686:e01) has joined #mythtv | |
| [16:53:13] | jerryr: | dekarl: that might have been me. 6 to 10 seconds start up, frequently more, is normal for me. If there's something I can do to test defragmentarion on the table, let me know and I'll try to help. |
| [17:03:36] | dekarl: | jerryr: you can defragement with the following SQL query. should be run when no recording takes places trashes the database disk "ALTER TABLE `recordedseek` ORDER BY chanid, starttime;" |
| [17:04:40] | dekarl: | stuarta, InnoDB works differently. it sorts by primary key by default. So a primary key that groups everything for one recording together would be good. (e.g. the new recordedid without other fields would be enough) |
| [17:05:48] | dekarl: | which means a primary key CA in addition to a secondary key ABC may improve perfomance a lot, depending on the table |
| [17:07:38] | dekarl: | e.g. for a future program table with a unique id per entry it may help to prepend that already unique key with day(start_time), channel to group by day and channel first |
| [17:09:54] | Jordack (Jordack!~Jordack@75-151-31-172-Michigan.hfc.comcastbusiness.net) has joined #mythtv | |
| [17:13:56] | jerryr: | dekarl: can you clarify "trashed the database disk"? Does that only happen if I reorder the table while a recording is in progress? Your sentence was missing a word or two. |
| [17:14:49] | dekarl: | jerryr: it will read/write the full datafile and its indices, sorting it in between |
| [17:17:04] | jerryr: | dekarl: I'll backup the db beforehand to be safe. I should be able to do this tonight. |
| [17:18:51] | dekarl: | jerryr: ty, looking forward to hear if it helps |
| [17:24:42] | jerryr: | dekarl: WAF metric will shoot up if it does. |
| [18:08:07] | andreaz (andreaz!~andre_000@p5DD1506E.dip0.t-ipconnect.de) has joined #mythtv | |
| [18:10:19] | dekarl: | how about these the defragmentation statements? http://paste.ubuntu.com/11436866/ groups guide data by start time / channel and file markup by file. I just randomly picked the three biggest tables. filemarkup is likely little gain as I don't think that there are lots of deletions going on that leave free space to be filled. |
| [18:11:30] | dekarl: | maybe recordedseek could be sorted by chanid,starttime, and type while at it. |
| [18:44:13] | jerryr (jerryr!~androirc@2600:1002:b02c:d5a9:0:2:b686:e01) has quit (Remote host closed the connection) | |
| [19:38:13] | ghoti (ghoti!~paul@hq.experiencepoint.com) has quit (Ping timeout: 264 seconds) | |
| [20:01:31] | ghoti (ghoti!~paul@hq.experiencepoint.com) has joined #mythtv | |
| [20:02:35] | dekarl (dekarl!~dekarl@mythtv/developer/dekarl) has quit (Read error: Connection timed out) | |
| [20:03:36] | dekarl (dekarl!~dekarl@p4FE84DD2.dip0.t-ipconnect.de) has joined #mythtv | |
| [20:03:39] | dekarl (dekarl!~dekarl@p4FE84DD2.dip0.t-ipconnect.de) has quit (Changing host) | |
| [20:03:39] | dekarl (dekarl!~dekarl@mythtv/developer/dekarl) has joined #mythtv | |
| [20:10:04] | dekarl: | tgm4883: superm1: has this been changed in the mythbuntu packaging? https://code.mythtv.org/trac/ticket/12439 |
| [20:10:16] | dekarl: | just hit it myself ;) |
| [20:11:08] | tgm4883: | we've not changed anything regarding that to my knokwledge |
| [20:14:04] | dekarl: | haha, I love Debian packaging... http://webapps-common.alioth.debian.org/draft . . . eter-fs-conf "where 3rd party ... can provide additional configuration information. " then "XXX this second part is not yet true. " |
| [20:14:26] | dekarl: | at least its documented ;) |
| [20:15:44] | dekarl: | looking at /etc/php5/apache2/conf.d/ it appears to be true now. just dropping a file containing "max_input_vars=BIGNUMBER" might work. |
| [20:16:28] | tgm4883: | dekarl: yes debian policy is amazing. it tells us things like we can specify a package to be for multiple releases then says that this functionality doens't exist yet either (or my favorite, when it doesn't tell youy that you can't do that yet and you ahve to go ask) |
| [20:17:53] | jheizer: | Major props to you packages guys. Packaging MobileMyth was a pain and now I don't want to do any updates or releases for fear that my scrips won't all work again. |
| [20:21:38] | tgm4883: | I'll let superm1 take all the credit for making the packaging. I just keep it updated |
| [20:23:50] | dekarl: | doh, the default appears to be 1000, with 16 input vars per channel thats a limit of 62 channels. not that much |
| [20:24:41] | tgm4883: | isn't mythweb going away soonish? |
| [20:31:14] | Jordack (Jordack!~Jordack@75-151-31-172-Michigan.hfc.comcastbusiness.net) has quit () | |
| [20:32:16] | dekarl: | tgm4883: http://paste.ubuntu.com/11438995/ works for me |
| [20:32:51] | dekarl: | tested on an up to date mythbuntu 14.04 LTS setup |
| [20:33:15] | tgm4883: | superm1: ^ |
| [20:35:07] | dekarl (dekarl!~dekarl@mythtv/developer/dekarl) has quit (Read error: Connection timed out) | |
| [21:11:15] | SteveGoodey (SteveGoodey!~steve@host109-159-59-143.range109-159.btcentralplus.com) has quit (Quit: Konversation terminated!) | |
| [21:11:58] | dekarl (dekarl!~dekarl@mythtv/developer/dekarl) has joined #mythtv | |
| [21:34:25] | amessina (amessina!~amessina@unaffiliated/amessina) has joined #mythtv | |
| [22:32:11] | superm1: | i'm fine with including that |
| [22:32:24] | superm1: | that's interesting that it just popped up out of nowhere though |
| [22:37:24] | skd5aner (skd5aner!~skd5aner@46.sub-70-198-71.myvzw.com) has joined #mythtv | |
| [22:40:00] | Tobbe5178 (Tobbe5178!~asdf@2001:2002:51e1:d8ee:5555:ee3f:5dd7:e2fc) has quit (Read error: Connection reset by peer) | |
| [23:24:03] | Roklobsta (Roklobsta!~Roklobsta@ppp118-209-112-237.lns20.mel4.internode.on.net) has quit (Quit: KVIrc 4.2.0 Equilibrium http://www.kvirc.net/) | |
| [23:24:23] | Roklobsta (Roklobsta!~Roklobsta@ppp118-209-112-237.lns20.mel4.internode.on.net) has joined #mythtv | |
| [23:51:23] | arescorpio (arescorpio!~arescorpi@168-45-16-190.fibertel.com.ar) has joined #mythtv | |
IRC Logs collected by
BeirdoBot.
Please use the above link to report any bugs.