Getting INNOdB to work..

xces

Heya, I have downloaded and installed the 3.23 version of MySQL. Set all the correct options as found in the manual for getting INNOdB to work. INNOdB works fine, i have a table of type INNOdB but i can't seem to use transactions..

My config file is attached to this thread as .txt file.

When i start: mysqld-max.exe (on a win95 system) the process is loading. I have a database called hoffice and a table called autos. But when i start the command line mysql.exe and type:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `autos` (`omsch`) values ('test');
Query OK, 1 row affected (0.33 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

Can't seem to figure out what i do wrong...

[deleted]

What's the problem?

All you say is that it doesn't work, but what do you mean by that?

xces

I am curious if my configuration file is ok, and how the transactions work in Innodb..

[deleted]

But and what I'd like to know is what makes you say that transactions are not working for you right now.

xces

Hey vinc, in my understanding the code should read like this:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `autos` (`omsch`) values ('test');
[b]Query OK, 0 rows affected (0.06 sec)[/b]

mysql> COMMIT;
[b]Query OK, 1 row affected (0.33 sec)[/b]

and not like this:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `autos` (`omsch`) values ('test');
Query OK, 1 row affected (0.33 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

Or am i wrong about INNODB transactions?

[deleted]

Have you checked to see wether the record actually is inserted into the table?

xces

Yes i did and the record IS inserted, but my problem is this:

As i said before, Topspeed inserts 100.000 records in 10 seconds, like it buffers all data to store and when the commit happens it commits all stuff to the database.

Now i wonder if mysql is supposed to react like this or does it also buffer like i was hoping it should do? (and get the incredible performance)

[deleted]

So there's nothing wrong with the transactions themselves, you're just curious about how they are implemented in MySQL's internals.

I'm sure that the innodb site can tell you alle about that, but one thing to note is that while transactions do allow you to insert at higher speed, you still pay a price durin the commit, when all records are updated all at once. You get to insert many records at high speed, but you'll have to wait longer during the commit.

Some databases buffer all the inserts and don't write them to the datafiles until you commit. This means lightning fast inserts and rollbacks, but very slow commits.

Other databases write the changes to the datafiles immediately and buffer any 'old' records as an 'undo' file. This gives slower inserts and rollbacks, but much faster commits.

The second method is more efficient because about one in ten transactions ends in a rollback, the other nine are commited, so having a high commit-speed is usually better than a high insert speed (because how often do you find yourself inserting 10k records/second?)

xces

You are right, i am just making a benchmark programme to see which one is the fastest.. 🙂 Thanx for your help

[deleted]

Be carefull about benchmarking straight selects and inserts, they don't give a realistic indication of a database's performance.

MySQL is frighteningly fast at doing simple selects, but if you start spamming a few insers inbetween the selects, performance drops dramatically.

And you may want to try postgresql 7.2+ too, it also has proper transactions and performs very well under heavy load (beter than mysql)

csn

Originally posted by vincente
Some databases buffer all the inserts and don't write them to the datafiles until you commit. This means lightning fast inserts and rollbacks, but very slow commits.

Other databases write the changes to the datafiles immediately and buffer any 'old' records as an 'undo' file. This gives slower inserts and rollbacks, but much faster commits.

Which way does PostgreSQL do it?

[deleted]

I'm not really sure which method they use (shame on me), but I do know that PostgreSQL uses the WAL (write Ahead Logging) method, where transactions are written to a logfile rather than to the datafiles themselves. That means the data pages don't have to be flushed to the datafiles on every commit, instead the database can do it's flushing when the server is not so busy.

http://www.postgresql.org/idocs/
http://www.postgresql.org/idocs/index.php?wal.html

Sxooter

Originally posted by csn

Which way does PostgreSQL do it?

Postgresql uses a system called MVCC. In this system, each user sees a unique (to their view) snapshot in time of the database from when their transaction started. It's complex, it's fast, and it's better than row level locking. It's the same basic methodology that Oracle uses.

http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/mvcc.html

Basically, when you committ, the work's already done. If you roll back, it just marks the new tuples as dead and moves on.

This system makes postgresql VERY fast for large transactions, and it also is immune to the problem many databases have of having a rollback segment that gets full.

In Oracle and many other transactional databases, you HAVE to commit every x thousand rows or the roll back segment will overflow. In Postgresql, the database IS the rollback segment. If you run out of space from a transaction in postgresql, then you filled up your hard drives.