Kategoriarkiv: MySQL

Gratis affärsidé: Webbtjänst som optimerar din databas

Många programmerare eller administratörer stöter förr eller senare problem med sin databas då denna blir överbelastad av en eller annan anledning.

Vad denna överbelastning kan bero på finns det många svar på (några har jag skrivit om tidigare). Men vad jag tror skulle behövas är en tjänst som man kan använda över webben som ansluter till en MySQL/Postgres-databas och kan ställa ett antal SQL-frågor, analysera tabeller samt monitorera SQL-frågor under några dagar för att sedan ge ett kvalificerat svar (rapport) på hur man kan optimera sin databas.

Nu har jag ju inte lagt ner så mycket efterforskningar för att se om det finns en sådan tjänst. Förmodligen finns det program som man kan använda i dagsläget för att optimera sin databas men inget som inte kräver installation och fungerar över Internet.

Sphinx wildcards och MySQL memory tables

Har tyvärr inte fått gjort så mycket gjort på mina projekt i helgen pga sociala aktiviteter men ett och annat har jag hunnit med och bl.a så har jag lärt mig hur man gör s.k ”wildcard” sökningar i Sphinx samt hur MySQL:s memory tables fungerar (och inte fungerar):

**

Genom att sätta min_prefix_len = 1 samt enable_star = 1 i sphinx.conf så är det möjligt att söka efter exempelvis foo* i Sphinx. Glöm inte att starta om searchd processen.

**

Minnestabeller MySQL är bl.a bra om man vill göra snabba sökningar och då går dessa direkt mot minnet istället för disk. Jag testade att stoppa in 12Mb data i en tabell och tittade sedan hur mycket minne MySQL processen tog:

Först så skapar jag en tabell med engine=memory och sedan populerar jag den med information enligt:

set @@tmp_table_size= 4294967295;

set @@max_heap_table_size= 4294967295;

CREATE TABLE lookup (id INT, INDEX USING HASH (id),`text` varchar(255) NOT NULL) ENGINE=MEMORY CHARSET=utf8;

INSERT INTO lookup SELECT id,text FROM texts;

Sökningar direkt i texts tar ca 1.5 sekunder (InnoDB med UNIQUE KEY) men sökningar mot minnestabellen lookup tar 0.3 sekunder! Dock så tar MySQL processen 472MB mer minne och då har jag enbart stoppat in 12MB data i memorytabellen. Ouch!

Läs mer på MySQL.com om The MEMORY (HEAP) Storage Engine.

MySQL köps upp av Sun

Läste just på IDG att svenska databasföretaget MySQL köps upp av jätten Sun för 6,3 miljarder kronor! Grattis Monty & C:o. Synd att de inte hann börsintroduceras för då hade jag säkert tjänat en hacka på dem:

”Hjärnan bakom MySQL är finländaren Michael ”Monty” Widenius. Han startade MySQL tillsammans med svenskarna Allan Larsson och David Axmark i mitten på 1990-talet.” -nyteknik.se

Sphinx Deltaindex

Har nu gjort om hur Sphinx gör sin deltaindexering och gick först från 6 timmar ner till 2 timmar och nu är jag nere i ca 1 minut:

$ time /usr/local/bin/indexer --rotate deltaindex_swedish
Sphinx 0.9.8-dev (r985)
Copyright (c) 2001-2007, Andrew Aksyonoff

using config file '/usr/local/etc/sphinx.conf'...
indexing index 'deltaindex_swedish'...
collected 25375 docs, 18.8 MB
sorted 2.0 Mhits, 100.0% done
total 25375 docs, 18824703 bytes
total 58.788 sec, 320212.09 bytes/sec, 431.63 docs/sec
rotating indices: succesfully sent SIGHUP to searchd (pid=15596).

real    0m58.817s
user    0m1.120s
sys     0m0.140s
$

Hur gjorde jag då? Jo jag sket i mina egna SQL-frågor och gick istället på det som rekommenderas:

Example 3. Fully automated live updates

 

# in MySQL
CREATE TABLE sph_counter
(
    counter_id INTEGER PRIMARY KEY NOT NULL,
    max_doc_id INTEGER NOT NULL
);

