Home / server / Questions / 52529
In Process
Christian
Asked: 2009-08-11 18:23:01 +0800 CST

Tuning a remote MySQL server?

  • 772

I am having to tune a client's MySQL server on a Windows machine but I only have access to the MySQL cli and some MySQLAdmin cli functions. I can't see how I can mysql-bench either because it needs to run on the same server as MySQL.

Is there a way I can benchmark or tune MySQL without having access to the OS? The Sysadmin who manages the server has told me just to send him any config changes and he will apply them but that will take forever going back and forth and I don't even know where the changes need to be made as I can't see anything on the system.

Anybody else had an issue like this?

Thanks.

mysql remote

4 Answers

  1. What sort of tuning are we talking about here?

    Perhaps the best idea would be for both of you to read a book such as "High Performance MySQL" and TOGETHER plan the best strategy.

    Doing it remotely is definitely not the most efficient way of doing things but its not totally impossible if you have decent cooperation on the other end.

    I think the real key here is going to be for both of you to be on the "same team".

    A "slow to respond" sysadmin is also not the most fun to work with. Perhaps you guys can schedule some phone time and go over a general battle plan... then fine tune it from there?

    Sorry to sound vague and general but that's the best I can do with the details of your question.

    Hope this helps.

    • 1

  2. What are you tuning for? There are so many factors and variables that you need to be very clear about this point. You could save a lot of time and frustration by setting up another installation of MySQL locally and do the bulk of your tuning on that before passing the new parameters to the server admin.

    • 1

  3. I'm not sure how far into this you've gone, so please forgive me if this answer is too general.

    There are several tools out there like supersmack and mybench that should run remotely.

    In the meantime, have you done the leg work of determining what's already present on the server? I'm always surprised how far I can get by:

    1. Double checking the server's specs (ram, cpus/cores, disk type and benchmark). Your admin should be willing to give you that.
    2. Ensuring that I've picked the appropriate table engines and indexes. Usually innodb for transactions/proceedures, myisam for most else. Indexes should cover your most frequent query plans and have a good mix cardinality to pare your sets down quickly.
    3. From there, you can tune your sql variable set. Start with show variables and show status, make sure your values match up against ram and prevalent engine types (no point in big innodb buffers if everything is myisam for instance). There are lots of little performance gains to be had depending on your specific data needs (trx_commit level, etc).
    4. Gen up a script or two to run against the remote server- perl is your best friend here. You'll find plenty help on the net, but start at CPAN's DBD::mysql page. You'll be pleasantly surprised at the load you can generate with a couple very simple scripts.
    5. Check your stats after a run or two- see how your query cache is doing, etc. The free mysqladmin tools have some basic monitoring functions you can watch during your tests as well.
    • 1

  4. Sounds entirely impossible; tell them to get a production-spec performance test machine and deploy the application in the same way on there. Get a load simulator hitting it with simulated production traffic while it's loaded with simulated production data (or a copy if feasible)

    Don't do performance tuning on production boxes until you've tested it- you could easily make things worse, or even introuction functional bugs.

    • 1