MySQL Primer for the SQL Sever DBA

  • Apologies: the Debain linux install command should be sudo apt-get install mysql-server not sudo apt-cache as I wrote above.

  • Just in case someone's looking to install.. The best bet is to use the appropriate package manage rather than the manual install described a few posts earlier. For Debian based systems, that would be apt-get as described (this includes Ubuntu). For rpm based systems such as RedHat or Suse, use the appropriate package manager. It will make the install even easier than a Windows install.

  • We've been evaluating MySQL Vs SQL2008. So far the cheapness of MySQL means that a scaleout architecture is a practical proposition. Performance wise scale-up is not so hot. Go beyond 4CPU and see what happens.

    NDB is brilliant for ultra-high concurrency reads. Sun don't recommend going beyond 8 data nodes though in theory you can go up to 48. Bear in mind you lose the performance if you start doing joins between tables so you have to be careful about what you want to do.

    With the religious flame wars you get emotive statements rather than scientific facts so you get statements such as MySQL InnoDB out-performs SQL Server or vice verse.

    Great, which version of each engine, what hardware spec, doing what?

  • Hi David.

    I never really had any problem with the MySQL engines themselves. If I stuck with myISAM I got single table backups and single table restores out of the box. Too cool for school, that.

    My performance problems were all in the connector. Queries that ran, or crawled rather, in the application came back instantly when pasted into SQLYog. It's was a hot server on a clean network and all inside the four walls. The latest and greatest of all components too. I wish that I could share real world statistics with you but that is all tucked away in the project folders under the confidentiality blanket.

    Then is have to look at what, IMHO, is the crippled way they implemented triggers, functions, and stored procedures. I can't wait for MySQL to "grow up" in this area. You can read that both ways. One, I'm hoping they do and anticipating the need for another look, and Two, I'm not waiting but moving forward without them. Sad really.

    It turned out, for us, that the low cost of acquisition was not enough to overcome the issues.

    MySQL is a great thing and will work well for many people. It has feature sets and attributes that can make it "killer" in lots of places.

    ATBCharles Kincaid

  • The thing that impresses me most about MySQL are the MySQL tech staff. These are a bunch of guys I consider myself privileged to have worked with.

    The trainers were also pretty hot. Neither group gives any sales BS. They are totally upfront and honest about what their product will and won't do. That means they inspire confidence.

    Being upfront about the limitations means that we design a solution accordingly.

    We looked at the various engines when we evaluated MySQL.

    Memory engine is fast but writes cause table locks. Also all strings become fixed length. VARCHAR(255) becomes CHAR(255). As a non-expiring cache it is unsurpassed.

    Blackhole engine will run any SQL statement it just doesn't store data. It is useful as a relay station for replication. Replication is Master to Slave and the MySQL guys recommend that the maximum number of slaves is 8. Go beyond this and you replicate to 8 black holes, each of which have 8 slaves.

    Archive engine is really a compressed MyISAM.

    One of the MySQL guys told me that the origins of the pluggable engine architecture came about because the developers didn't like having to wait to recompile MySQL so they split the storage and query engines to shorten recompile times. Serrendipity!

    As for size, it depends on what you are doing. For a read-only store you can get really big databases. One of ours in near 1TB and gives acceptable performance.

    MySQL also works well if you want massive write capability such as a logging application. Where it all gets a bit hectic is if you want massive reads AND writes.

    Backups are a pain. We really need a Litespeed equivalent for compressed backups.

    Although I have seen religious flame posts on DB engines there seems to be less of it than with, say, OS flame wars. I'm probably wrong but I feel that DBAs are interested in databases and database technology per se rather than any particular heresy.

  • There's just that one little new piece to add to the puzzle - Oracle taking over MySQL. That changes everything. In a bad way. The thing most MSSQL database people will notice when they first get into MySQL is how similar the two products are in terms of their functions, data types, etc. Even in .NET, you can take many database object instances, like SqlDataAdapter for example, and just make it MySqlDataAdapter, and it works. I think that is going to change, and we will see much more PL/SQL-like syntax. THAT is the worst part. Personally, I manage DB2, MySQL, and MSSQL, and there is no doubt in my mind that MS has the best product of the three.

  • We've used MySQL for some intense projects in the past. The problems that we encountered tended not to be with MySQL itself. I had queries that choked in the application but would run fine in SQLyog. It was the .NET connectors. I one failed project (yes, I've had them too) while the database performance was an issue it was not the killer.

    I also have been fearful of the takeover. At first I thought about the the "buy it to kill it" thing. More likely it will be the "let's merge all the teams so that we can look like we are saving money" thing. It's a slow poison. All the stuff that people hate about A (and the reason they didn't but it) will bleed into B. When B dies a slow death then the hymn sung over the grave would sound like "We were right with the first product all along." We have all seen this before.

    No as to one entity owning two some what competing products. I don't see that much of an issue. Case in point is Microsoft owning Apple. In the car wars Buick competed with Oldsmobile. Mercury competes with Ford.

    ATBCharles Kincaid

  • I have MySQL running on a 4X (dual core) 16 GB machine. I was using INNODB, but it kept running into locking errors. I had increased the innodb_buffer_pool_size, and that made some difference, but it was still taking forever. I was inserting millions of records into the database from a different database on the same server. So I moved back to MyISAM and removed all my INNODB changes from my.cnf. This however did not solve the problem either.

    For some reason I had to put the changes back to the innodb_buffer_pool_size even though I was using MyISAM. I don't understand this but it did make a difference.

    Even still it is still a very slow import process.

    We finally changed the architecture, so we import to a staging SQL Server, then use SSIS to make the needed modifications/merges and then write to MySQL.

    I would have to say its not been even close to a cost savings when we factor in troubleshooting time and no ETL tool (so we still have to use SSIS).

  • Mysql does get into trouble with larger datasets, and there is a lot of hacking you need to do. If your talking about single instances of Mysql in a corp env then sql server is most likely a better bet. However if your talking about a cloud env of 10 or more DB servers being accessed by over the internet the per proc license for SQL Server will usually be more expensive then any Dev time required to get mysql to work.

  • I'm interested to know what you mean by large datasets and in what contexts. I have run some moderately large spatial tables in MySQL -- the biggest being several billion rows and around 450 GB of disk space -- and not really had any issues, either on Windows or linux boxes. However, these are MyISAM tables and are largely read only, other than some large updates every few weeks, which I do on a second instance.

  • I've seen issues building indexes on large tables taking excessive amounts of time on anything over 50 GB keys. As I understand it this is do to the sorting process. In fact I've actually asked mysql at performance class on this issue and the answer is to build the keys and tables offline then replace the underlying files. I'd be curious how large your indexes are, and what your mem config is.

  • One of the MYD files is 100 gb and another is 40 gb. For both these tables, the indexes were defined before any data was put in. The tables were then populated using load data infile and a trigger to create the geometry object. As such, I can't really tell how long the index creation took, as it was all part of the overall load process, which did take 3 days on one of the tables. It is likely that the index creation would have taken a very long time, had I done it after data load, as MyISAM tables are completely rewritten in the process.

    Now that these tables and indexes are set up, I can do several thousand inserts a second, of fairly complex polygonal data, which I consider acceptable.

    My config file for sort buffer size, etc, is more or less the same as the sample huge.ini that comes with MySQL, as the box in question, is as 64-bit beast that only runs MySQL.

Viewing 12 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply