3,457 reputation
1720
bio website karwin.com
location California
age 47
visits member for 3 years, 3 months
seen 8 hours ago

I'm Senior Knowledge Manager at Percona, a services company specializing in the MySQL database.

I've been a software engineer since 1987, and my specialty is as an SQL maven. I also have experience programming in Java, PHP, Perl, C, JavaScript, and I have many other coding skills.

I've written a book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming from Pragmatic Bookshelf, based on the most common SQL problems I've answered on Stack Overflow and other forums, mailing lists, and newsgroups over the past 15 years.


10h
comment Increase performance WITHOUT adding indexes
@RolandoMySQLDBA, have you gotten improvement from putting tmpdir on a tmpfs? I have tried it and found little to no improvement. The overhead of creating temp tables seems to be the bottleneck. The savings by using tmpfs isn't enough to fix performance issues. I suspect temp tables on disk use the filesystem cache and end up living in system memory anyway.
10h
comment Increase performance WITHOUT adding indexes
@TobiasBaumeister, tmp tables on "disk" (even if it's a RAM disk or tmpfs) uses the MyISAM storage engine by default. You can also put a limit on the size of the RAM disk. Whereas tmp tables in memory use the MEMORY (aka HEAP) storage engine, and can use up to tmp_table_size memory per thread. Also, MEMORY storage engine does not support TEXT/BLOB columns.
Oct
29
comment How can i make dump of big mysql table?
"Connection timed out" or "No route to host" when you try to connect usually indicates that you cannot reach the server from the host you're running the mysqldump client on. Sounds like you have no access to the server at all, possibly because of a firewall. You should talk to the network administrator to find out if you can get a VPN connection or access to another client host on the same network.
Oct
10
comment Percona MySQL 5.5 Unique key is duplicated
@PrakashK, here's a link: dev.mysql.com/doc/refman/5.6/en/binary-log.html
Oct
2
comment Upgrading to new cPanel Server with mySQL on SSD — What should I change in my.cnf?
@ceejayoz, we at Percona frequently see scaling problems that are fixed by disabling the query cache. I advise to disable it unless you see a very favorable ratio of QC hits/misses, perhaps 10:1 or more.
Sep
26
comment Using an index for both asc and desc on a string column
@ypercube, yeah, you're right, I think I created the collation naively by just reversing the order of all the bytes. It turns out that designing collations is more complex than can be done in a few minutes in a StackOverflow post! :-) Anyway, this answer at least serves as a demo of how to do it.
Sep
26
comment Using an index for both asc and desc on a string column
@ypercube, see example of CHAR above.
Sep
25
comment Using an index for both asc and desc on a string column
It's a pretty odd case.
Sep
25
comment Using an index for both asc and desc on a string column
@Cratylus, I just copied the block of codes from the latin1_general_ci collation in the same file, and reversed the order.
Sep
25
comment Speed Up Index Creation in MySQL
I guess I assume everyone using MySQL is using a Linux server. Because for the most part, they are. Running a Windows server on a VM seems to me like fighting a boxing match with both hands tied behind your back.
Sep
25
comment Speed Up Index Creation in MySQL
See msdn.microsoft.com/library/dn197896.aspx, it confirms that you only get 300 IOPS per disk in Azure. That's really disappointing.
Sep
25
comment Speed Up Index Creation in MySQL
Okay then go with the tmpfs solution. Based on this and your other question, it sounds like your VM disks are really slow.
Sep
12
comment Amazon: How to change database name in Amazon RDS MySQL?
Aha, I tried it, and that doesn't work if you have triggers. I get an error: ERROR 1435 (HY000): Trigger in wrong schema
Sep
12
comment Amazon: How to change database name in Amazon RDS MySQL?
Why not just RENAME TABLE tablename TO db2.tablename; for each table? Then there shouldn't be any shipping of data back and forth.
Sep
4
comment MySQL does not start - Ubuntu 14.04 LTS after migration to new disk
At this point I'd try using sudo -u mysql -g mysql and see if you can read and write files under the datadir. It could still be permission problems in the ancestor directories, or it could be AppArmor interfering and giving misleading error messages. Try disabling AppArmor temporarily and see if that makes the errors go away.
Sep
4
comment Calculating RAM requirements for an in-memory database
Check the total resident memory used by all processes by summing the RSS column from ps. You can also use free to check RAM in use. Also don't forget to leave some spare RAM for filesystem buffers.
Sep
1
comment How to generate auto increment values for my duplicate column in my sql?
You just cross-posted this on StackOverflow. Please post to one site.
Aug
29
comment MySQL CPU & Memory Spikes
CPU spikes in MySQL are not uncommon, depending on the version. It could be a variety of causes. To be sure, you'd want to use oprofile or something to find out exactly which functions within the mysqld process account for the most execution time during the CPU spikes. My first guess is the query cache, which has been known to spend time resorting itself internally - try setting query_cache_type=0 and query_cache_size=0 and restart. See if that helps.
Aug
27
comment Proposal: MySQL blob handling revision
@IGotRoot, how would your idea support transaction isolation, transaction rollback, redo logging, or crash recovery? You should learn more about what databases do, that makes a database not the same as a filesystem.
Aug
25
comment How to change rows to columns in mysql?
It's hard to debug an SQL query by staring at the code that generates the query. I suggest you SELECT @sql to return the final query before you try to prepare it. The syntax error may be more clear if you look at the full query.