mysql begin ... commit help w/php?

fandelem

I'm trying to write a re-indexing program in PHP.

Essentially, it adds about 10,000 rows every 5 minutes (the entire PROCESS takes 5 minutes.. all the while it's doing the inserts).

I believe that it would be much faster if I did not have to "auto-commit" each transaction. Instead when the process finishes, it just issues a commit.

According to MySQL docs, it does support the begin ... commit architecture.. although I have not been too successful in utilitizing it..

Has anyone?

I've tried this:

mysql_query("begin");

// LOTS of inserts/updates

mysql_query("commit");

and yet it functions just as fast (rather "slow") as without those two statements...

am I missing something?

cheers,

tomhath

I've never played with transactinos in mysql, but I did see this in the manual. Perhaps the parts about "transactions safe tables" and "non-transactions safe tables" are what's getting you:

6.7.1 BEGIN/COMMIT/ROLLBACK Syntax

By default, MySQL runs in autocommit mode. This means that as soon as you execute an update, MySQL will store the update on disk.

If you are using transactions safe tables (like BDB, InnoDB, you can put MySQL into non-autocommit mode with the following command:

SET AUTOCOMMIT=0

After this you must use COMMIT to store your changes to disk or ROLLBACK if you want to ignore the changes you have made since the beginning of your transaction.

If you want to switch from AUTOCOMMIT mode for one series of statements, you can use the BEGIN or BEGIN WORK statement:

BEGIN;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

Note that if you are using non-transaction-safe tables, the changes will be stored at once, independent of the status of the autocommit mode.

fandelem

actually, i had read that portion -- except for the one part about transaction-safe tables (ie. innodb.. mine were myisam) -- thanks for that tidbit.

so i tried it. altered my tables to be innodb type.

still was doing autocommits after changing it to:

mysql_query("set autocommit=0");

//lots of transactions

mysql_query("commit");

what i was wondering if anyone knew of perhaps PHP is at fault -- ie. adding in a 'commit' after any mysql_query... i think this actually IS the case, as proof of the table still auto-committing...

can anyone find anything anywhere disproving this?

-k

nnichols

do you have mysql compiled with InnoDB support?

SHOW VARIABLES LIKE "%have%"

Should list table types supported

ahundiak

You need to see what version of mysql you have and what the underlying storage system is. mysql has only recently started to offer support for transactions. Most version will accept transaction commands but not do anything with them.

You might try using mysql directly to test if transactions are actually supported. Begin a transaction, make a few updates then try to rollback.

fandelem

mysql> show variables like '%have%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_bdb      | YES   |
| have_gemini   | NO    |
| have_innodb   | NO    |
| have_isam     | YES   |
| have_raid     | NO    |
| have_ssl      | NO    |
+---------------+-------+
6 rows in set (0.28 sec)

ah, crap.

so i must recompile with innodb support. okay. i will get back to you guys tomorrow :>

fandelem

Okay.. innoDB is insanely slow compared to MyiSAM. Wow. It looks like they have lots of work to do to bring this database type up to speed.. or maybe I'm just doing something wrong? I added the compile args.. and added the my.cnf file to mysq/data.. and show variables now shows it enabled.. but it never seems to commit.. it just times out.. does anyone have a working example, maybe?

ahundiak

I think you are looking at evolution in progress.

Transactions do slow up many database operations. Mysql is just now starrting to support transactions and it probably will be quite some time before performance is acceptable.

You might want to consider postgresql if you really need transactions.