7 miljoen rows in mysql

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Daniel van Seggelen

Daniel van Seggelen

11/06/2019 12:03:32
Quote Anchor link
Ik heb een zwaar groot tabel met ruim 7 miljoen rijen.
Het punt is dat wanneer ik een count query wil uitvoeren dat hij er oneindig mee bezig blijft.

Is dit wel verstandig met mariaDB? Of moet ik echt postgresq gebruiken? Zal dit echt uitmaken?
 
PHP hulp

PHP hulp

28/03/2024 20:22:55
 
Ivo P

Ivo P

11/06/2019 12:12:25
Quote Anchor link
hoe tel je?

SELECT * FROM tabel
en dan met num_rows() ?

of

SELECT COUNT(1) AS aantal FROM tabel

?
 
- Ariën  -
Beheerder

- Ariën -

11/06/2019 12:46:46
Quote Anchor link
En hoe steken je indices en queries in elkaar? Met een goede opzet moet 7 miljoen geen probleem zijn voor MySQL/MariaDB.
Gewijzigd op 11/06/2019 12:47:49 door - Ariën -
 
Daniel van Seggelen

Daniel van Seggelen

12/06/2019 04:45:43
Quote Anchor link
select met count geeft resultaat, mar erg lang.

Daarnaast gewoon een select * from tabel, duurt eeuwen zelfs in de cli.

De internetverbinding is waar ik nu be wel erg langzaam, maar goed zo lang hoeft het ook weer niet te duren.



Toevoeging op 12/06/2019 04:47:12:

ook gewoon gaan naar www.domein.nl/phpmyadmin laad uren, gebeurd niks. En niks staat in de wachtrij met mysql queries

daarnast gewoon een create index op deze tabel komt geen einde aan. Snap niet waarom, ook geen foutmeldingen. Hoe debug ik dit?
Gewijzigd op 12/06/2019 04:54:57 door Daniel van Seggelen
 
Ivo P

Ivo P

12/06/2019 09:12:32
Quote Anchor link
Stel je bent vakkenvuller bij de AH.
De vraag van je chef is: hoeveel cola ligt er in de winkel?

optie 1: haal alle flessen op de 2 pallets naar kantoor en tel daar hoeveel het er waren. En doe vervolgens niets met de flessen in kwestie.

optie 2: loop de winkel in, tel de flessen en loop met het papiertje met 114 erop terug naar kantoor.

Dit is te vergelijken met SELECT * en met SELECT COUNT()

Als je alleen maar wilt tellen, dan is SELECT COUNT() de manier.
Zit er nog een WHERE aan die query?

Puur SELECT COUNT(1) FROM tabelnaam zou eventueel enkele seconden mogen duren, maar niet meer dan dat.
Heb het net getest met enkele tabellen met tussen 3.4 en 5 miljoen records: circa 2 tellen met een uitschieter naar 8.
 
Thomas van den Heuvel

Thomas van den Heuvel

12/06/2019 15:35:13
Quote Anchor link
En afhankelijk van je MySQL client API kan dit ook nog allerlei consequenties hebben voor geheugengebruik. Ik kan mij zo voorstellen dat als je heel erg inefficiënte of gewoon lompe queries uitvoert dat zowel de MySQL- alsook de PHP-kant tegen limieten aan beginnen te hikken. Informatie over MySQL kun je opvragen via phpinfo() onder het kopje mysql(i).

Als de client afwijkt van "mysqlnd" (de MySQL native driver) dan zou je eens bij je webboer kunnen informeren waarom er bewust daarvoor gekozen is, want volgens mij is mysqlnd min of meer de standaard tegenwoordig.
Gewijzigd op 12/06/2019 15:35:48 door Thomas van den Heuvel
 
Daniel van Seggelen

Daniel van Seggelen

13/06/2019 10:04:28
Quote Anchor link
Ik krijg bij phpmyadmin:

Gateway Timeout
The gateway did not receive a timely response from the upstream server or application.

verder er worden dus verder geen queries uitgevoerd, maar alleen bij het laden krijg ik dit al.
Dus het heeft niets met de queries te maken.

Als ik truncate table GROTE_TABEL doe, dan duurt het jaren, lijkt mij gewoon dat mysq dit niet aankan
 
Ivo P

Ivo P

13/06/2019 10:43:20
Quote Anchor link
Wat is de query die je uitvoert?

Zit daar nog een WHERE-stuk bij?

of puur SELECT Count(1) FROM tabel?
 
- Ariën  -
Beheerder

- Ariën -

13/06/2019 11:07:42
Quote Anchor link
Daniel van Seggelen op 13/06/2019 10:04:28:

Als ik truncate table GROTE_TABEL doe, dan duurt het jaren, lijkt mij gewoon dat mysq dit niet aankan

Als ik zo hoor heb ik eerder het idee dat je MySQL moet tunen.

Ik neem aan dat je een eigen server hebt?
Kijk eens hier naar? Dit geeft een analyse (en fixt niks).
https://github.com/major/MySQLTuner-perl/blob/master/README.md
Gewijzigd op 13/06/2019 11:23:11 door - Ariën -
 
Thomas van den Heuvel

Thomas van den Heuvel

13/06/2019 19:18:53
Quote Anchor link
Ook wordt hier weer een probleem geconstateerd aan het eindpunt. Een timeout is het eindresultaat van alles wat ervoor gebeurde. Vraag is dus, wat ging hier allemaal aan vooraf. Het kan inderdaad liggen aan configuratie. Het kan natuurlijk ook gewoon dat je tabel of query inefficiënt is, of dat beide gewoon brak zijn.

Daarnaast: wat probeer je precies te bereiken?

Maar tevens: hoe luidt het gedrag van de data. Je hebt het nu alleen over een "grote tabel X", dat zegt ons niet zoveel. Wat zit hierin? Welke indexen staan hierop? Welke queries voer je hier allemaal op uit, en hoe vaak? Hoe verandert de tabel over tijd? Komen hier nog veel meer records bij? Verdwijnt er ooit iets uit de tabel? Wat is de functie van deze tabel? Hoe vaak wordt deze gemiddeld geraadpleegd? Heb je deze tabel echt nodig? Kun je deze mogelijk splitsen? Of een groot deel archiveren omdat deze toch nooit opgevraagd wordt? Is alle data die hierin zit echt relevant?
 
Daniel van Seggelen

Daniel van Seggelen

14/06/2019 01:48:58
Quote Anchor link
Even een update met
"truncate table GROTE_TABEL" in cli doe dan heeft dat 6 uren en 44 min en 14.12 seconde geduurd.

Daarna kom ik gewoon weer in phpmyadmin.

Dit is het tabel:

https://www.globalwebdevelopment.net/tttabel.png


Dit zijn de indexen voor het tabel

https://www.globalwebdevelopment.net/index.png


Quote:
"Een timeout is het eindresultaat van alles wat ervoor gebeurde. Vraag is dus, wat ging hier allemaal aan vooraf"


Daarvoor heb ik dus via csv feeds met "load data infile" alle data in de tabel geplaatst.
Geen fouten t/, ruim 6.3 miljoen rijen.
Ja ik heb een eigen vps centos 7

De configuratie kan ik wel blijven tunen, maar weet iemand aan de hand van deze tabel informatie toevallig wat er fout kan zijn?


Daarnaast heb ik ook een standaard mysql configuratie voor mariaDB
Gewijzigd op 14/06/2019 02:09:20 door Daniel van Seggelen
 
- Ariën  -
Beheerder

- Ariën -

14/06/2019 02:16:41
Quote Anchor link
Ivo P en Thomas hebben nog een belangrijke vraag openstaan. Zou je die in ieder geval nog kunnen beantwoorden?
Gewijzigd op 14/06/2019 02:17:45 door - Ariën -
 
Ivo P

Ivo P

14/06/2019 09:22:14
Quote Anchor link
TRUNCATE leegt bij mijn weten je tabel door hem geheel weg te gooien, en opnieuw te creëren.

Dat zou dus best snel moeten gaan. In tegenstelling tot DELETE FROM tabel WHERE 1=1

Blijft over dat de opslag misschien nog een tijd bezig blijft om de schijfruimte te reorganiseren.

Maar 6 uur is overdreven.
 
- Ariën  -
Beheerder

- Ariën -

14/06/2019 09:29:55
Quote Anchor link
Een heb je ook het analyze-script geprobeerd?
 
Willem vp

Willem vp

20/06/2019 15:54:29
Quote Anchor link
Daniel van Seggelen op 13/06/2019 10:04:28:
Als ik truncate table GROTE_TABEL doe, dan duurt het jaren, lijkt mij gewoon dat mysq dit niet aankan

Ik heb het zojuist even geprobeerd op een oude tabel die ik zelf hier nog had rondzwerven. Tabel was 350 GB groot en had ruim 3,2 miljard records. De truncate duurde 9,52 seconden. Een select count(*) van die tabel (vóór het truncaten ;-)) was in 0,00 seconden klaar.

Overigens was dit op een fysieke server. Ik heb nog een MariaDB op een VPS draaien, maar daar heb ik geen tabellen die ik ongestraft kan truncaten. Heb daar wel een tabel van ruim 188 miljoen records waar ik een select count(*) op kan doen, en die komt ook in 0,00 seconden terug met een antwoord. Op mijn VPS had ik de afgelopen maand van de 123 miljoen uitgevoerde queries er slechts 143 die langer dan 5 seconden duurden. De tabelgrootte is dus niet per se bepalend voor de duur van je queries.

Ik vermoed dus ook (het is al vaker genoemd) dat je ergens een tuning-probleempje hebt. Je zou eens een script als https://launchpad.net/mysql-tuning-primer kunnen gebruiken om te kijken of er ergens pijnpunten zitten. Het is een oud script (uit 2011) maar ik heb er al verschillende servers goed mee kunnen tunen. Een ander script dat goede adviezen geeft (en actiever wordt ontwikkeld) is http://mysqltuner.com/

Klopt trouwens de index op prijs/productUrl/verzend_tijd/verzend_kosten? Ik kan er zelf in ieder geval geen situatie bij bedenken waarin die nodig/nuttig zou kunnen zijn.
 
Ivo P

Ivo P

20/06/2019 16:31:30
Quote Anchor link
an als we dan toch naar de tabel kijken:

winkel_id klinkt als een foreign key naar een tabel met winkels. Ik zie daar geen FK relatie en/of index?

het is ook een int(9), terwijl id van deze tabel een int(11) is. Wil je een relatie tussen tabellen leggen, moeten die wel gelijk zijn. Als je een int(11) op deze tabel hebben, verwacht ik dat ook op je winkel tabel. (en dus ook voor winke_id).

EAN: dat is geen getal Dat is een string bestaande uit cijfers. Wil je niet dat voorloopnullen verdwijnen, dan geen int gebruiken. VARCHAR...

Bedragen gedragen zich het best als DECIMAL(10, 2) en niet als double.


producturl: een url van 1000 tekens is al extreem. Maar in TEXT kun je 64K tekens kwijt. (overkill?)

verzend_tijd: een datetime misschien? MAar wat sla je op als je 222 tekens nodig kunt hebben?
 
Thomas van den Heuvel

Thomas van den Heuvel

20/06/2019 19:42:24
Quote Anchor link
Ivo P op 20/06/2019 16:31:30:
Wil je een relatie tussen tabellen leggen, moeten die wel gelijk zijn.

En om relaties aan te kunnen leggen en transacties te kunnen gebruiken kun je beter de InnoDB engine gebruiken (is misschien zelfs nog steeds verplicht / andere engines genieten deze ondersteuning mogelijk nog steeds niet), dus dat spul is waarschijnlijk allemaal MyISAM.

Als je echt relationele databases wilt bouwen, en geen tabellen die als los zand aan elkaar hangen, dan is InnoDB eigenlijk de enige juiste keuze bij mijn weten.
 
Willem vp

Willem vp

20/06/2019 23:21:51
Quote Anchor link
Ivo P op 20/06/2019 16:31:30:
verzend_tijd: een datetime misschien? MAar wat sla je op als je 222 tekens nodig kunt hebben?

Ik vermoed (wilde gok) dat de verzend_tijd niet zozeer een tijdstip is waarop het product is verstuurd, maar eerder een indicatie van de levertijd (met een niet ideaal gekozen veldnaam). Daar zou je dus iets in opslaan als "drie dagen" of "volgende dag, indien besteld voor 22:00 op werkdagen". Met een beetje creativiteit kun je dan best richting de 200 tekens komen. Maar ook in dat geval kun je nog wel normalisatie op dat veld toepassen.

Voor zover ik weet, is het verschil tussen int(9) en int(11) niet relevant bij het leggen van relaties tussen tabellen. Een int wordt gewoon opgeslagen als 4 bytes en het getal tussen haakjes is alleen voor presentatiedoeleinden (en dan vooral wanneer het veld 'zerofill' is). In het kader van consistentie is het uiteraard wel aan te bevelen e.e.a. gelijk te houden.

Het is inderdaad wat overkill om een url als text-veld op te slaan, maar ik heb in de praktijk wel urls gezien die langer dan 1000 tekens waren. Om de rijgrootte niet al teveel de spuigaten uit te laten lopen zou je die url dan in een text-veld kunnen zetten.

Thomas van den Heuvel op 20/06/2019 19:42:24:
dus dat spul is waarschijnlijk allemaal MyISAM.

Als ik naar de indexdefinities kijk, zie ik een index op de eerste 767 bytes van de kolom productUrl. Dat suggereert InnoDB (met row format 'redundant' of 'compact'). By MyISAM is die limiet 1000 bytes en die 767 bytes is dermate specifiek dat ik niet denk dat iemand die spontaan zelf invoert.
Gewijzigd op 21/06/2019 07:49:32 door Willem vp
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.