MySQL z dużą ilością danych Opcje

[trifek]

Witam serdecznie,
Mam taką tabelkę:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `kategorie` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `tytul` varchar(85) COLLATE utf8_unicode_ci DEFAULT NULL,
  `domena_url` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `enable` int(11) NOT NULL DEFAULT '0',
  `level` int(11) NOT NULL DEFAULT '0',
  `parent_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Indeksy dla zrzutów tabel
--
 
--
-- Indexes for table `cms_kategorie`
--
ALTER TABLE `cms_kategorie`
  ADD PRIMARY KEY (`bf_id`),
  ADD UNIQUE KEY `bf_id` (`bf_id`),
  ADD KEY `bf_id_2` (`bf_id`);
 
--
-- AUTO_INCREMENT for dumped tables
--
 
--
-- AUTO_INCREMENT dla tabeli `cms_kategorie`
--
ALTER TABLE `cms_kategorie`
  MODIFY `bf_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
[SQL] pobierz, plaintext 

Mój serwer: http://www.online.net/en/dedicated-server/dedibox-xc - z hdd sata3
Tabela ma 1.5GB.


Mój /my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=600
max_user_connections=400
wait_timeout=30
interactive_timeout=50
long_query_time=5
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log

#innodb_use_native_aio = 0
innodb_file_per_table

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

thread_cache_size = 256
table_cache = 1024
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=64M


Niestety strony ładują się dość długo. Czy da się to jakoś przyśpieszyć?
Czy raczej to problem serwera?

[Pyton_000]

Chcesz powiedzieć że tabele z kategoriami zajmują 1,5GB To ile ty tam tego masz? 150mln ?

[trifek]

W tej chwili nie mam dostępu do tej bazy, mam stary backup.
1,285,751 rekordów zajmowało 203.8 MB - teraz baza ma ok 1,3GB - ale może "utyć" do tych 1,5GB i zastanawiam się czy trzeba ten serwer zmieniać,czy wystarczy coś z konfiguracją pokombinować :/

[markuz]

Założ indeksy na id i parent_id jak jeszcze tego nie zrobiłeś - Baza się powiększy o kilkadziesiąt % ale będzie szybciej działać.

[KsaR]

ALTER TABLE `cms_kategorie`
ADD PRIMARY KEY (`bf_id`),
ADD UNIQUE KEY `bf_id` (`bf_id`),
ADD KEY `bf_id_2` (`bf_id`);
Wedlug mnie - bezsens, tylko zajmujesz miejsce i opozniasz a nie przyszybszasz.
Zostaw 1 klucz, np. PRIMARY KEY. Inne pokasuj.

bigint zmien na mediumint UNSIGNED (dzieki jej mozesz max. 16777215 id).
int UNSIGNED ma max. 4294967295 ale juz wazy 4 zamiast 3 bajtów ;p

Ten post edytował KsaR 2.10.2015, 12:56:18

[trifek]

Są klucze

ALTER TABLE `cms_kategorie`
ADD PRIMARY KEY (`bf_id`),
ADD UNIQUE KEY `bf_id` (`bf_id`),
ADD KEY `bf_id_2` (`bf_id`);
Wedlug mnie - bezsens, tylko zajmujesz miejsce i opozniasz a nie przyszybszasz.
Zostaw 1 klucz, np. PRIMARY KEY. Inne pokasuj.

bigint zmien na mediumint UNSIGNED (dzieki jej mozesz max. 16777215 id).
int UNSIGNED ma max. 4294967295 ale juz wazy 4 zamiast 3 bajtów ;p

da się to jakoś zdjąć z poziomu phpmyadmin?

[KsaR]

Są klucze




da się to jakoś zdjąć z poziomu phpmyadmin?

[SQL] pobierz, plaintext 
ALTER TABLE `kategorie` CHANGE `id`  `id` mediumint UNSIGNED NOT NULL PRIMARY KEY
[SQL] pobierz, plaintext 

Reszte bys musial poogoglowac, np. Ta kasacje nadmiernych kluczy.

Ten post edytował KsaR 2.10.2015, 13:01:00

[trifek]

[SQL] pobierz, plaintext 
 
ALTER TABLE `kategorie ` CHANGE `id `  `ADD PRIMARY KEY (`id`),`
[SQL] pobierz, plaintext 

takie coś?

[Pyton_000]

Wejdź sobie do PMA do Struktury i pod strukturą masz "Index" i tam możesz zmieniać, tak samo w strukturze możesz edytować

[trifek]

zrobił coś takiego: ALTER TABLE `kategorie` ADD INDEX( `id`); to jest OK?

[nospor]

trifek napisal, ze strona mu muli. Nie napisal, ze to zapytanie mu muli a ze strona. A to duza roznica.
Spojrzcie na jego strukture drzewiastą, samo `parent_id` . No jak to niby ma dzialac szybko? Toż to najgorsza struktura jaką można mieć. I jeśli teraz aplikacja probuje pobierac jakieś drzewkowe dane czy cokolwiek, to logiczne ze muli.

Oczywiscie problem może też być w jakiś indeksach. No ale po co zgadywać? Dobrze by było, by trifek zdiagnozował ktore czesci kodu/zapytan dokladnie mulą.

[trifek]

dzięki za pomoc

Tam nie ma nic nadzwyczajnego, wyświetlam dane na zasadzie:

[PHP] pobierz, plaintext 
 
$miasta = Array();
$stmt = $db->prepare("select tytul, domena_url, id FROM kategorie1   WHERE enable= 1  ORDER by RAND()  ASC limit 50;");
$stmt->execute();
    foreach ($stmt as $rowX) {
        array_push($miasta, $rowX['tytul']);
    } 
$stmt->closeCursor();
 
$produkty  = Array();
$stmt = $db->prepare("select tytul, domena_url, id FROM kategorie2   WHERE enable= 1  ORDER by RAND()  ASC limit 50;");
$stmt->execute();
    foreach ($stmt as $rowX) {
        array_push($produkty, $rowX['tytul']);
    }
$stmt->closeCursor();
 
$res = array();
foreach($miasta as $val) {
    $nazwa_miasta = baza_odczyt($val .' ' . array_shift($produkty));
    echo "<a href='".$portal_url."' title='".$nazwa_miasta."'>".$nazwa_miasta."</a>, ";
}  
 
 
[PHP] pobierz, plaintext 

tam oczywiście są jakieś inne "końcówki" tego zaytania.... np nie ma rand a jest where parent_id = ..... itp.

Jest to generalnie wyświetlanie danych z tej bazy

[nospor]

Juz samo ORDER by RAND() przy paru milionach rekordow jest w stanie zamulic baze totalnie niezaleznie od indeksow.

[trifek]

A jak takie losowanie lepiej rozwiązać?

[nospor]

google -> losowanie mysql

Jeden z wynikow
http://blog.vokiel.com/mysql-rand-jak-pobrac-losowe-wiersze/

[trifek]

Dziękuję bardzo

Myślisz o tej procedurze:

[SQL] pobierz, plaintext 
DELIMITER $$
DROP PROCEDURE IF EXISTS getRandomRecords$$
CREATE PROCEDURE getRandomRecords(IN cnt INT)
 
BEGIN
    DROP TEMPORARY TABLE IF EXISTS randomRecords;
    CREATE TEMPORARY TABLE randomRecords ( `rand_id` INT, `sub` CHAR(255) );
 
    loop_random: LOOP
        IF cnt < 1 THEN 
            LEAVE loop_random;
        END IF;
 
        INSERT INTO randomRecords
            SELECT r1.id, r1.sub FROM `keys` AS r1 JOIN
                (SELECT (RAND() *
                    (SELECT MAX(id) FROM `keys`)) AS id) AS r2
            WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1;
        SET cnt = cnt - 1;
    END LOOP loop_random;
 
SELECT * FROM randomRecords;
END$$
DELIMITER ;
[SQL] pobierz, plaintext 

[nospor]

Podalem ci linka, gdzie masz alternatywy. Autor opisal kazdą z nich. wybierz sobie.

[trifek]

Faktycznie wymiana zapytania pomogła

Zrobiłem takie zapytanie:
SELECT tytul, domena_url FROM kategorie WHERE enable = 1 and RAND()<(SELECT ((50/COUNT(*))*50) FROM kategorie) ORDER BY RAND() LIMIT 50 ;

I skrypt działa jakieś 40% szybciej, jednak jeszcze wolno... Ma ktoś może jakiś pomysł jak to przyśpieszyć?

Ten post edytował trifek 3.10.2015, 11:43:43

[Pyton_000]

Nadal używasz RAND()

[trifek]

takie coś znalazłem w przykładzie... da się to jakoś zastąpić?
W tym linku ta procedura wygląda fajnie, tylko nie potrafię jej zaadaptować

Wiesz może czy poprawnie ją "przerobiłem"?

Ten post edytował trifek 3.10.2015, 13:38:39