WN

WN (https://www.wn.se/forum/index.php)
-   Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)
-   -   Optimera MySQL (https://www.wn.se/forum/showthread.php?t=1047006)

Linuus 2011-03-08 21:33

Optimera MySQL
 
Hej!

Jag har en VPS där jag precis lagt upp en site. Jag har dock lite problem med MySQL och får ofta ett fel i stil med:
"Incorrect key file for table '/tmp/#sql...."

Jag har läst att detta kan bero på att det är för lite plats på disken för /tmp/...

Kollade i katalogen på disken och hittade en fil som hette #sql_33ba_0.MDY som är ca 270MB stor.

Vad används denna fil till? Hur stor är "normalt" att den är?

Jag har i huvudsak två tabeller som har många rader. Den ena har 19.000 rader och den andra 34.000 rader.


Tips på hur jag kan försöka analysera och optimera ev. flaskhalsar och problem?


EDIT:
Körde OPTIMIZE TABLE på dessa två tabeller och då försvann .MYD-filen så nu verkar det fungera bättre.

Hur kan jag undvika samma problem i framtiden?

hnn 2011-03-09 08:03

Använd index och optimera sql frågorna. Kör EXPLAIN framför dem.

Många säger att man ska undvika att program som "mysqltuner" osv och istället konsultera MySQL konsulter. eftersom programmen inte kan ge optimala svar.

Dom har iofs rätt, men du kan testa programmet iaf. Det ger dig ett litet hum om vad som kan vara fel.

tartareandesire 2011-03-09 13:54

Det finns en rad enkla saker man man göra manuellt på egen hand utan att behöva en konsult eller ett program som mysqltuner.

.myd är helt enkelt bara databasens datafil. I det här fallet är det förmodligen en backupfil som kan användas för att återställa databasen. Storleken är inte alls extrem och du har ganska små tabeller med några 10k rader så det ska inte ställa till några problem.

En enkel repair table fungerar förmodligen för dig.

Linuus 2011-03-09 14:36

mysqltuner har jag.

.myd filen är en tmp-fil som mysql skriver till tabellen när den gör en av mina JOINS verkar det som. Den vill alltså inte använda mina index. Har en tråd på Stack Overflow som jag fått lite tips i jag ska testa :)

tartareandesire 2011-03-09 15:12

Ah, läste lite snabbt, det var en tillfällig fil. De borde tas bort per automatik, kör du en gammal MySQL-version tro?

Linuus 2011-03-09 15:29

MySQL v.5.0.91 är det. Filerna verkar tas bort dock. Hade nog inget att göra med att jag körde "OPTIMIZE TABLE". Dock tar ju queryn väldigt lång tid att köra när den måste skriva en tmp-tabell på 200-400MB till disk för att köra den.

Här finns mina querys och tabeller beskrivna mer detaljerat:
http://stackoverflow.com/questions/5...ze-mysql-query

Linuus 2011-03-09 16:39

Jag kopierar in mitt inlägg från SO här så kanske någon här kan hjälpa mig med detta.

EXPLAIN på queryn som beskrivs nedan ger detta:
http://img268.imageshack.us/img268/3...10309kl015.png

Vilket tyder på att problemet har med catraws-tabellen att göra. Den gör ju en full table scan på den verkar det som och måste skriva en tmp-tabell till disken då.

Hi!

I have a database that stores products. The two biggest tables are my products table and images table.
Products: ~19.000 rows

Images: ~34.000 rows

Categories: ~60 rows

Raw categories: ~1200 rows

Brands: 700 rows


(The other tables are only a couple of rows)



I also have a table with brands, raw categories, static categories (the raw categories are mapped to a static category).

I have created a Product VIEW to gather the data I need, like this:
Kod:

    CREATE OR REPLACE VIEW `jos_clothes_view_products` AS
    SELECT tbl.*, r.name AS reseller, b.name AS brand, rcat.raw_name AS cats_raw, cats1.name AS cat1, cats1.slug AS catslug1, cats2.name AS cat2, cats2.slug AS catslug2
    FROM `jos_clothes_products` AS tbl
    LEFT JOIN `jos_clothes_brands` AS b ON b.clothes_brand_id = tbl.clothes_brand_id
    LEFT JOIN `jos_clothes_resellers` AS r ON r.clothes_reseller_id = tbl.clothes_reseller_id
    LEFT JOIN `jos_clothes_catraws` AS rcat ON rcat.clothes_catraw_id = tbl.clothes_catraw_id
    LEFT JOIN `jos_clothes_categories` AS cats2 ON cats2.clothes_category_id = rcat.clothes_category_id
    LEFT JOIN `jos_clothes_categories` AS cats1 ON cats1.clothes_category_id = cats2.parent_id


Then when running a query like this from PHP:

SELECT `tbl`.* FROM `jos_clothes_view_products` AS `tbl` WHERE `tbl`.`cat1` != 'NULL' AND `tbl`.`enabled` = '1' ORDER BY `created_on` DESC , `ordering` ASC LIMIT 0 , 20;

The query is often very slow! Not always though (probably because of caching?). I have also noticed that it creates a tmp-table at about 200MB in size. Sometimes it gets even bigger and fails with "Invalid key for file....".


Any ideas how I can optimize the query? Or the VIEW actually, I guess it's the bottleneck here. Correct?






Product table structure:
Kod:

    CREATE TABLE IF NOT EXISTS `jos_clothes_products` (
      `clothes_product_id` int(11) unsigned NOT NULL auto_increment,
      `clothes_reseller_id` int(11) unsigned NOT NULL,
      `aff_prod_id` varchar(50) NOT NULL,
      `title` varchar(255) NOT NULL,
      `description` text NOT NULL,
      `gender` varchar(20) NOT NULL,
      `clothes_brand_id` int(11) unsigned NOT NULL,
      `color` varchar(255) NOT NULL,
      `size` varchar(50) NOT NULL,
      `clothes_catraw_id` bigint(20) unsigned NOT NULL,
      `price` decimal(10,2) NOT NULL default '0.00',
      `shipping_cost` varchar(20) NOT NULL default '0.00',
      `currency` varchar(10) NOT NULL,
      `availibility` tinyint(1) NOT NULL,
      `product_url` varchar(350) NOT NULL,
      `real_url` varchar(300) NOT NULL,
      `slug` varchar(255) NOT NULL,
      `hits` int(11) NOT NULL,
      `enabled` tinyint(1) NOT NULL default '0',
      `access` int(11) NOT NULL default '0',
      `ordering` bigint(20) unsigned NOT NULL,
      `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
      `created_by` int(11) NOT NULL default '0',
      `modified_on` datetime NOT NULL default '0000-00-00 00:00:00',
      `modified_by` int(11) NOT NULL default '0',
      `locked_on` datetime NOT NULL default '0000-00-00 00:00:00',
      `locked_by` int(11) NOT NULL default '0',
      PRIMARY KEY  (`clothes_product_id`),
      KEY `clothes_brand_id` (`clothes_brand_id`),
      KEY `clothes_catraw_id` (`clothes_catraw_id`),
      KEY `created_on` (`created_on`),
      KEY `clothes_reseller_id` (`clothes_reseller_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18962 ;

Any ideas?

Best Regards




EDIT: Structure of jos_clothes_catraws
Kod:

    --
    -- Struktur för tabell `jos_clothes_catraws`
    --
   
    CREATE TABLE IF NOT EXISTS `jos_clothes_catraws` (
      `clothes_catraw_id` int(11) unsigned NOT NULL auto_increment,
      `clothes_category_id` int(11) unsigned NOT NULL default '0',
      `clothes_reseller_id` int(11) unsigned NOT NULL,
      `raw_name` varchar(255) NOT NULL,
      PRIMARY KEY  (`clothes_catraw_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1289 ;


Magnus_A 2011-03-10 09:27

Blir det bättre om du sätter index på kolumnerna
`clothes_category_id`och
`clothes_reseller_id`
i tabellen `jos_clothes_catraws`?

Linuus 2011-03-10 11:47

Citat:

Ursprungligen postat av Magnus_A (Inlägg 20396990)
Blir det bättre om du sätter index på kolumnerna
`clothes_category_id`och
`clothes_reseller_id`
i tabellen `jos_clothes_catraws`?

Nej tyvärr.

Magnus_A 2011-03-10 17:04

Och hur fungerar denna frågan:
Citat:

SELECT * FROM `jos_clothes_view_products` WHERE `cat1` != 'NULL' AND enabled` = '1' ORDER BY `created_on` DESC , `ordering` ASC LIMIT 0 , 20;

Linuus 2011-03-10 17:32

Hmmm. Ser inte ut att göra någon skillnad när jag kör EXPLAIN på den i alla fall.

Problemet verkar ju ligga i att den gör full table scan på catraws-tabellen för varje rad i category-tabellen.

Magnus_A 2011-03-11 00:02

Du joinar mellan catraws och categories på två olika sätt, kan vara det som stökar till det.
Full table scan får man leva med ibland. men jag trodde att det vara den temporära tabellen som var ditt stora problem?

Conny Westh 2011-03-11 00:24

Har du satt index i båda tabellerna dvs både på Främmande nyckel och primärnyckel? Det kan ha betydelse när SQL ska söka efter en FK (Forreign Key).

Ett vanligt problem är att man glömmer att indexera Främmande nycklar, det finns de som kör utan primärnycklar också.

Kolla hur dina sammansatta index ser ut dvs om du har fler kolumner i ett och samma index eller primärnyckel. Kolla att du har samma ordning i JOIN och WHERE satser.

Kolla även att du har det mest diskriminerande värdet först, och sen i fallande skala...

Adestro 2011-03-11 08:08

Jag skulle nog se över databasdesignen i allmänhet.

Citat:

`color` varchar(255) NOT NULL,
`size` varchar(50) NOT NULL,
`gender` varchar(20) NOT NULL,
`currency` varchar(10) NOT NULL,
Du kan inte bryta ut detta till egna tabeller och bara lagra ett kompakt nummerid i produkttabellen? Du har ganska mycket redundans.

Conny Westh 2011-03-12 01:41

Citat:

Ursprungligen postat av Adestro (Inlägg 20397156)
Jag skulle nog se över databasdesignen i allmänhet.



Du kan inte bryta ut detta till egna tabeller och bara lagra ett kompakt nummerid i produkttabellen? Du har ganska mycket redundans.

Just dessa egenskaper har naturliga primärnycklar som passar bättre än en syntetisk primärnyckel.

Ta Currency som har en internationellt standardiserad 3-ställig valutakod SEK, NOK; USD; EUR mfl dessa passar betydligt bättre som primärnycklar än ett orelaterat heltalsvärde.

Clarence 2011-03-13 18:33

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20397332)
Just dessa egenskaper har naturliga primärnycklar som passar bättre än en syntetisk primärnyckel.

Ta Currency som har en internationellt standardiserad 3-ställig valutakod SEK, NOK; USD; EUR mfl dessa passar betydligt bättre som primärnycklar än ett orelaterat heltalsvärde.

Naturliga primärnycklar ger nästan alltid, liksom i detta fall, onödigt stora primärnycklar vilket gör att det behövs en större minnesmängd för samma data. Om man dessutom använder innodb så får man också automatiskt en tidsanpassad klustring av datan då man har ett auto increment värde. Även sekundärindex för innodb blir mer effektiva då primärnycklarna används som adress till raden. Sen finns det visserligen många fall där sådan detalj-prestanda inte behövs tas vidare mycket hänsyn till men man bör iallafall vara medveten att man allt som oftast väljer bort prestanda för tydlighet med en naturlig primärnyckel.

Conny Westh 2011-03-14 10:54

Citat:

Ursprungligen postat av Clarence (Inlägg 20397588)
Naturliga primärnycklar ger nästan alltid, liksom i detta fall, onödigt stora primärnycklar vilket gör att det behövs en större minnesmängd för samma data. Om man dessutom använder innodb så får man också automatiskt en tidsanpassad klustring av datan då man har ett auto increment värde. Även sekundärindex för innodb blir mer effektiva då primärnycklarna används som adress till raden. Sen finns det visserligen många fall där sådan detalj-prestanda inte behövs tas vidare mycket hänsyn till men man bör iallafall vara medveten att man allt som oftast väljer bort prestanda för tydlighet med en naturlig primärnyckel.

Att optimera prestanda är inget självändamål.

När man bygger en databasstruktur i en relationsdatabas så är det viktigaste att man får en inbyggd kvalitetskontroll på informationen man lagrar.

Då är naturliga primärnycklar en naturlig del av det arbetet. Främmande nycklar som då länkar till primärnycklar är en annan del. Atomära egenskaper ytterligare en. Att följa normalformernas definitioner ytterligare en.

Att använda syntetsiska primärnycklar ska manundvika och endast använda i de fall när det inte finns en naturlig primärnyckel eller där en naturlig primärnyckel kommer att bestå av orimligt många kolumner (typ; >7 kolumner).

Den "prestandavinst" man vinner på att använda syntetiska PK är oftast så marginell att den inte är värd att beakta, i de flesta fall. Man förlorar så otroligt mycket mer vad gäller den inbyggda kvalitetskontrollen av information när man gör det.

Använder man naturliga PK så kan man som människa utföra okulärbesiktning av data på ett enklare sätt än med syntetiska PK. Du kan m a o direkt se om set står NOK i en kolumn där du för väntar dig att det ska stå SEK, men om det står 4 i stället för 7 så finns det inget naturligt sätt för en människa att avgöra om informationen är korrekt eller inte. Man måste ha mycket mer komplexa sätt att kontrollera om informationen är korrekt och det innebär att det är lättare att man får strukturella fel i sin databas och sina applikationer.

Adestro 2011-03-14 11:28

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20397661)
Att optimera prestanda är inget självändamål.

När man bygger en databasstruktur i en relationsdatabas så är det viktigaste att man får en inbyggd kvalitetskontroll på informationen man lagrar.

Då är naturliga primärnycklar en naturlig del av det arbetet. Främmande nycklar som då länkar till primärnycklar är en annan del. Atomära egenskaper ytterligare en. Att följa normalformernas definitioner ytterligare en.

Att använda syntetsiska primärnycklar ska manundvika och endast använda i de fall när det inte finns en naturlig primärnyckel eller där en naturlig primärnyckel kommer att bestå av orimligt många kolumner (typ; >7 kolumner).

Den "prestandavinst" man vinner på att använda syntetiska PK är oftast så marginell att den inte är värd att beakta, i de flesta fall. Man förlorar så otroligt mycket mer vad gäller den inbyggda kvalitetskontrollen av information när man gör det.

Använder man naturliga PK så kan man som människa utföra okulärbesiktning av data på ett enklare sätt än med syntetiska PK. Du kan m a o direkt se om set står NOK i en kolumn där du för väntar dig att det ska stå SEK, men om det står 4 i stället för 7 så finns det inget naturligt sätt för en människa att avgöra om informationen är korrekt eller inte. Man måste ha mycket mer komplexa sätt att kontrollera om informationen är korrekt och det innebär att det är lättare att man får strukturella fel i sin databas och sina applikationer.

Databasen administreras ju förhoppningsvis inte i en texterminal. :)

