Jak najlepiej przechowywać ngramy Google w bazie danych?

Pobrałem google onegrams kilka dni temu i to już ogromna ilość danych. Wstawiłem pierwszy z 10 pakietów do mysql, a teraz mam 47 milionów baz danych.

Zastanawiam się, jak najlepiej przechowywać ngramy Google w bazie danych. To znaczy, jeśli nie używasz onegramów, ale np. Twogramów lub trójgramów, ilość będzie znacznie większa. Czy mogę przechowywać 500 milionów rekordów w jednej bazie danych i pracować z nią, czy powinienem podzielić ją na różne tabele?

Po ilu rekordach należy go podzielić i jak najlepiej podzielić (biorąc pod uwagę, że twogramy zawierają 100 plików, a zatem prawdopodobnie około 5 miliardów rekordów)? Czy zaleca się stosowanie partycjonowania poziomego MySQL, czy raczej budowanie własnego partycjonowania (np. Za pomocą pierwszego znaku słowa => twogramy_a).

Było tak wiele zmian, które musiałbym wprowadzić do mojej pierwszej odpowiedzi, że zacznę tę !!!

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    NGRAM VARCHAR(64) NOT NULL,
    PRIMARY KEY (NGRAM),
    KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL,
    PRIMARY KEY (NGRAM_ID,YR)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
    NGRAM VARCHAR(64) NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
    DECLARE NEW_ID BIGINT;

    INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.YR,NEW.MC,NEW.PC,NEW.VC);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30,vc=vc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30;
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.yr Year,B.mc Matches,B.pc Pages,B.vc Volumes FROM 
ngram_key A,ngram_rec B
WHERE A.ngram='rolando angel edwards'
AND A.ngram_id=B.ngram_id;

Znacznie mniejsze tabele z informacjami o roku, ale o wiele większe klucze, aby zachować oryginalny ngram. Zwiększyłem również ilość danych testowych. Możesz wyciąć i wkleić to bezpośrednio do MySQL.

CAVEAT

Po prostu usuń ROW_FORMAT i stanie się dymaniczny i skompresuj tabele ngram_key o wiele mniejsze.

DiskSpace Metrics

nrgram_rec ma 17 bajtów na wiersz
8 bajtów dla ngram_id (maksymalna wartość bez znaku 18446744073709551615 [2 ^ 64 - 1])
8 bajtów na 4 małe kartki (po 2 bajty)
1 bajt Wewnętrzna flaga usuwania MyISAM

Wpis indeksu dla ngram_rec = 10 bajtów (8 (ngram_id) + 2 (rok))

47 milionów wierszy X 17 bajtów na wiersz = 0799 milionów bajtów = 761,98577 MB
47 milionów wierszy X 12 bajtów na wiersz = 0564 milionów bajtów = 537,85231 MB
47 milionów wierszy X 29 bajtów na wiersz = 1363 milionów bajtów = 1,269393 GB

5 miliardów wierszy X 17 bajtów na wiersz = 085 miliardów bajtów = 079.1624 GB
5 miliardów wierszy X 12 bajtów na wiersz = 060 miliardów bajtów = 055,8793 GB
5 miliardów wierszy X 29 bajtów na wiersz = 145 miliardów bajtów = 135,0417 GB

ngram_key ma 73 bajty 64 bajty dla ngram (ROW_FORMAT = FIXED ustaw varchar na char) 8 bajtów dla ngram_id 1 bajt MyISAM wewnętrzna flaga usuwania

2 wpisy indeksu dla klucza ngram = 64 bajtów + 8 bajtów = 72 bajtów

47 milionów wierszy X 073 bajtów na wiersz = 3431 milionów bajtów = 3,1954 GB
47 milionów wierszy X 072 bajtów na wiersz = 3384 milionów bajtów = 3,1515 GB
47 milionów wierszy X 145 bajtów na wiersz = 6815 milionów bajtów = 6,3464 GB

5 miliardów wierszy X 073 bajtów na wiersz = 365 miliardów bajtów = 339,9327 GB
5 miliardów wierszy X 072 bajtów na wiersz = 360 miliardów bajtów = 335,2776 GB
5 miliardów wierszy X 145 bajtów na wiersz = 725 miliardów bajtów = 675,2088 GB

Oto dość dzika sugestia

Konwertuj wszystkie ngramy na 32-znakowe klucze MD5

Ta tabela pomieści wszystkie ngramy dowolnego rozmiaru (do 255 znaków), 1-gram, 2-gram itp.

use test
DROP TABLE ngram_node;
DROP TABLE ngram_blackhole;
CREATE TABLE ngram_node
(
  NGRAM_KEY  CHAR(32) NOT NULL,
  NGRAM_YEAR SMALLINT NOT NULL,
  M_COUNT    SMALLINT NOT NULL,
  P_COUNT    SMALLINT NOT NULL,
  V_COUNT    SMALLINT NOT NULL,
  PRIMARY KEY   (NGRAM_KEY,NGRAM_YEAR)
) ENGINE=MyISAM
PARTITION BY KEY(NGRAM_KEY)
PARTITIONS 256;
CREATE TABLE ngram_blackhole
(
  NGRAM      VARCHAR(255) NOT NULL,
  NGRAM_YEAR SMALLINT NOT NULL,
  M_COUNT    SMALLINT NOT NULL,
  P_COUNT    SMALLINT NOT NULL,
  V_COUNT    SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blackhole FOR EACH ROW
BEGIN
    INSERT INTO ngram_node VALUES (MD5(NEW.NGRAM),NEW.NGRAM_YEAR,NEW.M_COUNT,NEW.P_COUNT,NEW.V_COUNT);
END; $$
DELIMITER ;
INSERT INTO ngram_blackhole VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
SELECT * FROM ngram_node;

Powód, dla którego wybrałem 256 partycji, wynika z faktu, że funkcja MD5 zwraca 16 różnych znaków (wszystkie cyfry szesnastkowe). Pierwsze dwa bajty to 16 X 16, 256.

Oto wynik w MySQL 5.5.11 na moim pulpicie Windows 7

mysql> use test
Database changed
mysql> DROP TABLE ngram_node;
Query OK, 0 rows affected (0.22 sec)

mysql> DROP TABLE ngram_blackhole;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE ngram_node
    -> (
    ->   NGRAM_KEY  CHAR(32) NOT NULL,
    ->   NGRAM_YEAR SMALLINT NOT NULL,
    ->   M_COUNT    SMALLINT NOT NULL,
    ->   P_COUNT    SMALLINT NOT NULL,
    ->   V_COUNT    SMALLINT NOT NULL,
    ->   PRIMARY KEY    (NGRAM_KEY,NGRAM_YEAR)
    -> ) ENGINE=MyISAM
    -> PARTITION BY KEY(NGRAM_KEY)
    -> PARTITIONS 256;
Query OK, 0 rows affected (0.36 sec)

mysql> CREATE TABLE ngram_blackhole
    -> (
    ->   NGRAM      VARCHAR(255) NOT NULL,
    ->   NGRAM_YEAR SMALLINT NOT NULL,
    ->   M_COUNT    SMALLINT NOT NULL,
    ->   P_COUNT    SMALLINT NOT NULL,
    ->   V_COUNT    SMALLINT NOT NULL
    -> ) ENGINE=BLACKHOLE;
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blackhole FOR EACH ROW
    -> BEGIN
    ->  INSERT INTO ngram_node VALUES (MD5(NEW.NGRAM),NEW.NGRAM_YEAR,NEW.M_COUNT,NEW.P_COUNT,NEW.V_COUNT);
    -> END; $$
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> INSERT INTO ngram_blackhole VALUES
    -> ('rolando',1965,31,29,85),
    -> ('pamela',1971,33,21,86),
    -> ('dominique',1996,30,18,87),
    -> ('diamond',1998,13,28,88),
    -> ('rolando edwards',1965,31,29,85),
    -> ('pamela edwards',1971,33,21,86),
    -> ('dominique edwards',1996,30,18,87),
    -> ('diamond edwards',1998,13,28,88),
    -> ('rolando angel edwards',1965,31,29,85),
    -> ('pamela claricia edwards',1971,33,21,86),
    -> ('dominique sharlisee edwards',1996,30,18,87),
    -> ('diamond ashley edwards',1998,13,28,88);
Query OK, 12 rows affected (0.18 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ngram_node;
+----------------------------------+------------+---------+---------+---------+
| NGRAM_KEY                        | NGRAM_YEAR | M_COUNT | P_COUNT | V_COUNT |
+----------------------------------+------------+---------+---------+---------+
| 2ca237192aaac3b3a20ce0649351b395 |       1996 |      30 |      18 |      87 |
| 6f7fd3368170c562604f62fb4e92056d |       1965 |      31 |      29 |      85 |
| fb201333fef377917be714dabd3776d9 |       1971 |      33 |      21 |      86 |
| 4f79e21800ed6e30be4d1cb597f910c6 |       1971 |      33 |      21 |      86 |
| 9068e0de9f3fd674d4fa7cbc626e5888 |       1998 |      13 |      28 |      88 |
| 8a18abe90f2612827dc3a215fd1905d3 |       1965 |      31 |      29 |      85 |
| be60b431a46fcc7bf5ee4f7712993e3b |       1996 |      30 |      18 |      87 |
| c8adc38aa00759488b1d759aa8f91725 |       1996 |      30 |      18 |      87 |
| e80d4ab77eb18a4ca350157fd487d7e2 |       1965 |      31 |      29 |      85 |
| 669ffc150d1f875819183addfc842cab |       1971 |      33 |      21 |      86 |
| b685323e9de65080f733b53b2305da6e |       1998 |      13 |      28 |      88 |
| 75c6f03161d020201000414cd1501f9f |       1998 |      13 |      28 |      88 |
+----------------------------------+------------+---------+---------+---------+
12 rows in set (0.00 sec)

mysql>

Proszę zauważyć, że załadowałem 1-gram, 2-gram i 3-gram do tego samego stołu, ale nie masz pojęcia, który MD5 należy do którego ngram. W ten sposób wszystkie ngramy mogą zostać zmodernizowane w tej jednej tabeli. Pamiętaj tylko, aby wstawić do tabeli ngram_blackhole, a reszta zostanie zrobiona za Ciebie.

Musisz wykonać zapytanie do tabeli ngram_node za pomocą MD5 () programu ngram bez względu na to, który ngram.

mysql> select * from ngram_node where ngram_key=MD5('rolando edwards');
+----------------------------------+------------+---------+---------+---------+
| NGRAM_KEY                        | NGRAM_YEAR | M_COUNT | P_COUNT | V_COUNT |
+----------------------------------+------------+---------+---------+---------+
| 6f7fd3368170c562604f62fb4e92056d |       1965 |      31 |      29 |      85 |
+----------------------------------+------------+---------+---------+---------+
1 row in set (0.05 sec)

Jeśli chcesz podzielić 1-gram, 2-gram i 3-gram na osobne repozytoria, po prostu utwórz inny stół, inny stół blackhole i inny wyzwalacz na stole blackhole, aby wstawić do drugiego stołu.

Ponadto, jeśli twoje ngramy są dłuższe niż 255 (jeśli robisz 7 gramów lub 8 gramów), po prostu zwiększ rozmiar VARCHAR kolumny NGRAM w tabeli ngram_blackhole.

Spróbuj !!!

AKTUALIZACJA

W pytaniu stwierdzono, że do mysql załadowano 47 milionów wierszy. W przypadku mojego sugerowanego układu tabeli zwróć uwagę na następujące kwestie:

ngram_node ma 41 bajtów na wiersz: 32 dla NGRAM_KEY
8 dla liczb (2 dla każdego SMALLINT)
1 dla wewnętrznej flagi MyISAM DELETED

Każdy wpis indeksu klucza podstawowego miałby 34 bajty
32 dla NGRAM_KEY
2 dla NGRAM_YEAR

47 milionów wierszy X 41 bajtów na wiersz = 1,927 miliarda bajtów, około 1,79466 GB.
47 milionów wierszy X 34 bajtów na pozycję indeksu = 1,598 miliarda bajtów, około 1,48825 GB.
Zużycie tabeli MyISAM powinno wynosić około 3,28291 GB.

Pytanie dotyczyło również załadowania 5 miliardów wierszy.

5 miliardów wierszy X 41 bajtów na wiersz = 205 miliardów bajtów, około 190,9211 GB.
5 miliardów wierszy X 34 bajtów na wpis indeksu = 170 miliardów bajtów, około 158,3248 GB.
Zużycie tabeli MyISAM powinno wynosić łącznie 349,2459 GB.

Zwróć uwagę, że tempo wzrostu miejsca używanego w tabeli MyISAM jest liniowe ze względu na klucz podstawowy o stałej wielkości. Na tej podstawie możesz teraz zaplanować miejsce na dysku.