PHP and Apache Ignite - very slow query

Question

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="clientMode" value="true"/> -->
   <!-- Enabling ODBC. -->
   <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?

EDIT:

So, problem was with offset and single machine comparison scenario. This article can be helped: http://apache-ignite-users.70518.x6.nabble.com/PHP-and-Apache-Ignite-very-slow-query-td18857.html

You should start by determining phase which took most of the time. Is it establishing a connection? Querying? Counting? Printing results? — alamar
I don't understand your logic. Mysql works all right but you want to use something else. I never seen an ODBC driver used for the anything mission critical, while mysql is used by Facebook and Wikipedia. Is your "big data" bigger than theirs? — Your Common Sense
Yes, on the production we have very big data. MySQL is working slow... so we need something much faster... and I start to test Apache Ignite. Of course we haven't big data like Facebook and Wiki... but this is only test. At this moment this is my goal - to run simple test. — Radosław Andraszyk
1. did you ever try to optimize mysql? What are your "very big data in numbers"? 2. There are in-memory databases that are much better integrated with PHP - Mongo, Tarantool, Sphinx, some others. Again why an obscure technology through a third-party driver? 3. did you test your query in the console, without PHP? — Your Common Sense

isapego isapego · Accepted Answer · 2017-12-07T10:41:36

First of all, you should use exactly the same code if you are going to compare performance. You use ODBC driver for Ignite, but you say that you use default MySQL driver. Do you even use PHP for MySQL test? In your benchmark it is unclear if you are benchmarking Ignite, PDO or PHP I/O itself. Try using exactly the same code for MySQL - it may make things more clear.

Second, Ignite is written in Java and run on JVM. This means that you need to "warm up" it to get real numbers, which you are going to see in production. Observed performance may be MUCH lower on a "cold" JVM, which you just started and have not run a single query on it.

PHP and Apache Ignite - very slow query

EDIT:

1 Answers