Vilken databasdesign som passar bäst beror ju på hur databasen ska användas. Men använder man ett RDBMS är man antagligen intresserad av att efterleva ACID. Annars ska man nog titta på andra databassystem med andra sätt att lagra data.

Du säger: "När man bygger en databasstruktur i en relationsdatabas så är det viktigaste att man får en inbyggd kvalitetskontroll på informationen man lagrar.". Det är ju just det man åsidosätter genom att göra som du föreslår? Genom att acceptera redundans ber man om dataanomalier i databasen. För att inte tala om prestandakostnaden att i beräkningar jämföra VARCHAR med VARCHAR jämfört med INT med INT...

Conny Westh 2011-03-14 11:46

Citat:

Ursprungligen postat av Adestro (Inlägg 20397665)
Datan administreras ju förhoppningsvis inte i en texterminal. :)

Vilken databasdesign som passar bäst beror ju på hur databasen ska användas. Men använder man ett RDBMS är man antagligen intresserad av att efterleva ACID. Annars ska man nog titta på andra databassystem med andra sätt att lagra data.

Du säger: "När man bygger en databasstruktur i en relationsdatabas så är det viktigaste att man får en inbyggd kvalitetskontroll på informationen man lagrar.". Det är ju just det man åsidosätter genom att göra som du föreslår? Genom att acceptera redundans ber man om dataanomalier i databasen. För att inte tala om prestandakostnaden att i beräkningar jämföra VARCHAR med VARCHAR jämfört med INT med INT...

