#1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533

    PHP Server running VERY slow


    I just rebuilt my local physical server:
    • CentOS 6.4 basic server
    • PHP Version 5.4.16 installed via yum with ius repo
    • Apache 2.2.15 installed via yum with CentOS repo
    • MySQL 5.1.69 installed via yum with CentOS repo


    Seems to work, but runs approximately 50 times slower than the same scripts on my VPS.

    To isolate the cause, I created a simple script to test speeds associated with database, files, math, etc tasks, but speeds were this time comparable to my VPS. I then added the db::script() test at the end which took about twice as long as on the VPS.

    I've disabled iptables and have selinux in permissive mode. I don't know if it is relevant, but I sometime use symbolic links. Please provide any any suggestions on what might be the cause and any recommended strategies to troubleshoot. Thank you

    PHP Code:
    <?php
        
    class db {
            private static 
    $instance NULL;
            private function 
    __construct() {}   //Make private
            
    private function __clone(){}   //Make private
            
    public static function db() //Get instance of DB
            
    {
                if (!
    self::$instance)
                {
                    try{
    self::$instance = new PDO("mysql:host=localhost;dbname=xxx;charset=utf8",'xxx','xxx',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
                    catch(
    PDOException $e){die(library::sql_error($e));}
                }
                return 
    self::$instance;
            }
            public static function 
    script($file//Get instance of script
            
    {
                return 
    shell_exec("mysql -uxxx -pxxx1 -hlocalhost -Dxxx < {$file}");
            }
        }
        class 
    object
        
    {
            private 
    $instance NULL;
        }

        
    $time0 microtime(true);

        
    $time microtime(true);
        for (
    $i=1$i<=1000$i++) {
            
    $newFile 'newfile.txt';
            
    $handle fopen($newFile'w') or die('Cannot open file:  '.$newFile); //implicitly creates file
            
    $text_variable file_get_contents("testfile.txt");
            
    fwrite($handle$text_variable);
            
    unlink($newFile);
        }
        echo(
    "<p>".(microtime(true)-$time)."</p>");

        
    $time microtime(true);
        for (
    $i=1$i<=1000$i++) {$obj=new object();}
        echo(
    "<p>".(microtime(true)-$time)."</p>");

        try{
            
    set_time_limit(240);    //change maximum execution time

            
    $time microtime(true);
            
    $sql 'DROP TABLE IF EXISTS testTable';
            
    db::db()->exec($sql);
            
    $sql 'CREATE TABLE testTable(ID INT UNSIGNED NOT NULL AUTO_INCREMENT, Data_ID INT NOT NULL, Time_ID INT NOT NULL,Data_Entry INT NOT NULL,PRIMARY KEY (ID) )';
            
    db::db()->exec($sql);
            echo(
    "<p>".(microtime(true)-$time)."</p>");

            
    $time microtime(true);
            
    $sql='INSERT INTO testTable(ID,Data_ID,Time_ID,Data_Entry) VALUES(:ID,:Data_ID,:Time_ID,:Data_Entry)'
            
    $stmt db::db()->prepare($sql);
            for (
    $i=1$i<=1000$i++) {$stmt->execute(array('ID'=>0,'Data_ID'=> 1,'Time_ID'=>1,'Data_Entry'=>10));}
            echo(
    "<p>".(microtime(true)-$time)."</p>");

            
    $time microtime(true);
            
    $sql='INSERT INTO testTable(ID,Data_ID,Time_ID,Data_Entry) VALUES(0,1,1,10)'
            
    $stmt db::db()->exec($sql);
            for (
    $i=1$i<=1000$i++){$stmt db::db()->exec($sql);}
            echo(
    "<p>".(microtime(true)-$time)."</p>");

            
    $time microtime(true);
            
    $sql='SELECT * FROM testTable'
            
    $stmt db::db()->query($sql);
            
    $data=$stmt->fetchAll(PDO::FETCH_ASSOC);
            echo(
    "<p>".(microtime(true)-$time)."</p>");
        }
        catch(
    PDOException $e){die('<pre>'.print_r($e,1).'</pre>');}

        
    //THIS CAUSES THE PROBLEMS!!!
        
    $time microtime(true);
        for (
    $i=1$i<=1000$i++) {db::script('updateTable.sql');}
        echo(
    "<p>".(microtime(true)-$time)."</p>");

        echo(
    "<p>".(microtime(true)-$time0)."</p>");

    ?>
    updateTable.sql
    Code:
    INSERT INTO testTable(ID,Data_ID,Time_ID,Data_Entry) VALUES(0,999,999,999);
    INSERT INTO testTable(ID,Data_ID,Time_ID,Data_Entry) VALUES(0,888,888,888);
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,960
    Rep Power
    9397
    Symlinks do take longer to use than regular files (since it's two or more file lookups) so you shouldn't just throw them around for the fun of it.

    Your db::script() fires up a shell that invokes mysql with a script to execute. That's already slow. You're saying it runs more slowly on VPS than not? How does the VPS virtualize the filesystem? Have you tried doing other actions that involve the shell/mysql (the program)/files to see how they compare?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    You're saying it runs more slowly on VPS than not?
    No, the opposite. Fast on the VPS and slow on the physical server. I've previously run the same applications on the same physical server, and did not have the performance issues.

    I don't think the symlinks or the shell that invokes the script is the culprit. Yes, they might be slower, but I am seeing tasks take from 50% to 16,000% longer on the physical server than the VPS. As such, I am thinking it is caused from insufficient memory allocation or some other configuration issue.

    I executed the test script that I posted in my first post, and witnessed the physical server running 81% slower. Some small tasks were as much as 500% slower, but the biggest contributor the shell_exec() and without this task, the physical server was only running 7% slower. Again, I ran these same scripts before rebuilding my server, and originally witnessed similar performance between the two servers. Why I don't see as significant of performance discrepancy that I am witnessing on my main script (which I didn't post, but it basically creates the database and infrastructure for my application), I have no idea.

    I went line by line through phpinfo, and compared the output, and hopefully the following can provide some clues:

    Configure Command for fast server
    Code:
    './configure' '--build=i686-redhat-linux-gnu' '--host=i686-redhat-linux-gnu' '--target=i686-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/var/lib' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--disable-debug' '--with-pic' '--disable-rpath' '--without-pear' '--with-bz2' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--with-xpm-dir=/usr' '--enable-gd-native-ttf' '--with-t1lib=/usr' '--without-gdbm' '--with-gettext' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-pcre-regex' '--with-zlib' '--with-layout=GNU' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--with-kerberos' '--enable-ucd-snmp-hack' '--enable-shmop' '--enable-calendar' '--with-libxml-dir=/usr' '--enable-xml' '--with-system-tzdata' '--with-mhash' '--with-apxs2=/usr/sbin/apxs' '--libdir=/usr/lib/php' '--enable-pdo=shared' '--with-mysql=shared,/usr' '--with-mysqli=shared,/usr/lib/mysql/mysql_config' '--with-pdo-mysql=shared,/usr/lib/mysql/mysql_config' '--with-pdo-sqlite=shared,/usr' '--without-gd' '--disable-dom' '--disable-dba' '--without-unixODBC' '--disable-xmlreader' '--disable-xmlwriter' '--without-sqlite3' '--disable-phar' '--disable-fileinfo' '--disable-json' '--without-pspell' '--disable-wddx' '--without-curl' '--disable-posix' '--disable-sysvmsg' '--disable-sysvshm' '--disable-sysvsem'
    Configure Command for slow server
    Code:
    './configure' '--build=x86_64-redhat-linux-gnu' '--host=x86_64-redhat-linux-gnu' '--target=x86_64-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/var/lib' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib64' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--disable-debug' '--with-pic' '--disable-rpath' '--without-pear' '--with-bz2' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--with-xpm-dir=/usr' '--enable-gd-native-ttf' '--with-t1lib=/usr' '--without-gdbm' '--with-gettext' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-pcre-regex' '--with-zlib' '--with-layout=GNU' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--with-kerberos' '--enable-ucd-snmp-hack' '--enable-shmop' '--enable-calendar' '--with-libxml-dir=/usr' '--enable-xml' '--with-system-tzdata' '--with-mhash' '--with-apxs2=/usr/sbin/apxs' '--libdir=/usr/lib64/php' '--enable-pdo=shared' '--with-mysql=shared,/usr' '--with-mysqli=shared,/usr/lib64/mysql/mysql_config' '--with-pdo-mysql=shared,/usr/lib64/mysql/mysql_config' '--with-pdo-sqlite=shared,/usr' '--without-gd' '--disable-dom' '--disable-dba' '--without-unixODBC' '--disable-xmlreader' '--disable-xmlwriter' '--without-sqlite3' '--disable-phar' '--disable-fileinfo' '--disable-json' '--without-pspell' '--disable-wddx' '--without-curl' '--disable-posix' '--disable-sysvmsg' '--disable-sysvshm' '--disable-sysvsem'
    Loaded modules for fast server
    Code:
    core prefork http_core mod_so mod_auth_basic mod_auth_digest mod_authn_file mod_authn_alias mod_authn_anon mod_authn_dbm mod_authn_default mod_authz_host mod_authz_user mod_authz_owner mod_authz_groupfile mod_authz_dbm mod_authz_default util_ldap mod_authnz_ldap mod_include mod_log_config mod_logio mod_env mod_ext_filter mod_mime_magic mod_expires mod_deflate mod_headers mod_usertrack mod_setenvif mod_mime mod_dav mod_status mod_autoindex mod_info mod_dav_fs mod_vhost_alias mod_negotiation mod_dir mod_actions mod_speling mod_userdir mod_alias mod_rewrite mod_proxy mod_proxy_balancer mod_proxy_ftp mod_proxy_http mod_proxy_connect mod_cache mod_suexec mod_disk_cache mod_cgi mod_version mod_php5 mod_ssl
    Loaded modules for slow server
    Code:
    Same, except also includes mod_substitute and mod_proxy_ajp
    Fast Server: Timeouts: Connection: 120 - Keep-Alive: 15
    Slow Server: Timeouts: Connection: 60 - Keep-Alive: 15

    Fast Server Core extension_dir /usr/lib/php/modules
    Slow Server Core extension_dir /usr/lib64/php/modules

    Fast Server MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient
    Slow Server MYSQL_LIBS -L/usr/lib64/mysql -lmysqlclient

    Thank you for your help.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    14
    Rep Power
    0
    hi dude

    Here is my tips to you

    For PHP use some kind of opcode cache like APC or eAccelerator otherwise PHP has to parse your files on each request.


    For general apache tuning you should do some googling, couple of things like disabling .htaccess files comes to mind but it still should be faster than JSP...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    I don't really understand how opcode cache like APC or eAccelerator works, but I don't think it is the cause because I am only running a couple of scripts. EDIT. Installed APC. No improvement. Also, don't think it is Apache as the performance hits are happening within a given PHP script.

    I've provided a new test script which better demonstrates the difference in performance, and displayed the results in the below table.

    createdatabase.sql is just some SQL script which creates 100 table, updatedatabase.sql is just SQL script which adds 282 records, and Vertical Markets.csv has 52 rows.

    Please provide any guidance as I am at my wits end.

    Code:
    ╔════════════╦═════════╦═══════════╦═══════════╦═══════════╗
    ║    Task    ║ VPS (s) ║ Local (s) ║ Delta (s) ║ Delta (%) ║
    ╠════════════╬═════════╬═══════════╬═══════════╬═══════════╣
    ║ Delete DB  ║ 0.287   ║ 5.792     ║ 5.505     ║ 1918%     ║
    ║ Create DB  ║ 1.839   ║ 20.827    ║ 18.988    ║ 1032%     ║
    ║ Update DB  ║ 0.316   ║ 16.470    ║ 16.155    ║ 5118%     ║
    ║ Add VMs    ║ 0.039   ║ 3.264     ║ 3.225     ║ 8219%     ║
    ║ Total Time ║ 2.481   ║ 46.354    ║ 43.873    ║ 1768%     ║
    ╚════════════╩═════════╩═══════════╩═══════════╩═══════════╝
    PHP Code:
    <?php
        
    class db {
            private static 
    $instance NULL;
            private function 
    __construct() {}   //Make private
            
    private function __clone(){}   //Make private
            
    public static function db() //Get instance of DB
            
    {
                if (!
    self::$instance)
                {
                    try{
    self::$instance = new PDO("mysql:host=localhost;dbname=bidjunction;charset=utf8",'xxx','xxx',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
                    catch(
    PDOException $e){die(library::sql_error($e));}
                }
                return 
    self::$instance;
            }
            public static function 
    script($file//Get instance of script
            
    {
                return 
    shell_exec("mysql -uxxx -xxx-hlocalhost -Dbidjunction < {$file}");
            }
        }

        function 
    snapshot($msg,$last_time)
        {
            global 
    $table;
            
    $current_time=microtime(true);
            
    $delta_time=$current_time-$last_time;
            
    $table.="<tr><td>{$msg}</td><td>{$delta_time}</td></tr>";
            
    $msg=$msg.': '.$delta_time;
            echo(
    "<p>{$msg}</p>");
            
    syslog(LOG_INFO,$msg);
            return 
    $current_time;
        }
        function 
    print_table($start_time,$table)
        {
            
    $delta=microtime(true)-$start_time;
            return 
    "<table>{$table}<tr><td>Total Time</td><td>{$delta}</td></tr></table>";
        }

        
    date_default_timezone_set('America/Los_Angeles');
        
    error_reporting(E_ALL);
        
    $last_time=microtime(true);
        
    $start_time=$last_time;
        
    $table=null;
        
    set_time_limit(2000);    //change maximum execution time

        
    try
        {
            
    $db='bidjunction';
            
    $sql="DROP DATABASE IF EXISTS {$db}";
            
    db::db()->exec($sql);
            
    $sql="CREATE DATABASE {$db}";
            
    db::db()->exec($sql);
            
    $sql="USE {$db}";
            
    db::db()->exec($sql);
        }
        catch(
    PDOException $e){die('<pre>'.print_r($e,1).'</pre>');}
        
    $last_time=snapshot('Delete DB',$last_time);

        
    db::script('/var/www/bidjunction/ayb_application/initial_install/createdatabase.sql');
        
    $last_time=snapshot('Create DB',$last_time);

        
    db::script('/var/www/bidjunction/ayb_application/initial_install/updatedatabase.sql');
        
    $last_time=snapshot('Update DB',$last_time);
     
        
    $input_file "/var/www/bidjunction/ayb_application/initial_install/Vertical Markets.csv";
        
    $file fopen($input_file"r");
        try
        {
            
    $sql ='INSERT INTO vertical_markets (id, name, list_order) VALUES (NULL,?,?)';
            
    $stmt db::db()->prepare($sql);
            
    //data is provided as array in the following order: 0:name, 1:list_order
            
    while (($data fgetcsv($file100000",")) !== FALSE)
            {
    $stmt->execute(array($data[0],$data[1]));}
        }
        catch(
    PDOException $e){die('<pre>'.print_r($e,1).'</pre>');}
        
    fclose($file);

        
    $last_time=snapshot('Add VMs',$last_time);

        exit(
    print_table($start_time$table));

    ?>
    Last edited by NotionCommotion; June 25th, 2013 at 10:06 AM.
  10. #6
  11. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    If all your script is doing is kicking off the command-line mysql interface, then any notable speed difference is likely coming from there. That isn't to say PHP itself is performing more slowly, but the test won't be good for measuring PHP specifically.

    Do you have separate MySQL server instances in each environment's "localhost", or are they connecting to the same one?

    Edit: Ah. Nevermind. I see for the INSERTS you're reading from the file and executing them directly.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    Thanks dmittner,

    I think you are one to something.

    I ran updatedatabase.sql from the shell, and it appeared to go much faster on the VPS.

    If you have any ideas, please advise.

    I will also do some research, post any new questions (if any) as a new post on the MySQL forum, and post my final findings (please let there be some!) back on this post.
  14. #8
  15. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by NotionCommotion
    Thanks dmittner,

    I think you are one to something.

    I ran updatedatabase.sql from the shell, and it appeared to go much faster on the VPS.
    Ah- then it's possible the problem is with MySQL more than it is with PHP. So to the question previously asked, do you have both environments accessing the same MySQL server, or does each environment have its own?

    If you're seeing notable speed differences when running commands directly through the command-line MySQL interface then that's the direction we should start looking.

    Folks in the MySQL forum might be able to help with this better than I, too.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0
    What file system type do you have on both servers? Also know the HDD specs for both as well?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    Originally Posted by IINCON
    What file system type do you have on both servers? Also know the HDD specs for both as well?
    Check below for file system. I don't know the HDD spec, but can figure it out if necessary. My confusion is why the slow laptop used to be fast before I upgraded CentOS. I've since posted a similar question at http://forums.devshed.com/mysql-help...ow-947507.html and provided more information about my system there.

    Thank you for your help!


    VPS:
    Code:
    [root@vps ~]# df -T
    Filesystem    Type   1K-blocks      Used Available Use% Mounted on
    /dev/simfs   simfs    20971520  16187440   4784080  78% /
    none         tmpfs     6224432         4   6224428   1% /dev
    none         tmpfs     6224432         0   6224432   0% /dev/shm
    [root@vps ~]#
    Laptop:
    Code:
    [root@server1 ~]# df -T
    Filesystem    Type   1K-blocks      Used Available Use% Mounted on
    /dev/mapper/vg_server1-lv_root
                  ext4    72383800   4243964  64462860   7% /
    tmpfs        tmpfs      956352         0    956352   0% /dev/shm
    /dev/sdb1     ext4      495844     60948    409296  13% /boot
    [root@server1 ~]#
  20. #11
  21. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Try starting the MySQL server daemon with the --skip-name-resolve option.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    Thanks E-Oreo,

    I stopped the service, and then started is using "service mysqld start --skip-name-resolve" and "/etc/init.d/mysqld start --skip-name-resolve", but obviously I am not doing it right. Could you be kind(er) and give me more detailed instructions?

    Thanks
  24. #13
  25. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    It may also work to add to the mysqld section of my.cnf the line:
    skip-name-resolve

    Otherwise, try shutting down mysql, then invoking it manually with:
    mysqld --skip-name-resolve
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    E-Oreo,

    Never was able to add options at the command line, but was able to do so from my.cnf, and confirmed that it was changed using SHOW variables. It had positive effect.

IMN logo majestic logo threadwatch logo seochat tools logo