Skip to content

Silveira Neto Posts

Elgg metadata performance issue

Published by Silveira on 2011-05-09

Elgg is a open source social networking framework.

In my Elgg instance (1.7.2) I have some entities with a metadata called tags. One entity can have zero, one or many tags. Tags are strings.

I’m trying to retrieve the last updated files with the tags tagA, tagB, … tagI in a profile widget using this query like this:

$options = Array (
    "types" => "object",
    "subtypes" => "file",
    "limit" => 10,
    "full_view" => FALSE,
    "order_by" => "time_updated DESC",
    "pagination" => FALSE,
    "metadata_name_value_pairs" => Array (
                Array("name" => tags, "value" => "tagA", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagB", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagC", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagD", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagE", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagF", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagG", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagH", "operands"=> "contains"),
                Array("name" => tags, "value" => "tagI", "operands"=> "contains")
    )
);
echo elgg_list_entities_from_metadata($options);

This is enough to put MySQL at 100% of processor use and make Elgg to stop.

I’m using phpMyAdmin to monitor the database and Wireshark to monitor the communication between Elgg and MySQL. I came to this SQL query:

SELECT COUNT(DISTINCT e.guid) AS total
FROM elgg_entities e
JOIN elgg_metadata n_table ON e.guid = n_table.entity_guid
JOIN elgg_metadata n_table1 ON e.guid = n_table1.entity_guid
JOIN elgg_metastrings msn1 ON n_table1.name_id = msn1.id
JOIN elgg_metastrings msv1 ON n_table1.value_id = msv1.id
JOIN elgg_metadata n_table2 ON e.guid = n_table2.entity_guid
JOIN elgg_metastrings msn2 ON n_table2.name_id = msn2.id
JOIN elgg_metastrings msv2 ON n_table2.value_id = msv2.id
JOIN elgg_metadata n_table3 ON e.guid = n_table3.entity_guid
JOIN elgg_metastrings msn3 ON n_table3.name_id = msn3.id
JOIN elgg_metastrings msv3 ON n_table3.value_id = msv3.id
JOIN elgg_metadata n_table4 ON e.guid = n_table4.entity_guid
JOIN elgg_metastrings msn4 ON n_table4.name_id = msn4.id
JOIN elgg_metastrings msv4 ON n_table4.value_id = msv4.id
JOIN elgg_metadata n_table5 ON e.guid = n_table5.entity_guid
JOIN elgg_metastrings msn5 ON n_table5.name_id = msn5.id
JOIN elgg_metastrings msv5 ON n_table5.value_id = msv5.id
JOIN elgg_metadata n_table6 ON e.guid = n_table6.entity_guid
JOIN elgg_metastrings msn6 ON n_table6.name_id = msn6.id
JOIN elgg_metastrings msv6 ON n_table6.value_id = msv6.id
JOIN elgg_metadata n_table7 ON e.guid = n_table7.entity_guid
JOIN elgg_metastrings msn7 ON n_table7.name_id = msn7.id
JOIN elgg_metastrings msv7 ON n_table7.value_id = msv7.id
JOIN elgg_metadata n_table8 ON e.guid = n_table8.entity_guid
JOIN elgg_metastrings msn8 ON n_table8.name_id = msn8.id
JOIN elgg_metastrings msv8 ON n_table8.value_id = msv8.id
JOIN elgg_metadata n_table9 ON e.guid = n_table9.entity_guid
JOIN elgg_metastrings msn9 ON n_table9.name_id = msn9.id
JOIN elgg_metastrings msv9 ON n_table9.value_id = msv9.id
WHERE (((msn1.string = 'tags'
         AND BINARY msv1.string = 'tagA'
         AND ((1 = 1)
              AND n_table1.enabled='yes'))
        AND (msn2.string = 'tags'
             AND BINARY msv2.string = 'tagB'
             AND ((1 = 1)
                  AND n_table2.enabled='yes'))
        AND (msn3.string = 'tags'
             AND BINARY msv3.string = 'tagC'
             AND ((1 = 1)
                  AND n_table3.enabled='yes'))
        AND (msn4.string = 'tags'
             AND BINARY msv4.string = 'tagD'
             AND ((1 = 1)
                  AND n_table4.enabled='yes'))
        AND (msn5.string = 'tags'
             AND BINARY msv5.string = 'tagE'
             AND ((1 = 1)
                  AND n_table5.enabled='yes'))
        AND (msn6.string = 'tags'
             AND BINARY msv6.string = 'tagF'
             AND ((1 = 1)
                  AND n_table6.enabled='yes'))
        AND (msn7.string = 'tags'
             AND BINARY msv7.string = 'tagG'
             AND ((1 = 1)
                  AND n_table7.enabled='yes'))
        AND (msn8.string = 'tags'
             AND BINARY msv8.string = 'tagH'
             AND ((1 = 1)
                  AND n_table8.enabled='yes'))
        AND (msn9.string = 'tags'
             AND BINARY msv9.string = 'tagI'
             AND ((1 = 1)
                  AND n_table9.enabled='yes'))))
  AND ((e.TYPE = 'object'
        AND e.subtype IN (1)))
  AND (e.site_guid IN (1))
  AND ((1 = 1)
       AND e.enabled='yes')

A special thanks to the SQL formatting service SQLformat.appspot.com.

When I try to retrieve up to 4 tags I can get a response fairly quickly but more than that the response time seems to exponentially grow.

Any clues?

The workaround I’ll try now is to retrieve entities by each tag and them sort by myself.

Old City Hall Richmond

Published by Silveira on 2011-04-12

Those are two pictures that I took at Old City Hall in Richmond, Virginia, USA.

I really enjoyed the second one. The composition of the triangles of trees (Asymmetrical Balance), the pole (Rule of Thirds) and the curved sidewalk that makes a path for the eyes.

But the picture wont fit in the proportion for my wallpaper so I put the two together using Gimp making the necessary adjustments to fit everything together in a 1920:1080 ratio picture.

We can say there is is one tree representing each season: the green at left as Summer, the white cherry blossom as Spring, the one without leaves as Winter and the last with brown leaves as Autumn. Unfortunately the sidewalk and triangles lose the original effect but the pole in the middle created an interesting one.

The high resolution picture is available in my Flickr. I used a Canon Powershot G10.

Android screen height and width

Published by Silveira on 2011-03-12 
Context ctx = getContext();
Display display = ((WindowManager)ctx.getSystemService(ctx.WINDOW_SERVICE)).getDefaultDisplay();
int width = display.getWidth();
int height = display.getHeight();

Yes, there are easier ways to retrieve the screen width on Android but there are cases that this long code is the only solution. You may already have the Context. WindowManager or the Display and so it would be smaller, but this code is more general.

PHP: array, all elements but first

Published by Silveira on 2011-03-09 
$bric = array("Brazil", "Russia", "India", "China"); 
$ric = $bric; // array copy
$br = array_shift($ric); // left shift at $ric. $br stores "Brazil" 
print_r($bric); // $bric remains the same
print_r($ric); // $ric lost "Brazil"

Output:

Array
(
    [0] => Brazil
    [1] => Russia
    [2] => India
    [3] => China
)
Array
(
    [0] => Russia
    [1] => India
    [2] => China
)

Reference: PHP array_shift at php.net.

DIY recycle your old tv

Published by Silveira on 2011-02-20

When I came here to this room I found this old big tv laying around. Old but perfectly functional.

First I put a tv cable on it with hundreds of channels but after one month I realized that mostly of them are crap and I was watching everything on my laptop (mostly shows from Netflix and BitTorrent). Obviously I wanted to connect my laptop video output on it.  As an old television it doesn’t have any HDMI port. It have a RCA video and audio ports so after some research I bought a VGA-to-RCA converter. Besides watch movies on it there is a lot of cool things I could do with it to decorated the room or create a environment while we are jamming.

In this article I’ll show how to create a fireplace.

You will need

Getting a high definition fireplace video

You can search in Youtube for high definition fireplace videos.

There is a lot of ways to extract the videos from Youtube and it’s not the point explain it here but I can say one the best ways today is using JDownloader. Using it you can extract sound and video in any available resolution.

Choose a fireplace video and download the highest resolution available (usually 720p). Here I stored it at /home/silveira/Videos/fireplace_hd.flv.

Screen layout

I’m using the TV as a second display in the right. The width of my laptop monitor is 1920.

Creating a shortcut

First, right click in the upper panel of Gnome and select Add to Panel.  Select the Custom Application Launcher option.

Let’s fill it like this:

On the command put:

/usr/bin/mplayer /home/silveira/Videos/fireplace_hd.flv -nosound -loop 0 -geometry +1900+0 -fs

Explaning:

  • /usr/bin/mplayer the mplayer full path (probably you have the same path but you can try “whereis mplayer” in a terminal to find it).
  • /home/silveira/Videos/fireplace_hd.flv as I said, where I put the video.
  • -nosound no sound. 🙂 Usually I have some music playing so I don’t want any fire sound. But if you want  the fire crackling sound omit this parameter.
  • -loop 0 the video I took have 10 minutes. If I put -loop 6 it would loop for 1 hour. Zero means infinite times.
  • -geometry +1900+0 you can specify the size of the screen and position but here I’m justing saying I want it on horizontal position 1900 (bigger than my first screen) so it goes to the tv.
  • -fs fullscreen

Now drink the wine and cheese in front of your low carbon footprint fireplace.  😉

Bonus

Now you can use the same idea and make an virtual aquarium, ant farm, an interactive art piece or anything!