Messages

46

Users / Re: Huge query in pluto_media : taking my disk down to crawling

« on: September 21, 2013, 09:41:37 pm »

explain on my side

Code: [Select]

+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
| id | select_type | table          | type   | possible_keys      | key     | key_len | ref                                     | rows | Extra       |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
|  1 | PRIMARY     | <derived2>     | ALL    | NULL               | NULL    | NULL    | NULL                                    | 3017 |             |
|  1 | PRIMARY     | File           | eq_ref | PRIMARY,FatGroupBy | PRIMARY | 4       | s.PK_File                               |    1 |             |
|  2 | DERIVED     | File           | index  | Path,Filename      | PRIMARY | 4       | NULL                                    | 3231 | Using where |
|  2 | DERIVED     | Bookmark       | ref    | FK_File            | FK_File | 5       | pluto_media.File.PK_File                |    1 |             |
|  2 | DERIVED     | File_Attribute | ref    | PRIMARY,FK_File    | FK_File | 4       | pluto_media.File.PK_File                |    2 | Using index |
|  2 | DERIVED     | Attribute      | eq_ref | PRIMARY            | PRIMARY | 4       | pluto_media.File_Attribute.FK_Attribute |    1 |             |
|  2 | DERIVED     | LongAttribute  | ref    | FK_File            | FK_File | 5       | pluto_media.File.PK_File                |    1 |             |
|  2 | DERIVED     | Picture_File   | ref    | FK_File            | FK_File | 4       | pluto_media.File.PK_File                |    1 |             |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
8 rows in set (0.10 sec)


47

Users / Re: Huge query in pluto_media : taking my disk down to crawling

« on: September 21, 2013, 09:22:11 pm »

Thom

Did some research on that explain plan you posted and I think the problem was the group-by clause that groups by a bunch of dependent fields that aren't indexed. If we group on the PK of File and then join back to file to get the fields we need it seems to take care of that. In fact, it improves my query by quite a dramatic amount. Does anyone else want to try this?

Code: [Select]

SELECT File.PK_File, File.Path, File.Filename, File.Inode,
  s.CurrentDbAttrDate, s.CurrentDbAttrCount, s.HasAttributes,
  File.AttrDate AS OldDbAttrDate, File.AttrCount AS OldDbAttrCount,
  File.ModificationDate AS OldFileDate, File.Source
FROM
(SELECT PK_File,
  greatest(
    IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod),
    IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod),
    IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod),
    IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod),
    CAST('0000-00-00 00:00:00' AS DATE)
  ) As CurrentDbAttrDate,
  (1000000 * COUNT(Bookmark.PK_Bookmark) + 10000 * COUNT(Attribute.PK_Attribute) +
   100 * COUNT(LongAttribute.PK_LongAttribute) + COUNT(Picture_File.FK_Picture))
  AS CurrentDbAttrCount,
  COUNT(Attribute.PK_Attribute)+COUNT(LongAttribute.PK_LongAttribute) AS HasAttributes
FROM
File
LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_File
LEFT JOIN Picture_File ON Picture_File.FK_File = PK_File
  WHERE 1 = 1 AND (Path LIKE '/home/public/data%' OR (Path = '/home/public' AND Filename = 'data' )  OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )  OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )  OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' AND Filename = 'data' )  )  AND Missing = 0
GROUP BY PK_File) s
inner join File on s.PK_File=File.PK_File;


48

Users / Re: Huge query in pluto_media : taking my disk down to crawling

« on: September 21, 2013, 09:19:41 pm »

Code: [Select]

mysql> select count(*) from Picture_File;
+----------+
| count(*) |
+----------+
|      154 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from File;
+----------+
| count(*) |
+----------+
|     4333 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from File_Attribute;
+----------+
| count(*) |
+----------+
|    11794 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from LongAttribute;
+----------+
| count(*) |
+----------+
|       22 |
+----------+
1 row in set (0.00 sec)

Yes, you have a lot more media than I do (and more pictures!)

49

Users / Re: Huge query in pluto_media : taking my disk down to crawling

« on: September 21, 2013, 02:22:00 pm »

Hmmm... don't have time to check if the data is exactly the same but it is certainly the same number of rows and results in about a 25% speed improvement for me... perhaps someone else can try the following query rewrite to compare with the original:

Code: [Select]

SELECT PK_File, Path, Filename, INode,
  greatest(
    IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod),
    IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod),
    IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod),
    IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod),
    CAST('0000-00-00 00:00:00' AS DATE)
  ) As CurrentDbAttrDate,
  (1000000 * COUNT(Bookmark.PK_Bookmark) + 10000 * COUNT(Attribute.PK_Attribute) +
   100 * COUNT(LongAttribute.PK_LongAttribute) + COUNT(Picture_File.FK_Picture))
  AS CurrentDbAttrCount,
  COUNT(Attribute.PK_Attribute)+COUNT(LongAttribute.PK_LongAttribute) AS HasAttributes,
  AttrDate AS OldDbAttrDate, AttrCount AS OldDbAttrCount, ModificationDate AS OldFileDate,
  Source
FROM
File
LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_File
LEFT JOIN Picture_File ON Picture_File.FK_File = PK_File
  WHERE 1 = 1 AND (Path LIKE '/home/public/data%' OR (Path = '/home/public' AND Filename = 'data' )  OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )  OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )  OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' AND Filename = 'data' )  )  AND Missing = 0
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate;


50

Users / Re: Huge query in pluto_media : taking my disk down to crawling

« on: September 21, 2013, 01:02:36 pm »

To give you an idea of how crazy of a rabbit hole, this is.. I've spent the last three and a half hours with a friend well versed in SQL. We were able to compact the query, but the results actually wound up being slower than when we started.

I could REALLY use a database designer's help here.

-Thom

Thom... did you have any luck there? I ran that query on my DB and I got

3017 rows in set (0.40 sec)

Obviously my media db is pretty modest but that's pretty snappy all the same... . Bulek, how big is yours and how long does it take if you run the query manually from the mysql prompt?

51

Users / HDPVR 1212 Settop box capture and streaming

« on: September 19, 2013, 09:01:37 am »

Branched this off the FIOS thread to prevent from going off topic. In there, Thom gave some nice pointers regarding using the HDPVR to capture the video stream from a settop box for TV viewing or redirect to other MDs.

http://forum.linuxmce.org/index.php/topic,13316.msg97433.html#msg97433

Just wanted to follow up that I finally got this working (more or less) on my setup which I'm pretty pleased about.

Some pointers that I will wikify once I am more confident-

I flashed the HDPVR to the latest firmware. You have to do this on a windows machine so you need to suspend your conscience and principles temporarily. Basically I had the HDPVR plugged in (usb) to the windows laptop and I downloaded and installed the latest driver from Hauppauge (circa March 2012) on the laptop. I think this process updates the firmware of the plugged-in HDPVR while it's going.

HDPVR was recognised and installed as device automagically by LMCE (prior to this but I assume it would be unaffected by firmware upgrade) but I just needed to go back to Sarah and sort out the AV connections. This was a bit of a black art which I'll go through again and try to document but basically you need -
- Device Template for your Settop box with the outputs you want to connect to the HDPVR (in my case composite video and L,R analog audio)
- HDPVR device detected/installed.
Then in AV setup wiz, you ensure you add the Settop box device and then you somehow indicate that the appropriate input of the HDPVR is connected to the appropriate output of the Settop box.
As Thom says above, this creates a media scenario that basically plays the captured stream from the HDPVR and then puts your orbiter in control of the stb (however you are controlling that).

So you can use this scenario on any MD which is pretty cool. I have the HDPVR connected to one of my MDs in the TV room where the STB is and I can now watch TV on that MD (nicely wrapped in LMCE so any notifications etc still come through and I don't have to switch source on the TV) or on any other MD.

Glitches and issues -
1. Latest firmware of HDPVR has nasty colour saturation bug
Resolvable by running the following command on the MD/Core where the HDPVR is connected-

Code: [Select]

sudo /usr/bin/v4l2-ctl --verbose -d /dev/video0 --set-ctrl brightness=0x80 --set-ctrl contrast=0x40 --set-ctrl hue=0xf --set-ctrl saturation=0x40 --set-ctrl sharpness=0x80
Replace /dev/video0 with the node created for your HDPVR.
Need to work out how to stick this in as a default... possibly in the HDPVR code somewhere. Shouldn't cause problems with older firmware.
2. Long latency (5-7 seconds) delay between capture and view. Which is fine once you're viewing but you have to wait this long for any command to the STB to reflect on screen. Ouch. I think this may be due to the MD doing the capture/display battling a bit with resources. Strangely, when I stream to another MD and just use this MD for capture the delay is shorter. Haven't measured but possibly half as long.... almost usable. Which may support the resource issue-  playing a stream and capturing one might just be too much for the little fella.
3. Occasional x-server crash on capture MD. Caused when I tried to play a normal video on Capture MD while capturing and streaming TV from STB to another MD. Possibly related to resources as alluded to above.

Note that my capture MD is a Zotac AD04. 2Gb memory dual core AMD-E450 proc. Radeon ATI video. Not a heavy-weight and the X-server is not fantastic with this and displays some other issues.


Still, I'm pretty pleased with this progress and I hope this info helps someone else.

Next steps- 

• probably need to swap out this MD with a more potent machine with properly supported Nvidia card to see if that helps with the issues.
• Try the whole lot on a 12.04 experimental server - still to setup
• Get my settop box IR code blasting working. Not sure why, but I can record codes from the remote but the STB ignores them when blasted back. Works with my TV but STB not interested
• Sort out the colour saturation thing as code that runs with HDPVR device start

52

Users / Re: CEC - Anyone (for Denon AVR 1909)

« on: September 18, 2013, 01:58:49 pm »

I've found an Onkyo TX-NR509. It seems all the NR series have ethernet and I have to presume that where there are Android and iOS control apps there must be a protcol to control via ethernet. Pricey (because it's new) but nothing wrong with Onkyo....

53

Users / Re: CEC - Anyone (for Denon AVR 1909)

« on: September 18, 2013, 08:11:40 am »

I seriously would recommend getting an older model Denon 23xx, e.g. a 2308, 2309, 2310, 2311... as these can be had for excellent prices.

I haven't used HDMI-CEC in any context within LinuxMCE, and can't even begin to tell you how to set it up. Hari wrote the Kwikwai driver, so he'd be able to tell you...but...

-Thom

Thanks for the thoughts Thom.... Can't find anything available locally. Sadly, it's very difficult to buy off e-bay in South Africa for some reason (most of the sellers have "may not ship to South Africa") so we're not exactly spoilt for choice. I really don't intend to get unsupported hardware... I don't want to fight the things but it comes down to availability so often.

I'll keep looking...

54

Users / CEC - Anyone (for Denon AVR 1909)

« on: September 17, 2013, 02:37:48 pm »

Hi all...

Been looking out for a bargain AV receiver for a while and there's a pretty good deal on a Denon AVR 1909. I know it's not the newest model out there and doesn't sadly, have IP or RS232 control, but I think in all other areas it will do the job I require -

• upscale from analog video - for my old cable box input
• 3 hdmi inputs should be plenty

My only concern is on the control side. I see it is supposed to work with CEC so I was thinking of getting one of those pulse-eight injectors to turn it on and off, switch inputs and change volume.

Has anyone had any luck with those?

I really want all my hardware to be controllable via my orbiters/remotes. TV just needs to go on and off which I can do with IR blasting. But volume and input switching needs to happen on the amp so I need those functions...

thoughts, advice, criticism?

55

Users / Re: Fios and LMCE

« on: September 17, 2013, 08:39:38 am »

Thank Thom, will give it a go....

56

Users / Re: Fios and LMCE

« on: September 16, 2013, 09:06:15 am »

... outputs, which can be passed through a Hauppauge HD-PVR (the original 1212 unit), to distribute the signal to other media directors, and to allow Orbiter's UI to be laid over the top.

Thom.... regarding this option above... I was under the impression you still needed to use Myth to capture the HD-PVR stream... is that the case or is there another way to capture this stream and redistribute as you describe? (Cos I'm battling with Myth ;-)

57

Users / Re: Kickstarter Campaign to fund a programmer?

« on: September 06, 2013, 10:17:57 am »

Hi.

I am starting this thread to gauge interest, and promote discussion.

If I were to start a KickStarter campaign, to fund $50,000, for me to work wholly on LinuxMCE for a year, would there be enough interest? You know me. You know what I can do. What are your thoughts?

-Thom

Thom, I think it's a great idea and I'd like to see it happen.
Also can't add a packet (my currency is fast becoming worthless) but will definitely contribute.

The tricky thing will be deciding what to focus on. You will obviously have ideas yourself but everyone else (esp those contributing) will have lots of input too and it might be tricky getting consensus. Do you only take suggestions from contributors? Does the size of the contribution affect the weight of the suggestion? Tricky, but not impossible I'm sure.
Regarding focus - I would add the high-level and rather generic comment that whatever is focussed on should be something that can broaden the user base as much as possible... because more users equals more potential funding, more pressure we can exert on hardware suppliers to comply etc etc. More clout in general. Identifying those things and agreeing on them is another story ;-)

58

Users / Re: Screensaver - can't exit if it kicks in while using firefox

« on: August 29, 2013, 08:19:03 am »

a CMD_Keep_Screen_On needs to be fired to the closest onscreen orbiter. (the closest on-screen orbiter). This can be done in the General Info Plugin.

I'll leave this as an exercise, for a bit, and implement it if nobody takes it up

-Thom

I'm on it.

I discovered last night that the same happens with youtube so it does make sense to do this, I believe.
Questions regarding exercise:

• Do I need to setup a development environment?
• Should I rather do it on 12.04? (I'm on 10.04 but have been thinking of setting up a 12.04 test box)

59

Users / Re: Following upgrade, media on MD starts at maximum volume

« on: August 29, 2013, 08:14:32 am »

Please edit /usr/pluto/bin/SetupAudioVideo.sh and hash out "alsactl store" on lines 166 and 170. Also /usr/pluto/bin/AVWizard_Run.sh lines 437 and 476 for good measure... then reboot. Should fix this.

Thanks! Will check this out and let you know.

60

Users / Re: Screensaver - can't exit if it kicks in while using firefox

« on: August 28, 2013, 10:31:40 am »

You guys probably already know this.  But you can set the time out in the media directors page in web admin.  I set mine to 30000 or 8 hours. 

I didn't know that - thank you, that is helpful. And it sort of makes sense to not time out if you're in UI2 and you generally have a "background" screensaver anyway...