...

View Full Version : innoDB or myISAM? (updates per second)



sitNsmile
12-09-2011, 06:53 PM
Okay. So I have been trying to figure this out for a long time. starting to run some test now. but its so hard to figure out which is best.


I know innoDB is good with higher concurrent connections (which I desperately need)

So I have a table that has the information of the content, and I have another table that has so many +1s (such as stats, pageview sorta stats) it needs to be fast. and the locking of entire table with myISAM doesnt seem appetizing.

whats the best configuration for a table that deals with a lot of updates per second?

Old Pedant
12-09-2011, 09:15 PM
How many per second? "lot" isn't descriptive enough.

I used to feed MyISAM tables *new* data at the rate of 1GB every 20 minutes, but of course INSERTs don't have the locking problem that UPDATEs do.

sitNsmile
12-10-2011, 12:43 AM
For an example, lets just say anywhere from 500-1000 updates a second. Mostly due to pageview counters..etc

Old Pedant
12-10-2011, 03:30 AM
Ugh...yeah, that does sound high enough to maybe choke MyISAM. I guess all you could do is try it. You could certainly simulate it easy enough. The good part is that if it works on a small table, it's almost surely going to work on a larger one. After all, if you do have page scope conflicts, you are more likely to have them if you have fewer pages.

sitNsmile
12-10-2011, 05:38 PM
Ugh...yeah, that does sound high enough to maybe choke MyISAM.

Okay. but what I see the most is Locking. Where I may be using a lot of selects to identify what exist. and then a lot of updates to create the statistics. Running the selects which locks up some of the updates, I think could create discrepancies.

Old Pedant
12-11-2011, 07:09 AM
If you can, don't do the SELECTs. Use updates that incorporate the SELECT as part of a single SQL statement. Our use stored procedures. You are likely killing performance, anyway, by doing a SELECT followed by UPDATE. You have to make *two* round trips from PHP (or whatever you are using) to the DB server instead of the one that a well-written stored procedure would require.

sitNsmile
12-11-2011, 04:05 PM
If you can, don't do the SELECTs. Use updates that incorporate the SELECT as part of a single SQL statement. Our use stored procedures. You are likely killing performance, anyway, by doing a SELECT followed by UPDATE. You have to make *two* round trips from PHP (or whatever you are using) to the DB server instead of the one that a well-written stored procedure would require.

I wasn't aware you could incorporate a SELECT inside a UPDATE, what would be an example of that? also my main reference was multiple users, such as other users are running queries while some are using updates, which I see the cause for the locks.

Old Pedant
12-11-2011, 08:17 PM
Example:

UPDATE pagehits, members
SET pagehits.bypaidmembers = pagehits.bypaidmembers + IF(members.ispaid IS NULL,0,1),
pagehits.bynonpaidmembers = pagehits.bynonpaidmembers + IF(members.ispaid IS NULL,1,0)
WHERE members.memberid = $_SESSION["memberid"]

Kind of a silly example, as I would assume if you were keeping track of $_SESSION["memberid"] you'd probably also have $_SESSION["paidmember"], but you get the idea.

You don't really use a SELECT, you just do a multi-table UPDATE where you actually only update the one table.

But of course you could also do something like this:


UPDATE pagehits SET firstvisits = firstvisits + 1
WHERE pageid = $pagenumber
AND ( SELECT COUNT(*) FROM pagesVisited
WHERE pageid = $pagenumber
AND memberid = $_SESSION["memberid"] ) = 0

More than likely, though, what you really want are some stored procedures.

sitNsmile
12-15-2011, 09:59 PM
Hey, thanks for the help, but the actual answer to my question, if locking is a problem. should i go with innoDB?

Also. if UPDATES are being locked because of high performance queries are running (SELECTS), do the UPDATES still enter in once the lock is over, or do I lose those updates?

Old Pedant
12-15-2011, 10:36 PM
?? A SELECT should never block an UPDATE. Surely even MyISAM uses a two-phase commit? If MySQL is so poorly designed that a SELECT can block an UPDATE, then MySQL is the wrong product to be using. (Heck, even the mini-database engine that I created back in 1994-1995 used two-phase commit. Reads could never affect writes. And that was a throwaway DB, almost.)

sitNsmile
12-15-2011, 11:18 PM
?? A SELECT should never block an UPDATE. Surely even MyISAM uses a two-phase commit? If MySQL is so poorly designed that a SELECT can block an UPDATE, then MySQL is the wrong product to be using. (Heck, even the mini-database engine that I created back in 1994-1995 used two-phase commit. Reads could never affect writes. And that was a throwaway DB, almost.)


example: my complex query.

SELECT SUM(is_packet_1.packet_imps) AS imp, SUM(is_packet_1.packet_leads) AS leads FROM is_campaign_pubs , is_packet_1, is_packet_2 WHERE is_packet_1.packet_id = is_packet_2.packet1_id ..etc

Tons of updates that are being locked when that above query runs are like

UPDATE is_packet_1 SET packet_imps=packet_imps+1 WHERE pcid = 277 AND mcid = 13397.. etc


I have bots (crons) that run on the SELECT (sum) queries to update certain optimization tasks, but the users on the website create the +1 statements, which are being locked because the bots run so many heavy queries I wish I could tone down some how.

Old Pedant
12-15-2011, 11:33 PM
Huh...so MySQL isn't as smart as I thought? That's sad. Dunno what to say.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum