Monday, March 9, 2015

Avoiding duplicate partners

There is hope that SimilarPartners now produces usable results. To be verified on a production site.

The previous implementation did not use an additional table. It simply added a field phonetic_words (containing the phonetic words separated by spaces) and used an SQL statement constructed .

If we then had two rows a and b, a with phonetic_words being ‘BAR BART FOO’ and b with phonetic_words being ‘BING BARN FOO’.

The find similar rows for a, it used the following WHERE clause using all possible combinations of two of a’s phonetic words:

WHERE ('BAR'  in pw AND 'BART' in pw)
   or ('BAR'  in pw AND 'FOO' in pw)
   or ('BART' in pw AND 'FOO' in pw)

But this also (wrongly) matched our b row because ‘BAR’ matches ‘BARN’. AFAIK the LIKE clause in SQL does not have regular expression syntax.

Note that MySQL has a SOUNDEX function, but this implements only the original Soundex algorithm and is not usable for non-english languages.

These are reasons why we now use an additional table defined by the lino.modlib.dupable_partners.models.Word model.

The implementation was rather tricky. I splitted lino.modlib.dedupe into a mixin lino.mixins.dupable and a new plugin lino.modlib.dupable_partners.

All this work has several advantages:

  • there is hope that it works now.

  • it is now possible to have more than one dupable model in an application. For example both partners and products might be dupable, and each of them must get its own table of phonetic words.

  • applications can chose to not install lino.modlib.dupable_partners even if their partners model inherits from DupablePartner.

Participants d’un rendez-vous

Identifié et résolu deux problèmes qui pourraient être l’explication du ticket #123. En général le phénomène vient quand Lino n’a pas crée d’entrée dans le panneau “Participants” du rendez-vous en question.

  • Quand on crée un rendez-vous manuel avec un bénéficiaire, et quand on ne spécifie pas de bénéficiaire dans le dialogue d’insertion (mais plus tard dans le détail en remplissant le champ “Bénéficiaire”), also Lino ne remplissait pas les participants.

  • Quand un rendez-vous a au moins un participant, Lino ne touche plus à la liste des participants. Cette règle était trop simpliste. Par exemple quand on modifie le bénéficiaire d’un rendez-vous existant. Lino demande maintentant.

MySQL performace

Here is another MySQLTuner report:

>>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: django
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 1M (Tables: 162)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'django'@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 10h 24m 20s (19M q [64.568 qps], 61K conn, TX: 9B, RX: 13B)
[--] Reads / Writes: 66% / 34%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (3% of installed RAM)
[OK] Slow queries: 0% (5K/19M)
[OK] Highest usage of available connections: 10% (16/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.6M
[OK] Key buffer hit rate: 100.0% (13M cached / 921 reads)
[OK] Query cache efficiency: 99.4% (18M cached / 18M selects)
[!!] Query cache prunes per day: 7763
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 3K sorts)
[!!] Joins performed without indexes: 871
[OK] Temporary tables created on disk: 19% (2K on disk / 12K total)
[OK] Thread cache hit rate: 97% (1K created / 61K connections)
[!!] Table cache hit rate: 4% (141 open / 2K opened)
[OK] Open file limit used: 24% (249/1K)
[OK] Table locks acquired immediately: 99% (228K immediate / 228K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_open_cache (> 400)