PHP and Apache Ignite - very slow query

classic Classic list List threaded Threaded
12 messages Options
bagsiur bagsiur
Reply | Threaded
Open this post in threaded view
|

PHP and Apache Ignite - very slow query

Firstly, sorry for my English.

I start to use Apache Ignite with PHP (PDO, UnixODBC, Ignite ODBC driver). I
successfully installed it and configured Ignite, DSN and ODBC on my linux
server (Debian). I installed php-odbc extension and everything looks well.

I start Apache Ignite by: ./bin/ignite.sh php-ignite.xml where
php-ignite.xml is my config(default config + odbc enable):

<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
   xsi:schemaLocation="
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/util
    http://www.springframework.org/schema/util/spring-util.xsd">
   <bean id="ignite.cfg"
class="org.apache.ignite.configuration.IgniteConfiguration">
   
   
   <property name="odbcConfiguration">
       <bean
class="org.apache.ignite.configuration.OdbcConfiguration"></bean>
   </property>
</bean>

I wrote a simple PHP script to connect with Ignite by PDO and put some
custom data in it:

<?php

try {

    $db = new PDO('odbc:Apache Ignite');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = 'CREATE TABLE IF NOT EXISTS test_md5 (id int PRIMARY KEY, md5_1
VARCHAR, md5_2 VARCHAR, md5_3 VARCHAR, md5_4 VARCHAR, md5_5 VARCHAR, md5_6
VARCHAR, md5_7 VARCHAR, md5_8 VARCHAR, md5_9 VARCHAR) WITH
"atomicity=transactional,cachegroup=somegroup"';

    $db->exec($sql);

    for($i=0; $i<=1000000; $i++){
        $md5 = md5($i);
        $sql = "INSERT INTO test_md5 (id, md5_1, md5_2, md5_3, md5_4, md5_5,
md5_6, md5_7, md5_8, md5_9) VALUES ($i, '$md5', '$md5', '$md5', '$md5',
'$md5', '$md5', '$md5', '$md5', '$md5');";
        $db->exec($sql);
    }


} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}

On this point everythink looks nice. Data is set to the cluster. Now I wont
to do some tests, to check if Apache Ignite is faster solution for big data
then MySQL. This is my test (simple select only):

<?php

error_reporting(E_ALL);
$startTime = microtime(true);

try {

    $ignite = new PDO('odbc:Apache Ignite');
    $ignite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $nRows = $ignite->query('select count(*) from test_md5')->fetchColumn();
    echo 'Rows count: '.$nRows.'<br><br>';

    $stmt = $ignite->prepare("select * from test_md5 limit 60500,10");
    $stmt->execute();
    $rows = $stmt->fetchAll();

    foreach($rows as $item){
        echo 'id: '.$item['ID'].' - md5: '.$item['MD5_1'].'<br>';
    }


} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}

echo "<br><br>Speed test:  " . number_format(( microtime(true) -
$startTime), 4) . " Seconds\n";

Results: Speed test: 1.3717 seconds My question is: Why does Apache Ignite
work so slow?

When I do the same with MySQL, results are much faster: Speed test: 0.0019
seconds

Of course this is a simple test but I wan't to use Apache Ignite to work
with big data.

How can I configure Ignite corectly?

what am I doing wrong?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Igor Sapego-1 Igor Sapego-1
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

Do you test MySql through PHP and ODBC as well?
Have you "warmed up" the JVM prior testing?

Also, I can see code which is not related to Apache Ignite
but can take a lot of time, i.e. printing of 60500 records.

Best Regards,
Igor

On Tue, Dec 12, 2017 at 11:37 AM, bagsiur <[hidden email]> wrote:
Firstly, sorry for my English.

I start to use Apache Ignite with PHP (PDO, UnixODBC, Ignite ODBC driver). I
successfully installed it and configured Ignite, DSN and ODBC on my linux
server (Debian). I installed php-odbc extension and everything looks well.

I start Apache Ignite by: ./bin/ignite.sh php-ignite.xml where
php-ignite.xml is my config(default config + odbc enable):

<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
   xsi:schemaLocation="
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/util
    http://www.springframework.org/schema/util/spring-util.xsd">
   <bean id="ignite.cfg"
class="org.apache.ignite.configuration.IgniteConfiguration">


   <property name="odbcConfiguration">
       <bean
class="org.apache.ignite.configuration.OdbcConfiguration"></bean>
   </property>
</bean>

I wrote a simple PHP script to connect with Ignite by PDO and put some
custom data in it:

<?php

try {

    $db = new PDO('odbc:Apache Ignite');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = 'CREATE TABLE IF NOT EXISTS test_md5 (id int PRIMARY KEY, md5_1
VARCHAR, md5_2 VARCHAR, md5_3 VARCHAR, md5_4 VARCHAR, md5_5 VARCHAR, md5_6
VARCHAR, md5_7 VARCHAR, md5_8 VARCHAR, md5_9 VARCHAR) WITH
"atomicity=transactional,cachegroup=somegroup"';

    $db->exec($sql);

    for($i=0; $i<=1000000; $i++){
        $md5 = md5($i);
        $sql = "INSERT INTO test_md5 (id, md5_1, md5_2, md5_3, md5_4, md5_5,
md5_6, md5_7, md5_8, md5_9) VALUES ($i, '$md5', '$md5', '$md5', '$md5',
'$md5', '$md5', '$md5', '$md5', '$md5');";
        $db->exec($sql);
    }


} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}

On this point everythink looks nice. Data is set to the cluster. Now I wont
to do some tests, to check if Apache Ignite is faster solution for big data
then MySQL. This is my test (simple select only):

<?php

error_reporting(E_ALL);
$startTime = microtime(true);

try {

    $ignite = new PDO('odbc:Apache Ignite');
    $ignite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $nRows = $ignite->query('select count(*) from test_md5')->fetchColumn();
    echo 'Rows count: '.$nRows.'<br><br>';

    $stmt = $ignite->prepare("select * from test_md5 limit 60500,10");
    $stmt->execute();
    $rows = $stmt->fetchAll();

    foreach($rows as $item){
        echo 'id: '.$item['ID'].' - md5: '.$item['MD5_1'].'<br>';
    }


} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}

echo "<br><br>Speed test:  " . number_format(( microtime(true) -
$startTime), 4) . " Seconds\n";

Results: Speed test: 1.3717 seconds My question is: Why does Apache Ignite
work so slow?

When I do the same with MySQL, results are much faster: Speed test: 0.0019
seconds

Of course this is a simple test but I wan't to use Apache Ignite to work
with big data.

How can I configure Ignite corectly?

what am I doing wrong?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

bagsiur bagsiur
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

This post was updated on .
Hi and thank you for your answer.

Im not test mysql with odbc driver and at this moment my goal is to find
more faster solution then mysql. I know that mysql is working on native
mysql driver and ODBC is a third-party driver but this is recommended in official Apache Ignite documentation:
https://apacheignite.readme.io/v2.0/docs/odbc-driver

In my simple script, SQL query with "limit 60500,10" mean: start with 60500
record and give me 10 recors. So script print only 10 lines.

So, my question is still actually. Is it possible to run Apache Ignite with
ODBC driver to work faster with standard SQL queries then MySQL in standard
configuration?

Thanks for reply :)



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Wolfram Huesken Wolfram Huesken
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

Hi!

If you don't run both queries from PHP, you cannot really compare the
results. Running queries from the mysql console means that you don't
have to establish a connection, which takes quite some time.

You don't have to use ODBC to connect to MySQL, PDO will do a fine job
and makes your data comparable.

Cheers
Wolfram

On 12/12/2017 21:52, bagsiur wrote:

> Hi and thank you for your answer.
>
> Im not test mysql with odbc driver and at this moment my goal is to find
> more faster solution like mysql. I know that mysql is working on native
> mysql driver and ODBC is a third-party driver but this is recommended
> solution in official Apache Ignite documentation:
> https://apacheignite.readme.io/v2.0/docs/odbc-driver
>
> In my simple script SQL query with "limit 60500,10" mean: start with 60500
> record and give me 10 recors. So script print only 10 lines.
>
> So, my question is still actually. Is it possible to run Apache Ignite with
> ODBC driver to work faster with standard SQL queries then MySQL in standard
> configuration?
>
> Thanks for reply :)
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
bagsiur bagsiur
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

This post was updated on .
Maybe Im not explain everythinnk correctly. I run mysql queries by PHP of
course

So, one more time:

I prepare the same database, first db in MySQL and second in Apache Ignite.
Database have the same size and number of rows (~6 milion rows).

I prepare the same PHP script (as in my first post) useing PDO to connect.
Only one difference was beetwen connecting:

For mysql:

$mysql = new PDO('mysql:host=localhost;dbname=speed_test', 'root',
'*********');

For ignite:

$ignite = new PDO('odbc:Apache Ignite');

Here is my /etc/odbc.ini:

[Apache Ignite]
description=Apache Ignite
driver=Apache Ignite
ADDRESS=127.0.0.1:10800

And /etc/odbcinst.ini:

[Apache Ignite]
Description=Apache Ignite
Driver=/usr/local/lib/libignite-odbc.so
Setup=/usr/local/lib/libignite-odbc.so
DriverODBCVer=03.00
FileUsage=0
UsageCount=1

I do a lot of a different test... In one of them I was test only time for run a queries like:

$startTime = microtime(true);
$nRows = $mysql->query('select count(*) from test_md5')->fetchColumn();
$stmt = $mysql->prepare("select * from test_md5 where md5_2 like '%a2d6%' or md5_9 like '%a2d6%' order by id limit 20");
$endTime = microtime(true);

Problem is not with connecting time.

So, my question I still actually :)





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Wolfram Huesken Wolfram Huesken
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

Sorry, then I got you wrong. In this case your results are of course
comparable.

Cheers
Wolfram

On 12/12/2017 22:14, bagsiur wrote:

> Maybe Im not explain everythinnk correctly. I run mysql queries by PHP of
> course
>
> So, one more time:
>
> I prepare the same database, first db in MySQL and second in Apache Ignite.
> Database have the same size and number of rows (~6 milion rows).
>
> I prepare the same PHP script (as in my first post) useing PDO to connect.
> Only one difference was beetwen connecting:
>
> For mysql:
>
> $mysql = new PDO('mysql:host=localhost;dbname=speed_test', 'root',
> '*********');
>
> For ignite:
>
> $ignite = new PDO('odbc:Apache Ignite');
>
> Here is my /etc/odbc.ini:
>
> [Apache Ignite]
> description=Apache Ignite
> driver=Apache Ignite
> ADDRESS=127.0.0.1:10800
>
> And /etc/odbcinst.ini:
>
> [Apache Ignite]
> Description=Apache Ignite
> Driver=/usr/local/lib/libignite-odbc.so
> Setup=/usr/local/lib/libignite-odbc.so
> DriverODBCVer=03.00
> FileUsage=0
> UsageCount=1
>
> So, my question I still actually :)
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

--
Wolfram Huesken
27 Paya Lebar Road
#11-03 Paya Lebar Residences
Paya Lebar 409042
Singapore

Mobile (SG): +65-96464190
Mobile (TH): +66-98-837-7748
E-Mail: [hidden email]
Igor Sapego-1 Igor Sapego-1
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

So, have you "warmed up" the JVM prior testing?


Best Regards,
Igor

On Tue, Dec 12, 2017 at 5:18 PM, Wolfram Huesken <[hidden email]> wrote:
Sorry, then I got you wrong. In this case your results are of course comparable.

Cheers
Wolfram


On 12/12/2017 22:14, bagsiur wrote:
Maybe Im not explain everythinnk correctly. I run mysql queries by PHP of
course

So, one more time:

I prepare the same database, first db in MySQL and second in Apache Ignite.
Database have the same size and number of rows (~6 milion rows).

I prepare the same PHP script (as in my first post) useing PDO to connect.
Only one difference was beetwen connecting:

For mysql:

$mysql = new PDO('mysql:host=localhost;dbname=speed_test', 'root',
'*********');

For ignite:

$ignite = new PDO('odbc:Apache Ignite');

Here is my /etc/odbc.ini:

[Apache Ignite]
description=Apache Ignite
driver=Apache Ignite
ADDRESS=127.0.0.1:10800

And /etc/odbcinst.ini:

[Apache Ignite]
Description=Apache Ignite
Driver=/usr/local/lib/libignite-odbc.so
Setup=/usr/local/lib/libignite-odbc.so
DriverODBCVer=03.00
FileUsage=0
UsageCount=1

So, my question I still actually :)





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/


--
Wolfram Huesken
27 Paya Lebar Road
#11-03 Paya Lebar Residences
Paya Lebar 409042
Singapore

Mobile (SG): +65-96464190
Mobile (TH): +66-98-837-7748
E-Mail: [hidden email]

bagsiur bagsiur
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

Thanks for reply.

What do you mean with "warmed up" the JVM prior testing?

How can I do this?

Do you mean to prepare JAVA application with some SQL queries like my PHP
scripts from first post, and run it in JVM to check query time?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
dmagda dmagda
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

Ignite’s strength is an ability to store and process data in a distributed way in-memory. It means that while Ignite might loose to an RDBMS in a single machine comparison scenario, it will outperform in the scenarios where a lot of data is stored cluster wide.

Considering this, try to start a cluster of separated machine, preload data that’s meaningful to your application/service and execute benchmarks for some period of time (minutes, hours). The whole point is that it should be just a single execution of a query on a single machine.

Hope it helps.
Denis


> On Dec 12, 2017, at 12:34 PM, bagsiur <[hidden email]> wrote:
>
> Thanks for reply.
>
> What do you mean with "warmed up" the JVM prior testing?
>
> How can I do this?
>
> Do you mean to prepare JAVA application with some SQL queries like my PHP
> scripts from first post, and run it in JVM to check query time?
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/

bagsiur bagsiur
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

ok, I will try to build cluster on 3 machines and I will prepare tests one
more time.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Igor Sapego Igor Sapego
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

Also, please see for example this [1] article on why offset
can be slow and can cause troubles (especially in a distributed system)


Best Regards,
Igor

On Wed, Dec 13, 2017 at 9:12 AM, bagsiur <[hidden email]> wrote:
ok, I will try to build cluster on 3 machines and I will prepare tests one
more time.

bagsiur bagsiur
Reply | Threaded
Open this post in threaded view
|

Re: PHP and Apache Ignite - very slow query

Ok, thank you for reply. Now I understand where is mistake.

See you soon :)



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/