Det märks att du inte vet hur relationsdatabaser fungerar. Man garanteras att inga anomalier inträffar när man använder naturliga PK, det har du inte när du använder syntetiska PK. Redundans blir det inte om man använder naturliga PK. Själva nyckeln i sig kan aldrig dubbellagras.

Det där med texterminal förstod jag inte? Jag använder grafiska verktyg att analysera data med.

tartareandesire 2011-03-14 13:19

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20397671)
Det märks att du inte vet hur relationsdatabaser fungerar. Man garanteras att inga anomalier inträffar när man använder naturliga PK, det har du inte när du använder syntetiska PK. Redundans blir det inte om man använder naturliga PK. Själva nyckeln i sig kan aldrig dubbellagras.

Det där med texterminal förstod jag inte? Jag använder grafiska verktyg att analysera data med.

Det där är en mycket gammalmodig och stelbent inställning som inte har i modern webbutveckling att göra. Ja, att undvika redundans är självklart bra MEN att stirra sig blind på att använda naturliga PK är helt fel väg att gå idag (därmed inte sagt att naturliga PK i sig är dåligt). Att spara resurser och prestanda är betydligt viktigare på vältrafikerade sajter vilka blir allt fler i takt med att internet breder ut sig. Dessutom ligger det på alla programmerares ansvar att skapa så resurssnåla system som möjligt. IT är tänkt att vara ett grönare och mer miljövänligt alternativ men då måste också alla hjälpa till och inte fastna i gamla mönster och vanor. Idag förbrukar branschen enorma resurser trots allt bättre teknik och det gäller att tänka lite längre.

Nej, det där tänket bör förpassas till det förgångna en gång för alla.

danjel 2011-03-14 13:40

Man kan inte säga att man generellt ska undvika syntetiska primärnycklar.
Jag säger tvärtom , undvik naturliga primärnycklar i 9 fall av 10.
Speciellt viktigt då det är flera kolumner, tänk att göra en många-till-många relation, givet att du har t.ex två PK bestående av 4 kolumner så får du du 8 kolumner i kopplingstabellen jämfört med två kolumner om du kör syntetiska primärnycklar.

Det där med anomalier med syntetiska PK förstår jag inte, man är med det 100% säker på att man inte har dubletter. Och självklart har man foreign key constraints och valideringar av data ändå.

En primärnyckel ska vara unik och inte ha en mening, då denna "mening" kan ändras, personnummer är ett exempel.

Vad gäller prestanda så är joins mellan char avsevärt mycket slöare jämfört med integers. Så pass att det kan orsaka upplevd seghet med mycket data. Och vad gör man då, designar om databasen? Njae....

Adestro 2011-03-14 14:15

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20397671)
Det märks att du inte vet hur relationsdatabaser fungerar. Man garanteras att inga anomalier inträffar när man använder naturliga PK, det har du inte när du använder syntetiska PK. Redundans blir det inte om man använder naturliga PK. Själva nyckeln i sig kan aldrig dubbellagras.

Det där med texterminal förstod jag inte? Jag använder grafiska verktyg att analysera data med.

