transactions & locking & mysql

Anon

hi!

i got to the point where i have to perform some operations on 100.000 records [or more] and between the operation i dont want anyone to write/read from database.

1) how do i lock everything? is it possible to do it?

2) how long will it take mysql to perform some 100.000 queries [select, update, delete, insert]? can someone just give me approximate value? will that be 10 seconds, 10 minutes, 1 hour?

--- i also have to go with transactions. does mysql support them yet?

Matjaz

Anon

1 read the mysql manual

2 anything from 0.001 sec to three weeks, depending on what your query is.

3 no.

Anon

I'm a relative newbie to php & mysql, but I work with real-time software a lot. I would
try to solve a query time problem by copying the table(s) I do my query on.
On my os(win):

$did_it_work = exec
("copy \apache\db\table. my_table.");

On a better os you would use cp and single forward slashes and a different path...
All this is assuming disk space is not a problem, you may still have to lock while copying, but you may then take your time on the select query(ies) without preventing access by others, but applying the changes
(update, delete) to the shared table(s).
If the number of records to update/delete is much smaller than the total number of records, this may be the most practical solution... Good luck !

Anon

But he doesn't want users to be able to read/write.
This solution still allows that on the original tables, which is not what he wants.
Besides that, you need hefty access rights to copy tables (they should be well protected) so that requires more code than you might think.

If this was a solution, I would prefer to let MySQL copy the data over using the COPY command, because mysql will lock the tables "properly", where COPY just grabs what it can.

Anon

answers like: no, read the manual dont help at all. next time just dont write them if the information they carry is equal to zero.

i guess i will still have to find something to "emulate" transactions or something, i dont know. every useful information in welcome.

happy new year to everyone!

Anon

While Vincent may be terse, the truth is, that depending on your hardware and what you are doing, it really could take anywhere from minutes to days to process your data.

What I'd really recommend is to take a second looks at your requirements for this application, and see if you really need to lock the whole database to reading while you update.

I think your best bet here is gonna be to move to postgresql or some other transactional database. With postgresql and a quick ad dirty test I threw together to test it, I can update 100,000 records in 196 seconds before a vacuum analyze and 170 seconds after.

Note that I "wrapped" all the upates inside a begin;end; pair, so none of the changes showed up in the table until the end; command was executed.

This would give you the appearance you want, without needing to lock the database to reads while updating.

Anon

I'd like to (tersely) second the motion that "get a real dbms" might be the best (if least welcome) answer. You have no idea how easy life can be in these kinds of situations until you have used a real dbms with real transactions for a while...

Anon

1) the manual is quite clear about it, I'm not going to spoon-feed you what is already in the manual

2) there really is no good way of telling how long it will take because it depends on 100 factors.

3) what more do you need? you ask: does mysql support transactions? answer: no.

Anon

1) the manual is quite clear about it, I'm >not going to spoon-feed you what is already >in the manual

then dont reply to it.

2) there really is no good way of telling >how long it will take because it depends on >100 factors.

yeah i believe you that. i just thought you could give me some approx value like maybe an example you had with your work or something. i know you cant give me the exact value. do you have some research data/your experiences? anything.

3) what more do you need? you ask: does mysql support transactions? answer: no.

:-( thanks! i thought i heard about it but i guess "mysql guys" are about to implement it. is postgresql a better/more reliable/transactions enabled database? --again from experiences .

thanks!

Matjaz

Anon

1) whatever. You know where to get the information. If you don't like it, you're on your own.

2) I have done queries on 5000 records that took 10 mins on a P2-300 linux 128mb, and queries on 10M records that took 20 seconds on a p2-400 128mb.
In general, the bigger your tables, the more you depend on memory and disk-speed.
The only way to find out how long your query is going to take is to try it out.

3) There are quite nasty discussions going on about MySQL vs Postgres.
Personally I think both have their advantages. Postgres supports transactions and a lot more SQL statements than MySQL, but the current release (7.0.3 if I'm not mistaken) is not very fast compared to MySQL.
As for stability, that's up to you and your sysadmin.
MySQL has a tendency to slow down under extreme stress, but keep going, where Postgres will decide it's too much and stop (or so I've heard).
But it's the system admin's job to make sure all apps have enough memory and CPU power

Anon

I'll throw in my 2 cents on pgsql versus mysql

This is my experience with both, YMMV.

My experience has been that as of 7.0.3, you can't kill postgres with too many queries/too much work, as long as you're configured for enough back ends to handle the load. Note that the current max of 1024 backends is only a problem for scaling onto truly huge hardware, like n-way ultrasparc or SGI boxen.

On my test boxes, postgresql performance is about the same as you add users. Each user runs slower individually, since you're spreading the database thinner, but the number of responses per second stays the same, from 1 to about 700 simo connects.

Mysql is MUCH faster at simple queries, or with one or two users. This is quickly overcome when used in a real world environment with mixed reads/writes, where postgresql's non-blocking autotransactions allow hundred of users to read and write to the same table at the same time with reliability and data integrity.

My experience has been that MySQL is the one that just stops under load, but I mean PARALLEL load, not high load from one task/thread. Postgresql has never gone down under load for me, but in the past (6.5.3 and before) I have made it unresponsive for minutes at a time with goofy queries. I've never killed 7.0.3 once, period (except with the kill command :⁾ with gunky queries, or high parallel load, but I have slowed it down to near unresponsiveness a few times under extreme loads (>500 simo connections.)

I grew up with non-transactional databases, that had near useless joins/unions/intersects that were so slow, we did everything with temporary tables, much like how you pretty much have to with most mysql stuff, since it just doesn't have most of the features of a higher end dbms. I don't mind working around missing features in it, but I find that after writing the code for building temp tables in mysql, I could have one sql line, in the database as a view, in postgresql, and my app just has a "select * from view_name where..." statement in it. If the data changes, I don't have to rewrite a ton of mysql queries and more, just one view in postgres.

After a vacuum analyze on the table, and with judicious use of indexes, I find the performance to be similar under most situations (i.e. 20+page views a second with either database) that I don't really notice a difference in performance anymore.

Anon

Read my reply to Vincent for my take on postgresql, also, look for an article from Tim Perdue here on php builder about why he's switching (switched?) from mysql to postgresql due to poor performance under high parallel load.

The honest answer is, you don't know which db will work best for what you want without testing and more testing, but the more I use postgresql, the less I like mysql. It's got it's own tricks and tips to speed things up, but overall, it runs very quickly on a big box, like a dual PIII 800 or something with 512Meg ram or more, and scales to hundred of simos without a hitch.

MySQL is great for read mostly, write a little databases, postgresql is great for a mixed read/write environment, and they tend to overlap somewhere in between, but until you build part of your app, you won't know really.

I would highly suggest using db abstraction so you can switch from one database to another easily and quickly for testing or if/when a new database (version) comes along you need to use for performance reasons.

Anon

great help guys, i really appreciate it. yes scott, i am using db abstraction layer so i can switch easily if needed. the only think i shouldnt be using is db->affected_rows [php lib] coz they dont work with "real" databases. i had that example when switching from mysql to oracle. one should always use "select count" except affected_rows.

you are right, i should test it somehow but i guess i wont be able to run "a real test" until the projects go live and gains its members/subscribers.

it will be a system for advertising, performing lots of read&write operations every seconds. i hope shared hosting can survive this!

Matjaz

Anon

That's the kind of database you have to write your own benchmark for testing it. You can use wget and/or ab (apache aBuse or ApacheBench) and php or perl to write a pretty intensive test program for it to see what kind of parallel load it will be able to handle.