# in sphinx.conf
source main
{
    # ...
    sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(id) FROM documents
    sql_query = SELECT id, title, body FROM documents \
        WHERE id<=( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
}

source delta : main
{
    sql_query_pre =
    sql_query = SELECT id, title, body FROM documents \
        WHERE id>( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
}


Misshandlad databas

Är detta ett tecken på att jag kanske misshandlat MySQL databasen lite väl mycket?

 

mysql 8962 0.0 0.0 21376 1552 ? S Dec17 0:00 /usr/bin/mysqlcheck –defaults-file=/etc/mysql/debian.cnf –all-databases –fast –silent
mysql 8963 0.0 0.0 17548 1744 ? S Dec17 0:00 perl -e $_=join(””, <>); s/^[^\n]+\n(error|note)\s+: The (handler|storage engine) for the table doesn.t support check\n//smg;print;

Optimera din MySQL databas

Jag har suttit många månader och felsökt varför vissa saker går långsamt i MySQL när jag utvecklat bloggsökmotorn och i detta blogginlägg försöker jag sammanfatta de erfarenheter jag lärt mig på vägen:

  1. InnoDB är många gånger att föredra framför MyISAM. Jag fick jättemycket problem med låsningar då jag skriver och läser mycket till stora tabeller. MyISAM är generellt snabbare på INSERT’s
  2. Undvik typkonverteringar. I python exempelvis så görs detta automatiskt ibland och då kan det leda till att MySQL måste göra typkonverteringar vid varje jämförelse.
    • – ”Avoid overuse of MySQL’s automatic type conversion. MySQL will perform automatic type conversion, but if you can avoid conversions, you may get better performance” (från boken MySQL: The definitive guide to using, programming, and administering MySQL 4.1 and 5.0, Third Edition)
  3. Använd EXPLAIN före SELECT manuellt för att se var flaskhalsar kan sitta.
  4. Försök att undvika FULLTEXT indextyper, en vacker dag så sitter du där med tonvis med information och sökningarna tar flera sekunder. Snegla istället på Sphinx.
  5. Tråda så mycket som möjligt. Men var inte dum
  6. Slå på log_slow_queries i my.cnf och kolla loggen vilka frågor som går långsamt och försök optimera dessa.
  7. Bryt ner SQL-frågorna och utred vilket värde som tar lång tid att få fram.
  8. Kör SHOW FULL PROCESSLIST ibland och titta på ”Time” fältet där du ser hur lång tid frågor tar.
  9. Undvik ORDER BY RAND() då dessa frågor kopieras till en temporär tabell vilket tar tid.
  10. Ha full koll på dina INDEX så att de används på ett optimalt sätt. Inte för många och inte för få.
  11. Försök att tweaka my.cnf så att den stämmer överens med just din hårdvara gällande RAM-minne osv. Exempelvis så kan innodb_buffer_pool_size vara upp till 80% av minnesstorleken.
  12. Använd LIMIT där det är möjligt.
  13. Testa att lägg databasen på en separat hårddisk (datadir). Är standard /var/mysql på de flesta OS.
  14. Kör OPTIMIZE TABLE en gång i veckan eller en gång per månad.
  15. Ställ dig frågan att det kanske inte är databasen det är fel på utan kanske hur DU använder den.
  16. Läs mysqlperformanceblog.com samt Google är din vän.

Såja, hoppas det var allt. Fyller på om det är något som jag glömt.

Memcached för att snabba upp SQL-frågor

Har sneglat rätt länge på memcached som används mycket flitigt av Facebook. Memcached fungerar som en cache innan frågor går in till SQL-databasen och på så sätt så minskar belastningen. Det är också möjligt att sätta ett timeout-värde hur länge frågor skall cachas.

Min tanke för att implementera memcached är att låta en funktion ta hand om alla MySQL-frågor och där anropa memcached eller MySQL.

Står en del intressant på Webmasters Network här samt hos Linux Journal.

Så här skriver Facebook om memcached:

”a distributed memory object caching system. Memcached was not originally developed at Facebook, but we have become the largest user of memcached and actively contribute modifications back to the project. We have contributed substantial changes back to the project, boosting the software’s performance by over 20% and increasing its memory efficiency by over 30% while adding new functionality. ”

Sökningar i MySQL MyISAM FULL-TEXT

När bloggz.se kom upp i över en miljon indexerade blogginlägg så började MySQL att gå på knäna så jag trodde ju såklart genast att jag hade gjort något fel när det gäller optimeringen av de index som jag använder mig av. Så jag la ungefär en månad på att felsöka och försöka lista ut vad som var galet, visst hittade jag diverse fel som gjorde att sökningarna tog långsammare men tillslut så gav även jag upp och började titta på nya alternativ.

Eter ett tag så fann jag det jag sökte efter: Sphinx. Sphinx indexerar hela MySQL full-text databastabellen och gör den sökbar på långt under en sekund. Vilken lycka! Nu kan jag sova lungt igen.