Vi kan kasta in tillverkare/märke som en VARCHAR i produkttabellen också. Blir mindre kul, ur prestandasynpunkt, när kunder ska kunna filtrera produkter efter färger, storlekar, tillverkare, etc. :)

Clarence 2011-03-14 16:45

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20397661)
Att optimera prestanda är inget självändamål.

Jag skulle påstå att prestandaoptimering alltid är ett självändamål. Oavsett om det är 1 eller 1 miljon användare vill man ha en rapp databas som inte tar onödigt mycket resurser i anspråk. Skalbarhet är den viktigaste relaterade aspekten, men den blir oväsentlig vid för dålig prestanda då lösningen blir för kostsam för att skala upp.

Citat:

När man bygger en databasstruktur i en relationsdatabas så är det viktigaste att man får en inbyggd kvalitetskontroll på informationen man lagrar.
Nej, när man bygger en databasstruktur där en inbyggd kvalitetskontroll i allmänhet är otroligt viktiga aspekt av datahanteringen är kvalitetskontrollen primär. De allra flesta moderna webbapplikationer faller utanför denna kategorin.

Citat:

Då är naturliga primärnycklar en naturlig del av det arbetet. Främmande nycklar som då länkar till primärnycklar är en annan del. Atomära egenskaper ytterligare en. Att följa normalformernas definitioner ytterligare en.
Och om du sedan tittar på uppstickarna som växer snabbast och ligger i framkant i utvecklingen så har de en helt annan uppfattning. De-normalisering, eventual consitency, begränsning av joins, uteslutande av foreign keys osv är deras mantra. Sanningen ligger väl snarare i att varje applikation har olika aspekter som väger olika tungt. Att påstå att man måste följa ACID för att använda en relationsdatabas eller säga att data endast får sparas i 3NF är för mig ungefär lika sant som de som att man ska använda moderna graf-databaser till precis allt.

Citat:

Att använda syntetsiska primärnycklar ska manundvika och endast använda i de fall när det inte finns en naturlig primärnyckel eller där en naturlig primärnyckel kommer att bestå av orimligt många kolumner (typ; >7 kolumner).

Den "prestandavinst" man vinner på att använda syntetiska PK är oftast så marginell att den inte är värd att beakta, i de flesta fall. Man förlorar så otroligt mycket mer vad gäller den inbyggda kvalitetskontrollen av information när man gör det.
Din tanke där är lika mycket på utdöende som den är på ingång skulle jag vilja påstå. Det blir allt vanligare med system där andra primärnycklar än ett auto increment används. Men vad som blir ovanligare är system där man försöker hitta en unik identifierare som redan finns i varje rad. Då blir man nämligen tvungen att definiera sin data mycket hårdare än vad man kan i de flesta agila miljöer eller mer än vad man vill i de flesta miljöer med användargenerat innehåll. Dessutom är det som redan påstått rent kasst ur prestandasynpunkt. Den prestandavinst som görs på de flesta primary key lookups är högst väsentlig, både med och utan joins och när det gäller innodb (vi pratar trots allt mysql här) är den även högst relevant för alla användning av sekundär-index.

Citat:

Använder man naturliga PK så kan man som människa utföra okulärbesiktning av data på ett enklare sätt än med syntetiska PK. Du kan m a o direkt se om set står NOK i en kolumn där du för väntar dig att det ska stå SEK, men om det står 4 i stället för 7 så finns det inget naturligt sätt för en människa att avgöra om informationen är korrekt eller inte. Man måste ha mycket mer komplexa sätt att kontrollera om informationen är korrekt och det innebär att det är lättare att man får strukturella fel i sin databas och sina applikationer.
Ja, jag skulle påstå att det är den enskilt största nyttan med naturliga PK, men för mig är den verkligen sekundär. Det är väldigt sällan man manuellt går igenom databasen för de allra flesta webbapplikationer och om man då måste skriva en join-sats för en kolumn eller två för att kontrollera finner jag inte så intressant.


Alla tider är GMT +2. Klockan är nu 02:43.

Programvara från: vBulletin® Version 3.8.2
Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Svensk översättning av: Anders Pettersson