#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    10
    Rep Power
    0

    Scalability and security


    I'm was considering developing some applications using MySQL but found a couple of things (well more than a couple but I'll only discuss a couple in this thread) I really didn't like.

    One of the concerns I have with using MySQL is it's ability to scale. I would never use the MyISAM table type because I think it's insane to not be able to maintain transactional consistency and recoverability on a production application so, I will only be using InnoDb. I know people run some very large databases on MySQL but it seems odd to me that you can't place tables, indexes, and transaction logs on separate disks. Maybe there is that capability and I'm just missing it. Please enlighten me as to why this is not a concern for those of you that develop high transaction applications.

    The second thing that concerns me is the lack of stored procedures. It's mainly a security issue in that you have to execute all of your SQL as strings. This of course lends itself to SQL injection attacks. Stored procedures avoid this because the "queries" are parameterized and adding SQL to any parameter would usually cause the procedure return nothing. In addition to this is that you have to give the user direct access to the base tables (very bad). So, do people that are writing apps for MySQL write a lot of extra (front end) code to try and capture injection attempts?

    The last thing that is disturbing to me is the lack of an online backup capability. How does anyone that runs a mission critical 24x7 operation sleep at night without this capability?


    I'm sure these have been discussed a lot on this and other forums but my searches didn't really turn up the type of discussion these issues that I would like.

    Thanks for your (intelligent) responses.


    --Buddy
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2002
    Location
    Binghamton, NY
    Posts
    536
    Rep Power
    29
    Here are the answers that correspond to your questions, in the same order.

    1. There are a few ways to handle this.
    First option is RAID. RAID handles splitting disk reading and writing for you. That way you don't really need to worry about the database doing it.
    Second, you can move the transactional logs to their own disk with the innodb_log_group_home_dir. You can also create the table space in multiple files across multiple disks. You can't currently control where the data or indexes go inside of there, but multiple table space support is part of MySQL 4.1 (4.1.1 and above). Depending on the amount of data you have, normally a large portion of this is cached in memory, so it normally isn't too important to have to split those.

    2. There are many parts to this question and answer as SQL injection attacks aren't a simple thing for any database.
    First off, many APIs, such as JDBC, Perl DBI, etc..., give you the ability to use prepared statements, which help to prevent injection attacks. This is definately the best way to go. Some API's do not have prepared statements, in which case they give you a fuction which prevents them, such as mysql_real_escape_string(). Other APIs will automatically escape them, like PHP does with its magic_quotes_gpc option.
    Second, MySQL has a very fine tunable privilege system where you can give individual rights all of the way down to the column level. This again helps allow you to prevent people from doing anything bad as well.
    Third, stored procedures are coming in MySQL 5.0, so if you are unhappy with the above you will be able to do it the way you are used to.

    3. You can do hot online backup with InnoDB. There are two options available.
    First is a pay-for program called InnoDB Hot Backup This will take an online binary backup, but again you need to buy it.
    The second option is a free method of taking a backup and that is just to use mysqldump. There is the --single-transaction option for mysqldump which causes the InnoDB backup to be a snapshot in time (repeatable read isolation level). InnoDB doesn't lock anything for reading, so it works out fine.

    Keep in mind that while both methods do not set any locks, they do generally cause a lot of disk reading and writing. This is one place where replication can be very useful. You can just create a slave machine which you use for backups only if the performance is possibly an issue.

    One last note, is that with the binary update logs it is possible to get point in time recovery with MySQL, ie. not lose any data at all from committed transactions even if the harddrive with the data crashes.

    Hopefully this helps answer these questions. Feel free to ask the others now as well ;-)
    Last edited by HarrisonF; November 7th, 2003 at 09:21 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    10
    Rep Power
    0

    Why bother with MySQL when Postgre has all of this already


    RAID is a good option but there can still be a bottle neck with a single controller. You can create two RAID arrays on tw controllers but then we're back to the original problem. Since 4.1.x is still listed in Alpha I'll not consider any of those feature as viable.

    I don't want to code anymore than I have to. Lack of stored procedures is a real killer for me. Since 5.0 is not even listed in Alpha (I guess it's in dreamland) I'll give it less consideration that 4.1.x.

    I saw InnoDB's online backup tool and even though it's not free it looks like a a good solution. Replication would be great if it worked on InnoDb tables.

    I guess I'm wondering why you would choose to use MySQL over Postgre. Both are free and Postgre has much more "standard" functionality.


    --Buddy
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2002
    Location
    Binghamton, NY
    Posts
    536
    Rep Power
    29
    I give you a nice response and then you try to start a religious war. I am going to ignore comparisons against PostgreSQL since they always end up silly.

    Yes stored procedures can be nice, but they are still coding. So laziness isn't a good reason for stored procedures. There are many good reasons for them, which is why they are currently being implemented (they are about 75% done). You can clone the source and compile them yourself if you don't believe me. There can also be a lot of drawbacks to stored procedures which I won't get into here unless you really want me to. Anything in databases have both pros and cons.

    To answer your semi-questions, replication does work with InnoDB. Why did you think it didn't?

    Yes, the online backup tool works great if you need a binary backup, but you can just use mysqldump if you don't want to spend money on it.

    You can split InnoDB table spaces on to seperate disks (or RAID arrays) currently. You currently can't control where it puts things exactly though. Of course, you can effectively control it in other ways, such as creating a 4 gig file on each raid array, and then if you load in 8 gigs of data, you know they are going to be split across both of them.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    10
    Rep Power
    0
    Well, I wasn't trying to start a religious war. I was just interested in the fact that since both are free why use one that is missing several ANSI SQL features that the other has? Since I've used neither I'd like to know why someone would choose one over the other (at least I think it's a valid question). I didn't realize I'd be hurting your feelings.

    I'd like to hear why you think that stored procedures have draw backs. I've been developing SQL Server applications for nine years and have never found any drawbacks to stored procedures.

    As for InnoDB replication, I thought I read in the documentation that the replication only worked with MyISAM tables. I was reviewing several features and may have confused some of the capabilities.

    So laziness isn't a good reason for stored procedures.
    I didn't know avoiding writing unnecessary code was laziness.
  10. #6
  11. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    542
    So, if you are free to choose why consider MySQL? Just for it's popularity? Go for PostgreSQL or other opensource DB.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    10
    Rep Power
    0
    I'm just trying to find out if someone has evaluated both and chose MySQL. Then I'd like to find out what where the key factors in their decision. If MySQL has something in it that makes it worth giving up other features (that I may find important) in PostgreSQL (or FireBird for that matter) I'd like to hear about it. That's all.
  14. #8
  15. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    542
    Personally I've used MySQL a lot, and still keep an eye on it, but I can't miss views, stored procedures and some more advanced features available in Oracle or SQLServer.
    So, unless severe budget restrictions prevent me from using the two above I would go for PostgreSQL.
    Regards

IMN logo majestic logo threadwatch logo seochat tools logo