Re: innodb rollback 30x slower than commit normal?
nik...@doppelganger.com (Nikita Tovstoles) writes: We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED. We use Hibernate and optimistic concurrency, so periodically concurrent write attempts cause app-level Exceptions that trigger rollbacks (and then we retry tx). We've added app-level caching and turned down our tomcat NIO thread count to just 8 (very little contention inside the app) but now we're seeing that rollbacks appear to be up to 30x slower than commits?! Is that normal? Here's a typical TX: Set autocommit=0; Select * from users where name=bob; Update users set visit_count=X where id=bobId and version=Y Commit; Set autocommit=1; When this tx is executed about 100 times/sec, appserver latency is about 10-15 ms per http request (including db time). However, when instead of commit a 'rollback' is issued, the latency spikes to 600-1100 ms (nearly all of that time in appserver appears to be spent waiting on db). So is that expected cost of a rollback? InnoDB is heavily optimised and assumes that a transaction will commit successfully. As such it's not optimised to do the rollback, and as such a rollback *IS* very expensive. I've seen similar behaviour on some servers I use at work so what you are seeing is I think normal. Can anything be done to speed it up? I'm not aware of anything so I think you have to accept it and make sure that where possible you try to avoid situations where you need to rollback. That's not always possible of course but sometimes the scope of the transaction can be narrowed and that should help a bit. However in your example you could easily do a single atomic update involving the SELECT and UPDATE. That would be much easier as you would either run the combined UPDATE or not. Perhaps that would work for you? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with mysql query, multiple list
abhishek@gmail.com (Abhishek Pratap) writes: I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. You said in a later post that you have thousands of events. If they are already in a table then use that, otherwise put the events into a temporary table and join the 2 tables together. Something like this simple example: mysql select * from events; +-+ | event_ts| +-+ | 2009-05-09 10:29:00 | +-+ 1 row in set (0.00 sec) mysql select * from table_name; ++-+-+ | id | start_ts| end_ts | ++-+-+ | 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 | | 2 | 2009-05-10 10:00:00 | 2009-05-10 11:00:00 | ++-+-+ 2 rows in set (0.00 sec) mysql select t.* from table_name t, events WHERE event_ts = start_ts and event_ts = end_ts; ++-+-+ | id | start_ts| end_ts | ++-+-+ | 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 | ++-+-+ 1 row in set (0.00 sec) Hope this helps. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
splitting large tables vertically
I was wondering about a scale out problem. Lets say you have a large table with 3 cols and 500+ million rows. Would there be much benefit in splitting the columns into different tables based on INT type primary keys across the tables? The split tables will be hosted on a same physical instance but can be spread over multiple disks. We're also open to splitting the query and reconstituting the data at the application layer such as select col1, col2 from t1 where col2='name'; select col2 from t2 where col1=t1.col1; select col2 from t3 where col1=t1.col1; as opposed to select t1.col2, t2.col2, t3.col2 from t1 inner join t2 on t1.col1=t2.col1 inner join t3 on t1.col1=t3.col1; My concern to this approach is the overhead of joins of such large number of rows. I was doing some research into the cost of joins and as of 5.0, the joins were still nested loop scans. I was wondering if there are others with practical experience in this matter and what they've found. Any feedback will be much appreciated. Kyong Inst. Web Programmer CMDBA 5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Still going in cicrles
I set up MySQL and when I try to start it it fails telling me that I need to run 'mysql_upgrade'. When I run 'mysql_upgrade' it runs 'mysqlcheck' which is supposed to only be run when the server works ...
Re: Problems After MySql 5.1.34 - THANKS
Thanks to everyone that offered all the valuable suggestions. SImply replaced the shared-community with shared-compat and all seesm fine. Will live with, or deal with, the missmatch in php libraries later, but for now everything seems to work. Thanks again to everyone that offered such detailed and helpful suggestions :) Gary Smith wrote: You are right. I misspoke regarding mysql - php - apache hell. It happens anytime an interface changes. From: Mark [ad...@asarian-host.net] Sent: Thursday, May 07, 2009 5:57 AM To: mysql@lists.mysql.com Subject: RE: Problems After MySql 5.1.34 Gary wrote: Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Well, you don't actually have to recompile PHP entirely, of course: just its mysql.so extension. @TS: Other than that, you basically need to recompile *everything* (or its mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw; so you'd need to build DBD:mysql as well (same for Python, etc). Walter wrote: Any (major)upgrade of mysql client requires the dependent subsystem to upgrade also. Anything else would be careless since you do not know if the interface has changed. Actually, you *do* know: that's what the changelog is for. :) When C header changes are made, an upgrade is in order. If not, when upgrading between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you won't need to recompile all system-wide MySQL client extensions. I've done this many times, without issue: you just need to be absolutely sure no header changes were made (when in doubt, recompile). I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's working just fine, but I spent several hours recompiling MySL client stuff; without doing so, your apps will likely behave erratically, or just segfault altogether. This isn't a MySL hell exclusively, btw. You'll get the same issue upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the beast. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@openenterprise.ca -- _ Johnny Stork Open Enterprise Solutions Empowering Business With Open Solutions http://www.openenterprise.ca Mountain Hosting Secure Hosting Solutions for Business http://www.mountainhosting.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need MySQL Logo for public use.
Since MySQL is one of our tools, we would like to have a logo that we can use at our website. And we will use it to link back to the MySQL site. Can someone point me to MySQL logos that I can legitimately use? Thanks Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: splitting large tables vertically
Do the 3 tables have different column structures? Or do they all have the same table structure? For example, is Table1 storing only data for year 1990 and table 2 storing data for 1991 etc? If so you could use a merge table. (Or do you need transactions, in which case you will need to use InnoDb and merge tables are not possible) Is there always a 1:1 relationship between the tables? If so, I'd recommend storing all the data in 1 table. Joins are really expensive and are to be avoided if at all possible possible. One way I found to make joins faster when retrieving under 5k rows, is to select the rows from each table and put them into a temporary Memory table, build the primary index on each memory table, and then use the memory tables for the join. This seems to be more work, but it is about twice as fast as joining the physical tables together. Hope this helps. If you could provide us with more information as how you will be adding and retrieving the data (how many rows are being retrieved in a query?), perhaps we can narrow down the solution for you. Mike At 07:44 AM 5/9/2009, Kyong Kim wrote: I was wondering about a scale out problem. Lets say you have a large table with 3 cols and 500+ million rows. Would there be much benefit in splitting the columns into different tables based on INT type primary keys across the tables? The split tables will be hosted on a same physical instance but can be spread over multiple disks. We're also open to splitting the query and reconstituting the data at the application layer such as select col1, col2 from t1 where col2='name'; select col2 from t2 where col1=t1.col1; select col2 from t3 where col1=t1.col1; as opposed to select t1.col2, t2.col2, t3.col2 from t1 inner join t2 on t1.col1=t2.col1 inner join t3 on t1.col1=t3.col1; My concern to this approach is the overhead of joins of such large number of rows. I was doing some research into the cost of joins and as of 5.0, the joins were still nested loop scans. I was wondering if there are others with practical experience in this matter and what they've found. Any feedback will be much appreciated. Kyong Inst. Web Programmer CMDBA 5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
