I have old applications that need to read (and write) MyISAM tables that themselves receive lots of bulk updates. Time to try MySQL-Proxy.
MySQL Proxy is light on documentation and very few people written anything about working. Most of what I have read says MySQL-Proxy is not ready for prim time. I have hope so I had to give it a try.
I started with thee VMware servers. I setup one master and two read only slaves. I tested the replication with mysqlslap from another independent server and it worked fine. The slave never ran more then a second behind.
I downloaded mysql-proxy-0.8.0.tar.gz (64x version). I create a mysql-proxy.cnf file to configure the proxy.
[mysql-proxy] basedir = /home/dbaops/mysql-proxy-0.8.0-linux-rhel5-x86-64bit keepalive = false deamon = false proxy-backend-addresses = 192.168.101.91:3306 proxy-read-only-backend-addresses = 192.168.101.92:3306,192.168.101.94:3306 proxy-lua-script = rw-splitting.lua
I ran this with the command; ‘ bin/mysql-proxy –defaults-file=mysql-proxy.cnf ‘.
I started with few commands run by hand. They worked. I could write and read data fine. Then I setup a heartbeat to create continuing updates. This also ran file.
Now for the real test. I ran mysqlslap though the proxy. Note in this command the port is set to 4040. This FAILED, in many bad ways.
mysqlslap --concurrency=25 --iterations=2 --auto-generate-sql --engine=myisam \ --auto-generate-sql-load-type=mixed --number-of-queries=10000 \ --host='db1' --port=4040 --user=root --password='P@ss0rd'
I turned on DEBUG in the rw-splitting.lua script by changing ‘is_debug = false’ to true. This worked grate with hand rolled quires. When I ran mysqlslap, it a segmentation falt-ed.
With DEBUG turned off I received all kinds of data errors. Everything from missing tables, no database selected and database disconnects.
So the stories are true. MySQL Proxy is not ready for prime time, unless you are creating a Three Stooges act.
LinkedIn
Grennan.com
Twitter
Well, first I would like to note that the developers of mysql-proxy still consider it to be alpha code, not to be used in production…
I’m trying to use it for a completely different reason: WAN optimization of badly written client applications.
I’m not a DBA but an application performance analyst, and I’ve been confronted countless times with applications that loop through thousands of ‘INSERT INTO xxx VALUES (a,b,c)’.
This is a WAN nightmare where mysql proxy comes in handy: rewriting the individual inserts into one multi-value insert.
I’ve only ran it in test databases so far, but it has not failed or segfaulted (yet) :-)
Link | July 27th, 2010 at 7:03 am
Like you I was hoping mysql-proxy could solve a problem I’m having with old code. I need to do read-write splitting so I can replicate a system with lots of read traffic. Like you, if it worked I could fix a problem now.
About your thousands of ‘INSERT INTO xxx VALUES (a,b,c)’ transactions. InnoDB tables I hope? MyISAM locks the entire table for an insert. If you change this to a multi-value insert you will be locking the table for a longer time. Yes, thousands of one line inserts may be a WAN problem, stacking up thousands of SELECT operations waiting on the LOCK to clear may also give you issues. The way it is now give breathing room for other transactions. InnoDB tables do row level locking and should not be an issue.
Be very careful if you use mysql-proxy in production. Any update of the code may change how it works and break your use.
You might also look at SQL Relay. The code is more stable. You might have to hack what you need into the code it self. I’m not sure if it has a scripting language.
Link | July 27th, 2010 at 8:37 am
What I’m faced with is this: the client application sits 300ms away from the database, starts a transaction, and loops through a few thousand inserts into an InnoDB table before committing. Each of these inserts take 300ms to register and I believe they also keep the record locked until the commit is sent (I’m not 100% sure about that). By grouping the inserts, I can bring the time between start transaction and commit down by a factor 100 at least… Possibly(?) keeping the records locked for a lot shorter period, but most certainly shortening the total time to do all the inserts.
Link | July 28th, 2010 at 5:14 am
Wow. 300ms is a very long delay. Most of the world is only 150ms away.
QUESTION: Is the database truly transactional? If so read and understand this carefully.
InnoDB does row level locking. If your SQL is only inserting records the rest of the database should not be effected. The index for other changes is not updated until the commit. Other queries don’t know about the new records until then. However, In row-level locking, InnoDB normally uses next-key locking. That means that besides index records, InnoDB can also lock the “gap” preceding an index record to block insertions by other sessions in the gap immediately before the index record. A next-key lock refers to a lock that locks an index record and the gap before it. A gap lock refers to a lock that locks only the gap before some index record.
By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows. Phantom rows area problem related to replication. If you are not replicating this server or database you might want to change this setting.
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
How InnoDB locking is handles is described at http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html and http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html .
As for create a multi-value insert, you are on the right track. This will lower you insert time. You might also check if the client can and is using compression.
Link | July 28th, 2010 at 10:20 am