Mysql izvēlās neefektīvus izpildes ceļus.

[marrtins]

Ja where nosacījumi nemainās, paņem iemet visus ID sanumurētus kādā cache un tālāk jau tikai atlasi vajadzīgos ID no cache un tālāk rindas paprasi mysql.

[Kasspars]

Šis nav tas gadījums.

 

Njā, tā izskatās. Jo lielāki rowi jau selekts lēnāks paliek.

Edited January 29, 2013 by Kasspars

[codez]

Ar MyISAM viss izskatās nedaudz labāk, bet

SELECT t.*
FROM (
    SELECT id
    FROM test
    ORDER BY id
    LIMIT 500000, 10
) o
JOIN test t
ON t.id = o.id
ORDER BY t.id

Laiks: 0.3

Izskatās, ka šijā gadījumā indeksi tiek noskenēti atsevišķi un tad atlasīti vajadzīgie ieraksti.

Bet MyISAM man neder.

codez, cik liels tev id buffer pools (show status like 'innodb%')? Izklausās pārāk maza atšķirība.

'Innodb_buffer_pool_pages_data', '8191'
'Innodb_buffer_pool_pages_dirty', '0'
'Innodb_buffer_pool_pages_flushed', '58661'
'Innodb_buffer_pool_pages_free', '0'
'Innodb_buffer_pool_pages_misc', '1'
'Innodb_buffer_pool_pages_total', '8192'
'Innodb_buffer_pool_read_ahead_rnd', '0'
'Innodb_buffer_pool_read_ahead', '4732'
'Innodb_buffer_pool_read_ahead_evicted', '304'
'Innodb_buffer_pool_read_requests', '58272196'
'Innodb_buffer_pool_reads', '136397'
'Innodb_buffer_pool_wait_free', '0'
'Innodb_buffer_pool_write_requests', '24084079'
'Innodb_data_fsyncs', '3036'
'Innodb_data_pending_fsyncs', '0'
'Innodb_data_pending_reads', '0'
'Innodb_data_pending_writes', '0'
'Innodb_data_read', '2314440704'
'Innodb_data_reads', '141139'
'Innodb_data_writes', '61550'
'Innodb_data_written', '2974566912'
'Innodb_dblwr_pages_written', '58661'
'Innodb_dblwr_writes', '809'
'Innodb_have_atomic_builtins', 'ON'
'Innodb_log_waits', '0'
'Innodb_log_write_requests', '2137581'
'Innodb_log_writes', '1283'
'Innodb_os_log_fsyncs', '1417'
'Innodb_os_log_pending_fsyncs', '0'
'Innodb_os_log_pending_writes', '0'
'Innodb_os_log_written', '1052165632'
'Innodb_page_size', '16384'
'Innodb_pages_created', '46935'
'Innodb_pages_read', '141128'
'Innodb_pages_written', '58661'
'Innodb_row_lock_current_waits', '0'
'Innodb_row_lock_time', '2782'
'Innodb_row_lock_time_avg', '2782'
'Innodb_row_lock_time_max', '2782'
'Innodb_row_lock_waits', '1'
'Innodb_rows_deleted', '0'
'Innodb_rows_inserted', '3145728'
'Innodb_rows_read', '23985373'
'Innodb_rows_updated', '2097152'
'Innodb_truncated_status_writes', '0'

Edited January 29, 2013 by codez

[codez]

Ja where nosacījumi nemainās, paņem iemet visus ID sanumurētus kādā cache un tālāk jau tikai atlasi vajadzīgos ID no cache un tālāk rindas paprasi mysql.

Tā kā datus ārā nedzēšu un tātad id ir pēc kārtas, tad pagaidām reālākais izskatās šādt variants:

SELECT * FROM test WHERE id>=500000 LIMIT 10

Laiks:0.15s

 

P.S. Laigan tas id tomēr var nebūt pēc kārtas, kautvai neizdevušās transakcijas atstāj tukšus id caurumus.

 

Bet vienalga gribētos izdomāt, kā taisīt paging-u 1M datiem.

Edited January 29, 2013 by codez

[marrtins]

Uzliec innodb_buffer_pool_size uz kādi 512MB un palaid kveriju divreiz (warm up cache) :P

[codez]

Uzliec innodb_buffer_pool_size uz kādi 512MB un palaid kveriju divreiz (warm up cache) :P

Jā, nomainīju bufera limitu uz 150MB (vairāk nevaru, jo 256MB VPS) un kverijs ar joinu tag izpildās 0.30 sekundē, bet bez joina 0.35 sekundēs.

Šķiet, ka tas pagaidām būs pietiekami labs risinājums.

 

 

P.S. Paldies marrtins.

 

P.S.S.

Vēl reālajā produkcijas tabulā saglabājās atšķirība.

Parastā selekta ar offsetu līdz pusei laiks ir 3.7s

Selektam ar joinu laiks ir 0.27 sekundes.

Edited January 29, 2013 by codez

[codez]

Pagaidām kaut cik apmierinošais variants priekš 1M ierakstu lapaspušošanas aizņem:

1)Iegūt ierakstus:

SELECT t.*
FROM (
    SELECT id
    FROM test
    ORDER BY id
    LIMIT 500000, 10
) o
JOIN test t
ON t.id = o.id
ORDER BY t.id

0.3s

 

2)Iegūt kopējo skaitu:

SELECT count(*) FROM test;

0.3s

 

Kopā 0.6s, kas jebkurā gadījumā nav nekas iepriecinošs.

[marrtins]

Tas 0.3s ar join ir uz reāliem datiem? Izklausās OK. Rēķini, ka tiem vajadzētu vēl lielāku poolu.

 

