[ dzaivan @ 06.11.2012. 12:57 ] @
Veliki pozdrav svim forumasima,
imam jedan veliki problem koji me muci vec mesec dana (i vise).

Tabela izgleda ovako:
Code:
CREATE TABLE IF NOT EXISTS `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT '0',
  `type_id` smallint(3) DEFAULT '0',
  `acdate` timestamp NULL DEFAULT NULL,
  `amount` decimal(11,2) DEFAULT '0.00',
  `total` decimal(11,2) DEFAULT '0.00',
  `country_code` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `currency` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `country_code` (`country_code`),
  KEY `type_id` (`type_id`),
  KEY `date` (`date`),
  KEY `currency` (`currency`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC AUTO_INCREMENT=3499518 ;


Objasnjenje: user_id - vlasnik racuna(Cardinality: 82129), type_id (Cardinality: 18)- vrsta racuna, acdate - datum, amount - iznos koji se upisuje na racun korisnika, total - novo stanje na racunu korisnika, country_code - zemlja (Cardinality: 18), currency - valuta(Cardinality: 18), active - status da li je aktivan upis ili ne...

Mozda nije najsrecnije resenje za pamcenje stanja i upisa na racunima korisnika ali je za sada tako (Ukoliko imate predlog i primere kako to bolje uraditi slobodno mi napisite :) )
Prilikom upisa novog stanja (kolona total) na racunu, prvo iscitavam stanje prethodnog stanja na racunu (kolona total) tj. iscitavam zadnji insert u tabelu account i upisujem novo stanje, sve to je uokvireno u okviru transakcije i lokovano sa "for Update"
I to sve super radi, ali krenule su da mi se javljaju cudne greske i to od kada je tabela narasla na preko 2 mil upisa (mesecno se u tabeli upisuju 250 000 inserta). Prilikom velikog saobracaja MySQL ili ubaci phantom read ili ne procita ni jedan row za tog korisnika vec mi php naredba koja cita mysql resurs javlja da select nije nasao ni jedan row... I onda vraca stanje korisnika na nulu + novo insertovani amount, i voila eto mene sa jos manje kose....
Query i kod izlgeda ovako:

Code:

Start Transaction;
jako puno querija

SELECT * FROM account
            WHERE user_id=45 AND
                  type_id=7 AND
                  currency = 'RSD' AND
                  country_code='sr' AND
                  active=1
            ORDER BY acc_id DESC LIMIT 1 FOR UPDATE;

<php-ov kod koji uzima stanje i sabira sa novim iznosom koji treba da se unese>

INSERT INTO accounts (user_id, type_id, acdate, amount, total, currency, country_code, active )
                        VALUES 
(45, 7, $today, 55, 55+prethodno_stanje, 'RSD', 'sr', 1)

jako puno querija

ako nije bilo greske
Commit;
ako je bilo 
Rollback


Php-ov kod ne vraca gresku, exception su svuda po kodu. Mysql transakcija je velika i ima u sebi 100 - 200 inserta (nikad za isti user_id u okviru jedne transakcije).

Pitanje - da li je moguce da transakcija zbog malo RAM-a (2 Gb na serveru) ili zbog prezauzetosti krece da brlja i da ne ispunjava osobinu konkurentnost.

(Server: Localhost via UNIX socket, Server version: 5.0.92-community-log ) Da li bi mozda pomoglo da predjem na 5.5 ?
my.cnf podesavanja:
innodb_log_buffer_size = 64M
innodb_buffer_pool_size=1024M
innodb_log_file_size = 256M
innodb_lock_wait_timeout=50
isolation-level je Repetable-read - probao serializable i ne ide

Hvala svima na odgovorima




[ bogdan.kecman @ 06.11.2012. 17:03 ] @
nemam kad da sve procitam na tenane ali
1. sta znaci "mnogo querija", zasto ti ja ta transakcija tolika? zar ti nije bolje da to podelis na manje transakcije
2. da li si siguran da ti mysql ne vraca greske, ovo ti je najverovatnije problem, tebi transakcija pukne na pola (posto je transakcija velika, imas ih mnogo, a transakcije na mysql-u su in-memory a ti ni rama nemas i onda pukne) i bude vraceno stanje pred transakciju a ti to ne ishendlujes kako valja... to je kod ovakvih problema 99.999% razlog
3. da li si gledao innodbmonitor? sta ti kaze, kakvo je stanje, kako stojis sa lokovima, semaforima... ?

sa 2M rekorda to sve generalno ne bi trebalo da bude problem, ali
1. koristis 5.0 mysql koji je prijatelju mnoooooooogo bajat + innodb, dakle digni to na novi 5.5, em je innodb znacajno brzi em bolje trosi ram, em se bolje skalira ...
2. ibp ti je 1G a imas ukupno 2G rama na masini, danas kada je ram toliko jeftin, to je zastrasujuce losa ideja... ti sa mnogo konekcija pojedes sav ram koji imas i garantovano kreces da swapujes, proveri potrosnju rama kada ti je veliki load
3. pogledaj mysql error log, vidi da li imas restarte mysql-a za koje nisi znao
4. zasto ne ides sa read [un]committed ? repeatable read je mnogo sporiji
[ dzaivan @ 07.11.2012. 16:44 ] @
Covek,

hvala puno na odgovoru.
Citat:
nemam kad da sve procitam na tenane
Molim te, pogledaj natenane kada imas vremena, ovo me bas muci. Ako uspes da mi pomognes vodim na krku :) (sledeci put kada se zateknes u Nisu.)

1. zasto imam puno querija u trnasakciji - na zalost takav je proces, to je neki slozen dogadjaj koji obuhvata puno update-a i to ne mogu da izmenim. Mozda bih mogao sa nestovanim transakcijama, ali mislim da to ne podrzava Mysql.

2.
Citat:
da li si siguran da ti mysql ne vraca greske
- nikad naravno nisam 100% siguran, mogu da buljim u kod nedelju dana i da ne nadjem nesto ocigledno, ali recimo 99% da nista nisam ispustio.

3.
Citat:
da li si gledao innodbmonitor?
eto npr. nisam znao da moze monitor da pamti u tabeli iz koje mogu posle da citam. Sve sam radio sa SHOW INNODB STATUS, u prilogu je output


Citat:
sa 2M rekorda to sve generalno ne bi trebalo da bude problem, ali
1. koristis 5.0 mysql koji je prijatelju mnoooooooogo bajat + innodb, dakle digni to na novi 5.5, em je innodb znacajno brzi em bolje trosi ram, em se bolje skalira ...
2. ibp ti je 1G a imas ukupno 2G rama na masini, danas kada je ram toliko jeftin, to je zastrasujuce losa ideja... ti sa mnogo konekcija pojedes sav ram koji imas i garantovano kreces da swapujes, proveri potrosnju rama kada ti je veliki load
3. pogledaj mysql error log, vidi da li imas restarte mysql-a za koje nisi znao
4. zasto ne ides sa read [un]committed ? repeatable read je mnogo sporiji


Vec sam narucio server sa vecim RAM-om (16Gb) uskoro prelazim na njega, pa cu videti da li se javlja greska. I da, citajuci tvoje prethodne postove rekao sam hosting kompaniji da stavi 5.5 na novom serveru.

REcimo da novi RAM i server 5.5 rese moje probleme - ono sto mene brine je dalje povecanje saobracaja, da li je moguce da se nakon nekog vremena opet jave greske. Nikako mi ne ide u glavu da onaj select vrati npr. za korisnika da nema rezultata, a korisnik ima upise u bazu, a nekad vrati pogresan tipa predzadnji upis a nekad skroz pogresan... Kao da mysql zbog nedostatka memorije/bufera/neceg nema gde da skladisti podatke i krece da vraca pogresne rezultate za SELECT ... Razumem da se uspori (to se i desava)mysql ili da baci error (gledao mysql error log - nema restarta niti gresaka) ali da samo prolazi i vraca pogresne rezultate bez obavestenja to mi nikako ne ide u glavu...

Hvala puno na odgovorima
[ after @ 07.11.2012. 22:45 ] @
Citat:
zasto ne ides sa read [un]committed ? repeatable read je mnogo sporiji


read commited txn level eliminise innodb gap lockove, pa samim tim se smanjuje broj lockova/deadlockova i brze se procesuiraju upiti...

Narocito ima efekta kod pojedinih tipova upita.

E sada za ranije verzije 5.0/5.1 vazilo je valjda izmedju ostalog da je read commited mnogo manje istestiran od default repeatable read i da iako je eliminisao znacajan broj lockova/deadlockova javljale su se neocekivane situacije/bugovi....pretpostavljam da je u 5.5 to na jos vecem nivou tj. da tih bugova vise nema ili su svedeni na minimum.

Ne racunajuci u bugove read commited i bin logove/replikaciju...

Pozdrav.
[ bogdan.kecman @ 08.11.2012. 03:27 ] @
pazi ovako, ili ti nisi negde ishendlao gresku koju ti je vratio mysql ili si pronasao neki mnogo gadan bug

dakle ako ti imas

begin;select; insert; update; update;update;select;insert;select;update;update...commit;

ne postoji nacin da je neki od inserta "izgubljen" ili da neki update nije prosao, dakle jedini nacin da se to desi je da ti na pola transakcije nesto prsne (recimo resetuje ti se ceo mysql server) a ti ne skapiras da ti je prslo i nastavis iz tvoje aplikacije da rokas dalje tu transakciju i na kraju odradis komit .. tako da izvrsis samo drugi deo transakcije .. potpuno je nemoguce da ti fali "samo sredina" ... ovo cesto moze da bude slucaj kada se isplati nacukati stored proceduru koja izvrsava tu transakciju tako da skines problem sa php-a ... ja nisam neki fan stored procedura ali nisam ni obozavalac php-a u enterprise okruzenju ..

sa toliko malo rama velika transakcija, posebno ako ih ima dosta paralelno moze da dovede do toga da ti prsne mysql ali to moras da vidis u logu, gledaj error log za to da se mysql sam zabo i gledaj sistem log za to da ti mozda kernel nije zabo mysql zato sto je povukao previse rama ..

tu je sve uvek dodatno sumnjivo kada je php u pitanju, koliko traje ceo taj proces, koliko rama on zauzima, dal je mozda php resio da prsne, da li koristis persistentne konekcije etc etc ... mnogo stvari koje mogu da podju naopako .. jos ako ti je php na istoj masini kao i taj mysql eto belaja...
[ Shinhan @ 08.11.2012. 13:09 ] @
Citat:
nikad naravno nisam 100% siguran, mogu da buljim u kod nedelju dana i da ne nadjem nesto ocigledno, ali recimo 99% da nista nisam ispustio.


Kad se desi MySQL greška, to neće dizati exception u PHPu.

Znači, da bi bio siguran da nepostoje MySQL greške, moraš uraditi sledeće:

Za svaki query hvataj šta vraća. Ako je to === false, onda postoji greška koju možeš videti sa $mysql->error ili mysql_error() (Ako još uvek koristiš mysql_ funkcije).

Znači MySQL greške ne moraju biti samo sintaksne. Možda ima neki deadlock i ako ne proveravaš šta ti MySQL vraća nećeš znati šta se desilo.

Još jedna stvar. Da li sigurno radiš samo SELECT i INSERT? Pošto ako bilo gde u tom procesu radiš TRUNCATE ili bilo koju drugu naredbu koja prouzrokuje implicitni commit to može praviti problema sa transakcijom.