Results 1 to 13 of 13
  1. #1

    how can you measure the speed of your MYSQL server??

    Hello.

    I am suffering ..... suffering of my site's very slow pages........

    please help me with this question:

    what is the standard?

    How can you measure the speed of your mysql server?


    is there a certain script that you can execute on a mysql server to give you an idea of how busy your mysql server is ???


    I mean before blaming my host for having put too many customers on my box, I really want to be sure.....
    WHT Addict
    Ask on WHT before you sign up with a no-value-host.

  2. #2
    does the following information about my server mean that http and mysql are on the same machine ???

    **************
    Server Status
    **************

    apache (1.3.31 (Unix)) up
    bind (9.2.1) up
    cppop up
    cpsrvd up
    exim (exim-4.34-60_cpanel_stmpcontrol_antivirus_rewrite_mailman2_mailtrap_exiscan) up
    exim-26 up
    eximstats up
    ftpd up
    imap up
    mysql (4.0.20-standard) up
    syslogd up
    Server Load 0.65 (4 cpus)
    Memory Used 57.7 %
    Swap 0.850 %
    Disk hda1 (/) 24 %
    Disk hda7 (/home) 18 %
    Disk hda6 (/tmp) 3 %
    Disk hda2 (/usr) 28 %
    Disk hda5 (/var) 33 %
    Disk hdb1 (/backup) 29 %
    WHT Addict
    Ask on WHT before you sign up with a no-value-host.

  3. #3
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,520
    If you're suffering massive lag on a server that runs Apache and mySQL, then I suggest you move mySQL to another server. With both running at the same time, way too much has to be processed by the CPU. Moving them to two separate servers will significantly reduce the load and thus reduce (destroy) the lag you are sufferring.

    To view the server status, type "top" and then "P" to sort by CPU usage. It'll go from highest to lowest usage.
    Jakiao

  4. #4
    Join Date
    Dec 2001
    Location
    NYC
    Posts
    1,902
    What's your site? You can sometimes figure out how many sites are hosted by checking the shared IP. If it's in the hundreds, then you'll feel the slowness.

  5. #5
    thank you Graham for the great help

    here is what I could copy from the "top P" command WHILE I was fetching one of my very slow (mysql avid) pages.

    Please advise....

    19:06:05 up 5 days, 12:48, 3 users, load average: 0.61, 0.34, 0.29
    180 processes: 177 sleeping, 1 running, 2 zombie, 0 stopped
    CPU0 states: 0.5% user 5.1% system 0.0% nice 0.0% iowait 93.3% idle
    CPU1 states: 4.3% user 0.3% system 0.0% nice 0.0% iowait 94.3% idle
    CPU2 states: 7.3% user 3.2% system 0.0% nice 0.0% iowait 88.3% idle
    CPU3 states: 7.2% user 5.2% system 0.0% nice 0.0% iowait 86.4% idle
    Mem: 1030244k av, 1012284k used, 17960k free, 0k shrd, 76208k buff
    616860k actv, 127740k in_d, 17652k in_c
    Swap: 1582392k av, 327668k used, 1254724k free 279848k cached

    PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
    16639 mysql 15 0 31440 20M 1432 S 99.9 2.0 3:47 2 mysqld
    22028 root 15 0 10072 140 80 S 10.5 0.0 3:37 3 httpd
    253 root 15 0 0 0 0 SW 1.9 0.0 0:57 0 kjournald
    16506 mailnull 15 0 520 248 164 S 1.1 0.0 0:41 2 exim
    11 root 15 0 0 0 0 SW 0.9 0.0 7:54 2 kswapd
    26189 mailnull 22 0 0 0 0 Z 0.9 0.0 0:00 2 exim <defunct>
    25854 mydomai 16 0 1176 1176 768 R 0.3 0.1 0:00 0 top
    26096 nobody 15 0 0 0 0 Z 0.3 0.0 0:00 3 httpd <defunct>
    252 root 15 0 0 0 0 SW 0.1 0.0 2:14 1 kjournald
    16414 root 15 0 444 272 164 S 0.1 0.0 0:23 1 sshd
    16947 named 25 0 23848 19M 868 S 0.1 1.9 21:42 3 named
    18509 root 19 0 452 412 304 S 0.1 0.0 0:24 0 bash
    1 root 15 0 112 84 56 S 0.0 0.0 0:13 1 init
    2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
    3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
    4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2 migration/2
    5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3 migration/3
    6 root 15 0 0 0 0 SW 0.0 0.0 0:16 1 keventd
    7 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd_CPU0
    8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd_CPU1
    9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2 ksoftirqd_CPU2
    10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3 ksoftirqd_CPU3
    15 root 25 0 0 0 0 SW 0.0 0.0 0:00 1 bdflush
    12 root 15 0 0 0 0 SW 0.0 0.0 0:04 3 kscand/DMA
    WHT Addict
    Ask on WHT before you sign up with a no-value-host.

  6. #6
    am sorry, this information is not at all easy to read this way.

    I will attach here the right text file, please check.
    Attached Files Attached Files
    WHT Addict
    Ask on WHT before you sign up with a no-value-host.

  7. #7
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,520
    Yeah, by the looks of it, mySQL and Apache are both very intensly ran. What you need to do is get a server dedicated to mySQL. mySQL by itself can handle a LOT on a small server.

    My mySQL server is a P4 2.8 ghz w/ 1gb ram. It processes 20,000 mySQL queries per minute and runs at 90% idle.

    What concern me right now is that you claim severe lag, yet your server is mostly idle according to the CPU output. Consider checking for network problems and such. Your server appears to be a very powerful server, so a load of 0.61 should NOT even begin to phase your speed.

    If all else fails, and you have the money, get yourself a server dedicated to mySQL. Lease it the first month, and if it helps, buy it and colocate it.
    Jakiao

  8. #8
    thank you very much jakiao for your kind help

    I appreciate it
    WHT Addict
    Ask on WHT before you sign up with a no-value-host.

  9. #9
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,520
    No problem. Feel free to message me with any questions regarding this matter. I've had to deal with a smiliar problem twice now on two different companies.

    Have a good day.
    Jakiao

  10. #10
    Join Date
    Apr 2003
    Location
    Los Angeles, CA
    Posts
    820
    I don't know what kind of queries you run on your mySQL server, but it looks like only one thread (one mySQL process) is taking up 99.9% of the CPU. To me it looks like your queries or tables are not optimizes. Try running mySQL with the "log slow queries" option. See: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html
    Pings <1 ms, Unlimited Transfer, Lowest Price: http://localhost/

  11. #11
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,681
    run

    mysqladmin status
    What is the result?
    Steven Ciaburri | Industry's Best Server Management - Rack911.com
    Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
    Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
    FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance

  12. #12
    Join Date
    Nov 2003
    Posts
    385
    turn on slow_query logging in mysql and optimize the slow query(ies)

  13. #13
    Originally posted by thelinuxguy
    run



    What is the result?

    sorry to disappoint you ...... mysqladmin status ERROR 1045: Access denied . I am not root admin.
    WHT Addict
    Ask on WHT before you sign up with a no-value-host.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •