very slow - MySQL

Discussion in 'PSA 2.0 Troubleshooting and Questions' started by kark, Feb 13, 2002.

  1. kark

    kark Guest

    One server is _very_ slow .... load average is about 65. :( You can imagine it takes minutes to log in and stuff. When I look (ps faux) I see logs of mysql messages like this:

    mysql 651 0.0 1.6 26836 4292 ? S Feb09 0:16 \_ /usr/local/slash/mysql/libexec/mysqld --basedir=/usr/local/slash/mysql --datadir=/usr
    mysql 653 0.0 1.6 26836 4292 ? D Feb09 0:14 \_ /usr/local/slash/mysql/libexec/mysqld --basedir=/usr/local/slash/mysql --datadir=
    mysql 654 0.0 1.6 26836 4292 ? S Feb09 2:51 \_ /usr/local/slash/mysql/libexec/mysqld --basedir=/usr/local/slash/mysql --data

    So I suppose it's not a script which is running like a crazy.

    What could this be?

    Thanks,
    Kark
     
  2. kark

    kark Guest

    Well ... I want to know who I can blame this .. so ... I'm reading the .err document in the mysql directory, but ... I'm not sure where to look. Who can help? :)


    P.S ... I'm getting a _lot_ of these messages. Are these normal messages?

    020130 16:31:03 Aborted connection 16450 to db: 'plesk' user: 'admin' host: `localhost' (Got an error reading communication packets)
     
  3. utman

    utman Guest

    I had this error as well it was due to me installing a cron job to see if mysqld was running except i forgot to tell it when to run and it was running all the time instead of every 10 minutes so it was just bogging it down until it stopped and then it tried to restart it and basically made it a mess. I then just edited the cron to execute every 10 minutes and all was happy in pleskville.
     
  4. kark

    kark Guest

    Except .. I didn't change anything and my clients don't have access to the cron. :)

    Kark
     
  5. utman

    utman Guest

    What does your mysql error log say?

    I think it is in /usr/local/psa/mysql/var/ns.servername.err
     
  6. serve-you.net

    serve-you.net Guru

    Messages:
    1,896
    AFAIK that means that the connection got dropped during transmission. Like say, when MySQL restarts.
     
  7. kark

    kark Guest

    Well ... it says a lot, but not exactly what the problem is:

    020213 21:08:31 Aborted connection 123890 to db: 'name' user: 'user' host: `name_server' (Got an error reading communication packets)
    020213 21:09:30 /usr/local/plesk/mysql/libexec/mysqld: Forcing close of thread 124007 user: 'usera'
    020213 21:09:30 /usr/local/plesk/mysql/libexec/mysqld: Forcing close of thread 123994 user: 'admin'
    and so on and so on.


    But ... when I turned everything on again it went wrong again, then the same errors came back, except it started with:

    020213 21:08:31 Aborted connection 123890 to db: 'db' user: 'userz' host: `localhost' (Got an error reading communication packets)


    One client of mine has filled in the servername as host (instead of localhost) ... could this be the problem?

    Kark
     
  8. kark

    kark Guest

    But .. I get this message almost every second ... is _that_ normal? :)

    Kark
     
  9. serve-you.net

    serve-you.net Guru

    Messages:
    1,896
    Nope not normal at all.
    Check
    mysqladmin status -uadmin -p

    This should show you all of the open queries, which may help you pinpoint whos doing what.
     
  10. kark

    kark Guest

    I get this:
    Uptime: 2163 Threads: 1 Questions: 164003 Slow queries: 0 Opens: 2030 Flush tables: 1 Open tables: 64 Queries per second avg: 75.822

    (I have turned off the domain I think who caused the problem btw and I don't have any more problems now .. can it be because he used the server name instead of localhost?)
     
  11. serve-you.net

    serve-you.net Guru

    Messages:
    1,896
    Possibly. I'm no MySQL expert, but it seems like it could be causing requests to leave the server and come back in, when calling via domain name. I could be totally wrong thinking that though.

    I usually lock all of the db's down to localhost, so that people cannot connect to the db except through their cripts on the server. Unfortunately PSA does not setup db's like this, so you have to manually go in and change each one.
     
  12. kark

    kark Guest

    He didn't have the server(host)name but the IP address in the script. Now that I changed it to 'localhost' it's ok. But ... now ... can I do something to prevent this? I don't think it's an option to change all the db's on every server. It's very time consuming. Can't I stop requests except when they filled in 'localhost' ?

    Kark
     
  13. serve-you.net

    serve-you.net Guru

    Messages:
    1,896
    The only way to stop this is to change the "Host" on the mysql.db table. The way PSA sets it up is with a wildcard, which allows you to connect to that bd from anywhere. This needs to be changed to localhost. Otherwise, I don't know of any other way to stop this.
     
  14. kark

    kark Guest

    Yeah, I know Plesk has a few "flaws" but I don't understand why they haven't changed that bit. Oh well ... I must stop see things as "flaws" and see them more as a 'feature'. :)

    Anyways ... can someone write a scripts so every night (or hour, whatever) it takes a look at every db and when a db is '%' it will change the setting to 'localhost' ?

    Or .. can't you stop it for making a rule in IPchains? To be honest, I am not familiair with IPchains ... someone built that for me. :eek:

    Kark
     

Share This Page