Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Horimoto Yasuhiro
    @komainu8

    In my opinion, there are two methods of backup.

    Restore of these methods is faster than mysqldump.

    1. File backup

    We copy and compress Database files as below example.

    • backup
    % cd /var/lib
    % tar -zcvfp backup.tar.gz mysql/*
    • restore
    % cd /var/lib
    % tar -zxvfp backup.tar.gz
    1. We use grndump and groonga command

    We backup mrn files with grndump command as below.

    • backup
    % grndump /var/lib/mysql/test.mrn > mroonga_dump.grn 
    % tar -zcvf mroonga_dump.tar.gz

    We restore mrn file with groonga command as below.

    • restore
    % tar -zxvf mroonga_dump.tar.gz
    % cat mroonga_dump.grn | groonga /var/lib/mysql/test.mrn

    Backup files are smaller than file backup in this method.
    However, We must install rroonga and groonga.

    Target of backup of this method only mroonga files.

    Horimoto Yasuhiro
    @komainu8

    I'm sorry.

    grndump command is deprecated.
    We should use dump command instead of grndump command as below example.

    • backup
    % groonga /var/lib/mysql/test.mrn dump > mroonga_dump.grn
    • restore
    % cat mroonga_dump.grn | groonga /var/lib/mysql/test.mrn

    We must install groonga.
    Target of backup of this method only mroonga files.

    JiNNeY74
    @JiNNeY74
    thanks a lot!!
    JiNNeY74
    @JiNNeY74
    -rw-r----- 1 mysql mysql      4096 Jan 24 13:32 EC_GW.mrn
     -rw-r----- 1 mysql mysql  17051648 Jan 24 13:32 EC_GW.mrn.0000000
     -rw-r----- 1 mysql mysql   8392704 Jan 23 19:35 EC_GW.mrn.0000100
     -rw-r----- 1 mysql mysql   4468736 Jan 23 19:35 EC_GW.mrn.0000101
     -rw-r----- 1 mysql mysql  12857344 Jan 23 19:35 EC_GW.mrn.0000102
     -rw-r----- 1 mysql mysql   4198400 Jan 23 19:35 EC_GW.mrn.0000103
     -rw-r----- 1 mysql mysql  16842752 Jan 23 19:24 EC_GW.mrn.000010A
     -rw-r----- 1 mysql mysql   8437760 Jan 23 19:24 EC_GW.mrn.000010B
     -rw-r----- 1 mysql mysql  14454784 Jan 23 19:24 EC_GW.mrn.000010C
     -rw-r----- 1 mysql mysql  20975616 Nov 28 18:07 EC_GW.mrn.000010C.c
     -rw-r----- 1 mysql mysql  16842752 Jan 23 19:35 EC_GW.mrn.000010D
     -rw-r----- 1 mysql mysql   8437760 Jan 23 19:24 EC_GW.mrn.000010E
     -rw-r----- 1 mysql mysql  14192640 Jan 23 19:35 EC_GW.mrn.000010F
     -rw-r----- 1 mysql mysql  16781312 Nov 28 18:09 EC_GW.mrn.000010F.c
     -rw-r----- 1 mysql mysql   1048576 Jan 24 13:32 EC_GW.mrn.001
     -rw-r----- 1 mysql mysql  12632064 Jan 24 13:32 EC_GW.mrn.conf
     -rw-r----- 1 mysql mysql   4468736 Jan 24 13:32 EC_GW.mrn.options
    we have many file. how to this case ?
    JiNNeY74
    @JiNNeY74
    and innodb wrapper mode -> ENGINE=Mroonga DEFAULT CHARSET=utf8 COMMENT='ENGINE "INNODB"'
    JiNNeY74
    @JiNNeY74
    @kou kou sama answer me!! pls~~
    Sutou Kouhei
    @kou
    mariabackup and DISABLE KEYS/ENABLED KEYS after restore will be the fastest way.
    In wrapper mode case.
    salmagomaa
    @salmagomaa
    Hello,
    I wonder if any body can tell me how can I use pgroonga for arabic dictionary. For example I need the search for word "أحمد" to fetch both "أحمد" and "احمد" i.e. the different forms of the letter "أ" should be considered
    Horimoto Yasuhiro
    @komainu8

    I have researched how to ignore different of those characters. However, PGroonga's current normalizer can't normalize those for إ and ا have handled as different character in Unicode.

    We need implement of a dedicated normalizer or normalizer's option for actualize this feature.

    So, could you create new issues in PGroonga on GitHub and you write detail specification of this feature to that one, please?

    salmagomaa
    @salmagomaa
    @komainu8 Thank you! and
    I already created an issue two days ago pgroonga/pgroonga.github.io#49
    Horimoto Yasuhiro
    @komainu8

    @salmagomaa Thank you for creating that.

    However, pgroonga.github.io is a repository for documentation.

    So, I recommend that create this issue in issues of pgroonga repository.

    Kentaro Hayashi
    @kenhys

    @Oniry
    The document about indexes with weight is not available yet, but if you have interested in this feature, the following example will help you to understand it.

    table_create Bigram TABLE_PAT_KEY ShortText \
      --default_tokenizer TokenBigramSplitSymbolAlpha \
      --normalizer NormalizerAuto
    
    table_create Diaries TABLE_HASH_KEY ShortText
    column_create Diaries title COLUMN_SCALAR ShortText
    column_create Diaries content COLUMN_SCALAR Text
    
    column_create Bigram titles COLUMN_INDEX|WITH_POSITION  Diaries title
    column_create Bigram contents COLUMN_INDEX|WITH_POSITION  Diaries content
    
    load --table Diaries
    [
      {
        "_key": "2013-02-06",
        "title": "groonga",
        "content": "I found it that is a fast fulltext search engine!"
      },
      {
        "_key": "2013-02-07",
        "title": "mroonga",
        "content": "I found mroonga that is a MySQL storage engine to use groonga!"
      },
    ]

    Then execute queries like this:

    select Diaries \
      --match_columns 'Bigram.titles * 10 || Bigram.contents' \
      --query 'groonga' \
    --output_columns 'title,_score' --sort_keys -_score --output_pretty yes

    ┌───────┬──────┐
    │title │_score│
    ├───────┼──────┤
    │groonga│10 │
    │mroonga│1 │
    └───────┴──────┘

    select Diaries \
      --match_columns 'Bigram.titles || Bigram.contents' \
      --query 'groonga' \
    --output_columns 'title,_score' --sort_keys -_score --output_pretty yes

    ┌───────┬──────┐
    │title │_score│
    ├───────┼──────┤
    │groonga│1 │
    │mroonga│1 │
    └───────┴──────┘

    If groonga is contained title and content column, it became to the same score without * 10 weight.

    @Oniry I hope it will help.
    anthonydafc
    @anthonydafc

    Hello, i'm trying to setup pgroonga in postgres 11, and it is the second time i get the same error when creating extension : ERROR: ERREUR: n'a pas pu charger la bibliothèque « C:/Program Files/PostgreSQL/11/lib/pgroonga.dll » : unknown error 127

    SQL state: XX000

    any idea
    anthonydafc
    @anthonydafc
    Another question concernng full text search. can we use &~ operator with scoring ? thanks for your replies
    Sutou Kouhei
    @kou
    How did you install PGroonga?
    &~ supports score.
    anthonydafc
    @anthonydafc
    i downloaded the version for postgres 11 x64, pasted folders to postgresql directory and did create extension pgroonga
    Sutou Kouhei
    @kou

    Umm. It's too rough instruction. I couldn't reproduce your case with the instruction.
    I try the following and it works well:

    1.Install PostgreSQL 11.3 by installer: https://www.enterprisedb.com/thank-you-downloading-postgresql?anid=1256619

    1. Download PGroonga binary for PostgreSQL 11: https://github.com/pgroonga/pgroonga/releases/download/2.1.9/pgroonga-2.1.9-postgresql-11.2-1-x64.zip
    2. Extract the PGroonga binary to C:\Program Files\PostgreSQL\11 (I needed to enable administrator permission)
    3. I run psql from startup menu
    4. Connect to postgres database with postgres user
    5. Create pgroonga_test database by CREATE DATABASE pgroonga_test;
    6. Change database by \c pgroonga_test
    7. Install PGroonga by CREATE EXTENSION pgroonga; (It doesn't report any error)
    8. Run the following SQL in tutorial https://pgroonga.github.io/tutorial/
    CREATE TABLE memos (
      id integer,
      content text
    );
    CREATE INDEX pgroonga_content_index ON memos USING pgroonga (content);
    INSERT INTO memos VALUES (1, 'PostgreSQL is a relational database management system.');
    INSERT INTO memos VALUES (2, 'Groonga is a fast full text search engine that supports all languages.');
    INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga as index.');
    INSERT INTO memos VALUES (4, 'There is groonga command.');
    SET enable_seqscan = off;
    SELECT * FROM memos WHERE content &@ 'engine';
    --  id |                                content                                 
    -- ----+------------------------------------------------------------------------
    --   2 | Groonga is a fast full text search engine that supports all languages.
    -- (1 row)

    So, it will be your environment specific issue.

    anthonydafc
    @anthonydafc
    @kou ok works better with latest version of poostgres 11 (i was using 11.1)
    anthonydafc
    @anthonydafc
    @kou SELECT , pgroonga_score(tableoid, ctid) AS score
    FROM score_memos
    where content &~ '.
    groonga'
    if a use this it will give me 0 scorer on every line found
    Sutou Kouhei
    @kou
    anthonydafc
    @anthonydafc
    same thing with seqscan disabled
    Sutou Kouhei
    @kou
    Please show the result of EXPLAIN ANALYZE VERBOSE SELECT ...;.
    anthonydafc
    @anthonydafc
    Seq Scan on public.score_memos (cost=10000000000.00..10000000657.70 rows=1 width=44) (actual time=0.087..0.107 rows=3 loops=1)
    Sutou Kouhei
    @kou
    Please show whole result. Don't omit anything.
    anthonydafc
    @anthonydafc
    Sorry
    "Seq Scan on public.score_memos (cost=10000000000.00..10000000657.70 rows=1 width=44) (actual time=0.087..0.107 rows=3 loops=1)"
    " Output: id, content, pgroonga_score(tableoid, ctid)"
    " Filter: (score_memos.content &~ '.*groonga'::text)"
    " Rows Removed by Filter: 1"
    "Planning Time: 0.162 ms"
    "Execution Time: 0.152 ms"
    Sutou Kouhei
    @kou
    Please show the output of the following:
    \d score_memos
    anthonydafc
    @anthonydafc
    Sorry but how does that work ?
    Sutou Kouhei
    @kou
    It's a psql command.
    anthonydafc
    @anthonydafc

    base_roonga_test-# \d score_memos
    Table ½ public.score_memos ╗
    Colonne | Type | Collationnement | NULL-able | Par dÚfaut
    ---------+---------+-----------------+-----------+------------
    id | integer | | not null |
    content | text | | |
    Index :
    "score_memos_pkey" PRIMARY KEY, btree (id)
    "pgroonga_score_memos_content_index" pgroonga (content)

    base_roonga_test-#

    Sutou Kouhei
    @kou
    \di pgroonga_score_memos_content_index
    anthonydafc
    @anthonydafc
                               Liste des relations
    SchÚma | Nom | Type | PropriÚtaire | Table
    --------+------------------------------------+-------+--------------+-------------
    public | pgroonga_score_memos_content_index | index | postgres | score_memos
    (1 ligne)
    Sutou Kouhei
    @kou
    select * from pg_catalog.pg_indexes where indexname = 'pgroonga_score_memos_content_index';
    anthonydafc
    @anthonydafc
    "public" "score_memos" "pgroonga_score_memos_content_index" "CREATE INDEX pgroonga_score_memos_content_index ON public.score_memos USING pgroonga (content)"
    Sutou Kouhei
    @kou

    I got it.
    You need to use pgroonga_text_regexp_ops_v2 operator class https://pgroonga.github.io/reference/#text-regexp-ops-v2 by searching with regular expression with index.

    CREATE INDEX pgroonga_score_memos_content_index ON public.score_memos USING pgroonga (content pgroonga_text_regexp_ops_v2);

    See also: https://pgroonga.github.io/reference/operators/regular-expression-v2.html#usage

    anthonydafc
    @anthonydafc
    @kou Works great. Many thanks
    anthonydafc
    @anthonydafc

    I have a question concerning the score. If i use an inner join i get a sql message saying : ERROR: ERREUR: la colonne « tableoid » n'existe pas
    LINE 1: select f.fid_recherchefichiers, pgroonga_score(tableoid, cti...
    ^
    HINT: Il existe une colonne nommée « tableoid » pour la table « p » mais elle ne peut pas être référencée dans cette partie de la requête.

    SQL state: 42703
    Character: 48

    anthonydafc
    @anthonydafc
    the tableoid doesn't exist
    Sutou Kouhei
    @kou
    Could you open an issue from here https://github.com/pgroonga/pgroonga/issues and write full SQLs (CREATE TABLE, CREATE INDEX, INSERT, SELECT, ...) to reproduce the case?
    anthonydafc
    @anthonydafc
    ok, have you ever had issues concerning lock tables with groonga ???