Nokešo to count, vai, ja nepieciešams realtiem - notrigerē ;)

 

Kā būtu, ja offsets iet pāri pa pusei, tad sortētu pēc ID desc un attiecīgi būtu offsets (count - īstais_offsets - limit), tb, LIMIT count-50000-10,10 Pēc tam ar php sasortē pretēji. Vienīgais, nez cik ātri tas PHP sasortēs...

[codez]

Tas 0.3s ar join ir uz reāliem datiem? Izklausās OK. Rēķini, ka tiem vajadzētu vēl lielāku poolu.

Jā, tas ir ar reāliem datiem.

Bet man šķiet, ka ar joinu vajag tieši mazāku pūlu, jo cik es saprotu, tad viņš skenē tos 500k ierakstus no indeksa, līdz ar to jāglabā tikai indekss un tikai pēc tam piejoino tos 10 ar visiem datiem.

Nokešo to count, vai, ja nepieciešams realtiem - notrigerē ;)

Laikam tā būs jādara. 

Kā būtu, ja offsets iet pāri pa pusei, tad sortētu pēc ID desc un attiecīgi būtu offsets (count - īstais_offsets - limit), tb, LIMIT count-50000-10,10 Pēc tam ar php sasortē pretēji. Vienīgais, nez cik ātri tas PHP sasortēs...

Ja taisa ar joinu, tad var turpat mysql pusē kārtot, jo offseta meklēšanu taisa ar desc, bet pēc joina atlasītajiem 10 ierakstiem uzliek asc.

 

Bet tagad radās jauna problēma, kas situāciju pamatīgi pasliktina.

Ir tā, ka ir vairāki desmiti sadaļas, kur katrā ir tie 1M ieraksti. Reālajā db ir vairāki desmiti miljoni ierakstu un kverijs ir

SELECT * FROM table WHERE category=15 ORDER BY id LIMIT 500000,10;

ar indeksu categry,id.

Kamēr darbojās vienas category ietvaros, bufferis palīdz. Bet tiklīdz kā sāk mainīt kategorijas, tā no buffera nav jēgas. Ok, man vēl ir pamazs buferis (150MB), bet ņemot vērā kopējo kategoriju un ierakstu skaitu, pat, ja varētu uzlikt pāris GB, tas neglābtu.

 

Tā kā man dati ārā netiek dzēsti, bet tikai lēnām nāk klāt un pagaidām ierakstu skaits vienā lapā nemainīsies, tad pēdējā ideja varētu būt piekabināt katram ierakstam savu page numuru. Bet tas ir tik neuniversāli, ka neceļās roka tādas lietas darīt.

 

Šķiet, ka nāksies izgudrot kādu jaunu datu lapaspušosanas paņēmienu.

[marrtins]

Bet man šķiet, ka ar joinu vajag tieši mazāku pūlu, jo cik es saprotu, tad viņš skenē tos 500k ierakstus no indeksa, līdz ar to jāglabā tikai indekss un tikai pēc tam piejoino tos 10 ar visiem datiem.

InnoDB gan dati gan index tiek mesti poolā. Laigan, protams, index uz integer kaut vai uz miljonu ir pāris mb un vēl pāris mb overhead datustruktūrām.

 

Kamēr darbojās vienas category ietvaros, bufferis palīdz. Bet tiklīdz kā sāk mainīt kategorijas, tā no buffera nav jēgas. Ok, man vēl ir pamazs buferis (150MB), bet ņemot vērā kopējo kategoriju un ierakstu skaitu, pat, ja varētu uzlikt pāris GB, tas neglābtu.

Domāju, ka nevajadzētu būt tik traki... to bufferpoolu jau arī kādreiz atbrīvo :) Gan jau tur ir algoritmi, kas interesantākās lietiņas "patur prātā", bet drazu izmet.

 

Tā kā man dati ārā netiek dzēsti, bet tikai lēnām nāk klāt un pagaidām ierakstu skaits vienā lapā nemainīsies,

Nu tad pieseivo statiskas HTML lapiņas katram atvērumam. Pārģenerē tikai pirmo reizi un jaunāko lapu, kamēr piepildās.

 

tad pēdējā ideja varētu būt piekabināt katram ierakstam savu page numuru. Bet tas ir tik neuniversāli, ka neceļās roka tādas lietas darīt.

Nu tas būtu tizli - papildus dati (piekam, visnotaļ nevajadzīgi) kā arī ja kas pamainās, tad tādai DB kautko pielikt/noņemt būs tāāds čžžž.

Edited January 30, 2013 by marrtins

[rpr]

ar lieliem offsetiem praktiski normāli nestradā neviena db.  kā jau marrtins teica, selektā tiek izmantots index, bet limits un offset reāli tiek veikts no atlasītiem datiem - attiecīgi sekventālais skans. to pašu var teikt arī par postgre un cik te ar solr vēl esmu ņēmies - tieši tas pats.

[daGrevis]

Tātad kāds ir universālais risinājums 1M datiem un pedžineišejam?

[rpr]

universāls risinājums šķiet nav, manuprāt šodienu tendence ielādēt postus skrollojot, ir vienkāršākais risinājums, kuru jau te kāds aprakstīja.

 

select id from posts where id>50 order by id limit 10;

 

attiecīgi pēc tam mēs dabūjam no selekta lielāko id, kuru jau lādējam atkal iekšā.

[daGrevis]

T.i., izmantojam ">" pret auto-increment lauku.

[mad182]

Ar šo arī ir nācies saskarties. Un tas laiks pieaug, pieaugot offset lielumam. 10m ierakstu dalīšanai lapās kverijs ar limit offsetu praktiski jau ir neizmantojams.