MySQL Primer for the SQL Sever DBA

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ken Kaufman » MySQL Primer for the SQL Sever DBA

42 posts, Page 2 of 5

««1 2 3 4 5 »»»

MySQL Primer for the SQL Sever DBA

Rate Topic

Display Mode

Topic Options

Author

Message

scott.w.white

Posted Friday, August 08, 2008 7:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 10:24 AM
Points: 9, Visits: 44

In present times when companies are looking to add projects and cut costs open source is a very viable option. In my experience more than 75% of the SQL Server projects would work well on MySQL. At an internet company I worked at we used MySQL for over 300GB of data. There are great analysis tools for it and lots of community driven support.

Where I do see a vacuum in the open source world as it relates to databases is ETL & multidimensional databases. There are some recent projects in this area of BI, but they are not close to as mature as the commercial offerings from companies like Business Objects or Oracle.

I would also recommend sticking with InnoDB for enterprise projects because of transactional support, performance and supportability of large databases.

Post #549174

john-839610

Posted Friday, August 08, 2008 8:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 12:00 PM
Points: 4, Visits: 13

bnordberg I think you must have some serious config issues if you are finding a myisam table's performance to be worse than access.
Some of these posts really get to the crux of the issue in any MSSQL vs MYSQL debate. With SQL server pretty much what you see is what you get. MySQL is infinitely configurable, which can be difficult at first, but is incredibly rewarding once you get past the first few gotchas. Hell, you can even add your own functions and recompile if you so wish.

In fact, MySQL is the first thing I ever built from source, a painful experience to begin with. Now, I am an ecstatic linux hacker and would never go back to black boxes and closed source. It all comes down to budget levels, comfort zones and what you need it for. As I said above, I used MySQL initially as it had a feature that SQL server didnt' have until 2008, namely spatial extensions and R-tree indexes.

Post #549198

SQL-DBA

Posted Friday, August 08, 2008 8:33 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 2:37 PM
Points: 73, Visits: 273

I would really love to see this same type of article written for PostgreSQL vs. MSSQL.

Post #549248

timothyawiseman

Posted Friday, August 08, 2008 8:43 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

This was a very informative article. I would join the call to expand it into a series going into more detail on the differences and when it is appropriate to use MySQL along side MsSQL.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Post #549261

Jack Corbett

Posted Friday, August 08, 2008 8:51 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Just to pile on, I agree it was an interesting article and cleared some things up for me. It has been a few years since I have looked a MySQL mainly because I have worked in MS shops and I am more concerned with being a credible expert in MSSQL than learning open source. My only comment is that from a tools perspective, MSSQL beats out MySQL or at least did when I last looked at it.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #549276

WhoMovedMyCheese

Posted Friday, August 08, 2008 10:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 04, 2011 8:26 AM
Points: 9, Visits: 113

Nice intro. My company is evaluating MySQL with a variety of expectations depending on with whom you speak. We are primarly MS SQL and Oracle off-host ( non-DB2 ). The core DB group has little experience in the Open Source arena but are looking to remedy that shortly. While I agree MySQL definitely has a place (cost, especially when looking at Oracle...yeeesh) and fits in, I'm a stodgy "old-timer" trying to redeem myself from my propreitary db ways. My initial research raises concerns, particularly the supportability of MySQL in large enterprise environments regarding:

- Environments with high concurrency demands, not just reads but high transactional volume. I'm under the impression this could be ugly.
- Lack of good backup technology. Having to lock all my tables to get a file level backup reduces availability somewhat unless you get fancy with your volume managers.
- Enterprise level security. Sometimes regulatory requirements impose requirements in this regard. For example, audit capabilities (DCL, DML, DDL, Logins etc ), account configuration ( password complexity, ability to lock accounts after x bad logins or at all! etc ). These features seem lacking in MySQL that were at least somewhat addressed in MS SQL 2005.
- Operational support/performance tuning aids. Oracle has the wait interface, tracing, ASH etc. MS SQL has profile among other features. What does MYSQL have....crickets. If I have a poor performing process what would I use? Instance wide stats are not going to cut it at that level. However, perhaps I've not gotten to that level in my researching yet....
- Vendor support...okay, this is the bigot in me. I know this can be purchased from Novell, Sun...etc. It would be interesting to know how this check box has been addressed by others. When a system is down...its nice to call somebody and get a quick answer rather than some forum with sometime dubious answers.

I agree I'd love to hear more about your experiences with these topics. Explaining additional comments like Innodb being a scaled down version of MySQL. Do you mean performance or in terms of robustness of functionality? etc.

Thanks

Post #549367

kenk-601293

Posted Friday, August 08, 2008 10:26 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:57 PM
Points: 14, Visits: 55

As a reply to the large datasets, myisam grew out the data warehouse world and understand it can handle large datasets. However the comment is based on the experience of building a 12 GB primary key on a 20 GB dataset. The process took over 36 hours to complete on a HP 585 with 16 GB of memory.

Post #549400

Samuel Clough

Posted Friday, August 08, 2008 11:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:03 AM
Points: 27, Visits: 108

I'm sorta amazed that this thread hasn't become a religious war...

You can use both innodb and MyIsam for very large datasets. In fact, a lot of the most popular web sites use MySQL. The best thing is to contact MySQL and talk with someone very familiar with what you are trying to do and then see how it compares.

There are third party backup solutions for MySQL. There's also mysqldump which is nice to dump a small database (schema and data). Sorta like Oracle's datapump but text based.

I'm sure MySQL could point you in the right directory for query tuning, but one nice feature was the slow running query log. You could activate it and MySQL would write any query that ran long to a text log file. You could then begin optimizing with the queries causing the most trouble. Quite helpful if you ask me.

Others have mentioned PostgreSQL. Postgres is another nice database. To me, Postgres tends to track Oracle and MySQL is more like a MSSQL (yes I know there are missing features), but it's always dangerous to make those comparisons :^).

Post #549437

SQLKen

Posted Friday, August 08, 2008 11:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 27, 2012 2:56 PM
Points: 124, Visits: 73

The long running query log falls a bit short in an oltp environment, because it can only be set at the second level. For example if you want to track queries running over 100 ms, you can't use this feature out of the box. I say out of the box, because I know there is a tweak to the source code you can compile that does provide this functionality.

ken kaufman
Lead DBA, Zillow.com

Post #549459

franck.leveneur

Posted Friday, August 08, 2008 12:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 11:04 AM
Points: 3, Visits: 8

Thanks for the interesting article.

NDB
"This is a true cluster". Yes you're right. And I think the cluster probably starts scaling even more when you have 4, 6, 8 , or 16 nodes. Something you can't do on MS SQL.
SQL Server Cluster is just a fail over solution, (or hot stand by), where SQL resources are switched to another physical box. But SQL resources are not shared among the 2 physical boxes.

Mysql also offers a fail over solution (DRDB i think). We're using Veritas cluster on Unix.
We also have a 64 bit, 16 CPU, 32 GB RAM and a SAN. CPU is sleeping all day long, yawning.

MyISAM.
Yes, it's fast, for FullText search. Not meant for PK look up. (innoDB is faster for this).

Replication
I would also have written something about replication.
MS SQL replication is just pure pain. Mysql is so easier to set up and offer lots of features like
1 master, 1,2, 10 slaves, 1 master 1 slave which replicates to other slaves. Another nice things is you can have innoDB on master (for the writes) then MyIsam on the slave (for the reads).

The Web is 80-90% read in general. We're also converting our TextML server to myIsam until we find better solution on Unix.

I would have loved to see your hardware configuration.
Did you use any 64 bit?

Anyway, great article. I think Mysql is really getting some traction in big corpo.

Franck L.
Sr SQL DB (10+ years) and MySQL DBA (3 years)

Post #549532

« Prev Topic | Next Topic »

42 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions