42 users online (0 members and 42 guests)  


Page 1 of 2 1 2 Last
  Results 1 to 15 of 17

Related

  1. Replies: 1
  2. PERL-MYSQL connection using ODBC    Forum: CGI Perl Forum
    Replies: 0
  3. Failed to create a web page    Forum: CGI Perl Forum
    Replies: 0
  4. html Anchor link failed    Forum: HTML Forum
    Replies: 1
  5. Replies: 1
  1. #1
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    mysql connection sometime failed

    Hi,

    I got a setup of mysql/apache/php running on my home server. The server is not the most powerfull 1Go of memory 2.66 Ghz cpu, and a 2Mbits bandwith. It is running under Fedora core 3.
    This should still be enough for the hosting of my home website and forum...

    the issue I have is that often during the evening the connection to mysql failed for some users and for me as well from time to time. It is happening mainly when there is many visitors on the forum (100 or more)

    The error message is the following :
    PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) in /var/www/html/lib/init_variables.php on line 30

    Do you have any idea of the reason of this problem? to reproduce the issue it is easy i just have to try running the apache benchmark :
    ab -c 50 -n 5000 http://192.168.0.1/testPage.phpl"

    this is really annoying for the visitors of my website and it seems that this is also happening on some other website : doing a google search with mysql.sock' (11) show many results ... but no answers !

    thank you in advance for your help.

  2. #2
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662

    Re: mysql connection sometime failed

    Hi, welcome to the forums.

    I would start with the errors logs. As its your own machine, you should have access.

    It should say why its shut down in there.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  3. #3
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    Re: mysql connection sometime failed

    Thanks for your quick reply.

    All I got in logs is in the httpd error_log is few line like the following :
    PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) in /var/www/html/lib/init_variables.php on line 30
    PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) in /var/www/html/lib/init_variables.php on line 30
    PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) in /var/www/html/lib/init_variables.php on line 30
    PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) in /var/www/html/lib/init_variables.php on line 30

    mysql doesn't shutdown, it is just not always replying to new request/connection , when there is too heavy traffic on the site.

    the mysql log and the linux message log doesn't show anything.

    I was thinking this might be due to the number of file descriptor so I increased it in the my.cnf and also for the users (ulimit) but this didn't change anything,

    I don't know if it's of any help but here is my mysql config file .

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    key_buffer = 16M
    max_allowed_packet = 16M
    table_cache = 1024
    sort_buffer_size = 5M
    net_buffer_length = 8K
    query_cache_size= 16M

    innodb_data_home_dir = /var/lib/mysql/
    innodb_data_file_path = ibdata1:40M:autoextend
    innodb_buffer_pool_size = 32M
    innodb_additional_mem_pool_size = 10M
    innodb_log_file_size = 32M
    innodb_log_buffer_size = 8M
    innodb_file_io_threads = 4
    innodb_thread_concurrency = 4

    max_connections = 256
    max_user_connections = 256
    max_connect_errors = 999999
    thread_cache = 8
    thread_cache_size = 20
    thread_stack = 128k
    open-files-limit = 8192
    connect_timeout = 10
    long_query_time = 10
    log_slow_queries
    back_log=20


    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    open-files-limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M


    Thanks again for your help

  4. #4
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662

    Re: mysql connection sometime failed

    Hey,

    Do you have phpMyAdmin? If so, you can do table checks in there to make sure you haven't got a corrupt table.

    If not, you cn manually check with 'CHECK TABLE tablename' and fix it with 'REPAIR TABLE tablename'

    Which forum software are you using by the way? Any modifications?
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  5. #5
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    Re: mysql connection sometime failed

    I do have phpmyadmin and the table check were not showing any issues. i also redeploy the complete set of data to make sure everything was fine.
    The forum I use is a default phpBB, but the issue can also appears on pages of the website, it really sound like this due to the number of visitor.

    the fact is that many other websites show this issue as well when there is too many connections. But the max connection is not the problem and the error message would then be different...

    AmI the first one on this forum who face this issue? If so if one of the member got a home test server on linux i believe the same issue could be reproduce by using the apache benchmark tool which emulate loads on the site.

    Thanks a lot for your help I really appreciate it.

  6. #6
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662

    Re: mysql connection sometime failed

    I am on a Fedora Core 3 machine right now, my actual home website is on a FreeBSD 4* machine. Both can run the benchmark testing fine (in fact, yesterday I ran a 'torture test' benchmark to check some performance issues with some scripts I'm auditing).

    Your machine seems easily well specced for a couple hundred visitors, presuming its dedicated and not running DVD's on it or something silly.

    After some more research, are you getting any messages with 'denied' in them in /var/log/messages ?
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  7. #7
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    Re: mysql connection sometime failed

    Actually the box is mainly used as aserver not even a X server started , no dvd or anything else so that memory can be free as much as possible,

    Selinux is configured correctly, it is still activated ( I configured it for mysql and apache, i have done a relabeb and used chcon for php eaccelerator for exemple) but even when I completly stop SElinux there is no difference.

    I find that very strange that the log of mysql and the one of system remains empty... Just the httpd error log contains something, currently I am again testing it with a ssh2 conncetion to the console, and it still failed the same way, I was this time using openSTA as a stressTesting tool.

    what i noticed when the error start to happen is that the number of process in the mysql processlist; is rising a lot... some time during heavy test I can see up to 100 processes, this I believe not expected as the request should go quite fast... but most of this process are sleeping. Only few SQL request are pending.
    It might be due to my settings but my my.cnf file doesn't looks to bad.

    I am really lost here.

    My last benchmark test just failed with 50 users again, here is the status at that period of time .

    mysql> status
    --------------
    mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686)

    Connection id: 58
    Current database:
    Current user: root@localhost
    SSL: Not in use
    Current pager: stdout
    Using outfile: ''
    Server version: 4.0.24-standard-log
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Client characterset: latin1
    Server characterset: latin1
    UNIX socket: /var/lib/mysql/mysql.sock
    Uptime: 20 min 38 sec

    Threads: 51 Questions: 737426 Slow queries: 9 Opens: 114 Flush tables: 1 Open tables: 108 Queries per second avg: 595.659
    --------------

    this might give you a better idea of what's going on...

  8. #8
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662

    Re: mysql connection sometime failed

    Could I have a link to your forum?

    At a guess, 9 slow queries says a lot. It may be that your searches are taking up a lot of sql time. The queries per second add up nicely to 100 users on an average page (phpBB uses between 5-10 per page)
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  9. #9
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    Re: mysql connection sometime failed

    currently I am on test phase so I can't give you an access to everything. The server is closed from the outside and hosted on an free hosting server in the meantime...

    Let me check If i can open a connection for you, i put a very high security level lately (for testing purpose and I only open the access a specific range of ip... I might have to be at home to change that, I am currently not in front of my server )

    Actually the page i am testing with apache benchmark is not a phpBB page but one page of my website which used a lot of heavy mysql querries... These pages are build with a home made Content Management System.

    Do you think that the fact that the querry which are executed on the server are bigs for a few pages could lead to the error message i have ? Because what bother me the more i the error message which sounds like an issue with my configuration or linux settings...

  10. #10
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662

    Re: mysql connection sometime failed

    If it always fails on the test page, then how about posting just the code for that here?
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  11. #11
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    Re: mysql connection sometime failed

    Yes I will post the code of a page which failed when doing the benchmark test...
    But it doesn't always failed, meaning for 5000 test with 50 concurent user only 500 or 1000 will failed the other just work fine without any error in the log...
    I grab the code and i post it as soon as possible, i am in the office currently so i will probably do that this evening.

    thanks again for your support, i hope we will find an answer after I will have posted the code.

  12. #12
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    Re: mysql connection sometime failed

    Just for testing purpose I wrote a very small testPage which contains just two querries to normal innoDB mysql table and the issue was appearing as well very quicly !!!

    Here is the code I used for this page :

    <html>
    <body>
    TEST >> <br>
    <?
    $myhote="localhost";
    $myuser="myuser";
    $mypass="mypass";
    $mydb="madb";

    $base_id = mysql_connect($myhote,$myuser,$mypass ) or die(error_log("mysqlError -> " . mysql_errno() . " - " . mysql_error()));
    mysql_select_db($mydb) or die(error_log("mysqlError -> " . mysql_errno() . " - " . mysql_error()));
    if ($base_id){
    $query = "select MODULE_ID, CODE, NAME, COLOR1, COLOR2 from cnt_module where STATUS=1 order by CODE ASC ;";
    $result = mysql_query($query)
    or die (mysql_error(). "no locales found");
    $cnt=0;
    while($row = mysql_fetch_array($result))
    {
    echo 'TEST Result : MODULE_ID = '. $row['MODULE_ID'] . 'CODE = '. $row['CODE'] . 'NAME = '. $row['NAME'] . 'COLOR1 = ' . $row['COLOR1'] . 'COLOR2 = ' . $row['COLOR2'] . "<br>";
    }
    mysql_free_result($result);

    $query = "select distinct b.LANGUAGE_ID, b.NAME from cnt_locale a, cnt_language b, cnt_locale_language c where a.STATUS=1 and b.STATUS=1 and a.CODE='CORP' and b.CODE='EN' and c.LOCALE_LANGUAGE= b.LANGUAGE_ID and c.LOCALE_ID = a.LOCALE_ID order by b.CODE ASC ;";
    $result = mysql_query($query)
    or die (mysql_error(). "offline language");
    if(mysql_num_rows($result) == 0) {
    mysql_free_result($result);
    echo "No language<br><br>";
    }else{
    $row = mysql_fetch_array($result);
    $lanid = $row[0];
    $lanname = $row[1];
    mysql_free_result($result);
    $query = "select distinct b.T_TRANSLATION, b.T_COMPANY_NAME from cnt_locale a, cnt_locale_trs b where a.LOCALE_ID='1' and b.T_CNT_ID=a.LOCALE_ID and b.T_LANGUAGE='".$lanid."';";
    $result = mysql_query($query)
    or die (mysql_error(). "offline language");
    if($row = mysql_fetch_array($result)){
    $locname = $row['T_TRANSLATION'];
    $compName = $row['T_COMPANY_NAME'];
    echo "translation : " . $locname . $compName . "<br><br>";
    }
    mysql_free_result($result);
    }
    mysql_close($base_id);
    }
    ?>
    </body>


    Does this means that the phpBB code or my home website code has nothing to do with the issue ?

    If this is the case then the issue might come from the configuration of linux or mysql...

    when testing this very small page I was having 289224K of memory free and used only 50 concurrent users on 5000 request ts

    This should be supported by a much smaller server than the one I have

    I hope you will have an idea but the more I look at it the less I understand it...

    thanks again.

  13. #13
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662

    Re: mysql connection sometime failed

    Me too :S

    Did you install mySQL from rpm's? And which version is it?
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  14. #14
    ajji's Avatar
    New User

    Status
    Offline
    Join Date
    May 2005
    Posts
    11

    Re: mysql connection sometime failed

    Hello,

    Yes the setup has been done with the official mysql rpm (not the one for fedora since the fedora rpms are for version 3.x)

    here are more info about the version I have :

    mysql> status
    --------------
    mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686)

    Connection id: 58
    Current database:
    Current user: root@localhost
    SSL: Not in use
    Current pager: stdout
    Using outfile: ''
    Server version: 4.0.24-standard-log
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Client characterset: latin1
    Server characterset: latin1
    UNIX socket: /var/lib/mysql/mysql.sock
    Uptime: 20 min 38 sec

    Just an other detail I didn't mention before, I am using innoDB tables, this might be a reason for the problem, so I will create a myisam table and run the same kind of smalltest as the one before, if it's still doesn't work, then I will run out of idea...

    Thanks again for your help on my issue, it s good too have some fast and usefull reply to my post, not all forums are like this

  15. #15
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662

    Re: mysql connection sometime failed

    Yeah, the innoDB tables immediately make you think of maybe a memory problem, but then your mysql error log would be full of messages...

    I'll be honest, as helpful as I like to be I'm a little stumped. If I was in your situation myself, I would actually be reinstalling mysql right now. Is that an option for you? It can sometimes help if you run up2date beforehand in case there are unresolved dependancies too.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?



Page 1 of 2 1 2 Last

Tags for this Thread