Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

MySQL Primer for the SQL Sever DBA Expand / Collapse
Author
Message
Posted Friday, August 8, 2008 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 11:04 AM
Points: 3, Visits: 8
bnordberg (8/8/2008)
Is anyone else having issues with MySQL's documentation and addition/removal of features? Things like database renaming (added then removed), local file imports having to use the option -L (was optional, now required) ... It seems like each release, no matter how minor, means I have to re-learn everything. Scripts I save in 1 version fail when run against the next minor release. I realize things like the database renaming were better off removed as they did not work at all. But how about fixing instead of just removing!
Also I would have to agree with performance issues, I have tables with 144 million rows, in MyISAM and the performance is worse than that of an MS Access DB!
Overall MySQL is not my DB of choice - SQL Server, Oracle and Sybase are better in every aspect. But MySQL is free, so I guess its worth all my time re-writing scripts!?



Are you using a SAN?
How about indexes?

MyIsam is not best for PK lookup.
Post #549533
Posted Friday, August 8, 2008 1:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 27, 2012 2:56 PM
Points: 124, Visits: 73
All our installs have been all 64 bit mostly using 16 GB of memory, with some using 8. The ease and sturdiness of replication has been a significant benefit for redundancy as well as splitting applications up between read and write. I hear in 6.0 they'll be leveraging replication to provide a synchronous mirror type env. One of the nice features of SQL Servers replication model is the fact that the transactions are stored in the distribution database, and I've been looking at a way to leverage the MSrepl_commands table so that we can replicate out to mysql.

ken kaufman
Lead DBA, Zillow.com
Post #549551
Posted Friday, August 8, 2008 1:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 11:04 AM
Points: 3, Visits: 8
All our installs have been all 64 bit mostly using 16 GB of memory, with some using 8. The ease and sturdiness of replication has been a significant benefit for redundancy as well as splitting applications up between read and write. I hear in 6.0 they'll be leveraging replication to provide a synchronous mirror type env. One of the nice features of SQL Servers replication model is the fact that the transactions are stored in the distribution database, and I've been looking at a way to leverage the MSrepl_commands table so that we can replicate out to mysql.


it should work fine then. Are you using a SAN?
Have you tried switching few tables to innoDB and run some query tests?

I think one important missing feature in MySQL is a Query is not able to use multiple indexes for 1 table. Always uses 1 index per query per table.

And the explain command is limited. MS SQL Query plan and Statistic io is a really good way to improve perf.









Post #549559
Posted Friday, August 8, 2008 1:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 27, 2012 2:56 PM
Points: 124, Visits: 73
We haven't used any SAN's we've stayed to single box solutions (HP 165/185/385/585). On the PK I referenced earlier we needed to query individual records on a dataset of 600 million rows in under 20 ms. Due to the particulars of the query it had to come off a b-tree in memory. We tried innodb, but the load times took to long. The index itself was 14 bytes, so we didn’t want to use ndb as we were getting close to our memory limitations on the box. So we created the b-tree on a myisam structure and pinned it using the query cache. In retrospect the memory engine would most likely have been a better choice.


ken kaufman
Lead DBA, Zillow.com
Post #549560
Posted Friday, August 8, 2008 2:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Samuel Clough (8/8/2008)
I'm sorta amazed that this thread hasn't become a religious war...

We're a pretty laid back group here, I think it's the patchouli that Steve pipes through our connections.

:D
Post #549580
Posted Friday, August 8, 2008 2:13 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Thanks for the article, Ken, very informative. It wasn't what I was expecting, I was expecting more of a command-level or operational level rather than storage engine level. Still, good information. I've always wondered what difference the storage engine made, and now I know.

I've tried to get MySQL working on my various platforms but always had problems, it was a couple of generations ago and hopefully they've made substantial improvements to their installers. In the books that I've read I was impressed by how close their syntax is to T-SQL and ANSI, it made me feel that the learning curve from a syntactic standpoint wouldn't be too great. One thing that I thought was really cool was the backup scripting out the table structure in addition to making the data a series of insert statements.

Currently I have it pre-installed on my web sites (on RHE) as a standard package supporting and supported by Fantastico, running a few Small Machines Forum message boards, a chat engine, and now my Wordpress blog. I quite like the power and stability, and the PHP admin interface works well, though it takes a little getting used to.


To add my $0.002 worth to suggestions for future articles, how about an installation walk-through? I know I could find plenty online, but it would be nice to see one from someone that we know, so to speak. I'd also like to see an article illustrating how to do stored procedures in MySQL.

Not that there's any pressure for you to write more articles for us here....
Post #549586
Posted Friday, August 8, 2008 5:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 27, 2012 2:56 PM
Points: 124, Visits: 73
I’m not sure if Steve wants to turn this into a mysql tutorial site. If you’re coming from a windows background the install can be more difficult then working with the database. I’ll post something on my blog and give the url once complete. Until then here’s a quick step through list provided by mysql:
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &


ken kaufman
Lead DBA, Zillow.com
Post #549661
Posted Saturday, August 9, 2008 1:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,308, Visits: 1,378
Great Article....:)


Post #549711
Posted Saturday, August 9, 2008 1:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
Nice article...


Atif Sheikh


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #549715
Posted Monday, August 11, 2008 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2008 12:00 PM
Points: 4, Visits: 13
If you are using Debian linux, you can install MySQL with a simple: sudo apt-cache install mysql-server. This will take care of all the group and user creations, the mysql_install_db script, etc. It will also all a script to init.d which will start the server automatically when the machine restarts, something that was missed out of the instructions above.
The windows installer is almost as easy. Download the installer and run and all you need to do is answer basic questions about the installation.

To fine tune MySQL you need to play with a file called my.cnf which under linux lives in /etc/my.cnf and using the windows installer end up in the MySQL Server [version] directory.

my.cnf allows you to set parameters concerning how much memory is allocated for the query cache, for joins, which port to accept connections on (default is 3306), where the data lives, how many letters to include in a full-text index, etc, etc. You can even use two versions of my.cnf to run two instances of MySQL on the same box using different ports and set the path to my.cnf when you install the db, ie, when you run mysql_install_db.

The MySQL manual is an excellent source of information for all of this http://dev.mysql.com/doc/ as it includes the official documentation from the MySQL developers and also lots of helpful comments from users to avoid gotchas.

Post #550062
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse