Help with slow server side rending
Help with slow server side rending
Hello,
I am pretty new to DataTables. I use to use tablesorter, but my new project requires several millions of records at any given time. I was told DataTables is what all the cool kids use. I was just like to first say that I am very impressed with this library so far.
I ran through the documentation, examples, and API. Based on my needs, it looks like I needed to use server side scripting. When running test data (~50,000) records, it seemed to work great for me. The table rendered in less than a second. Then i jumped over to the million+ data records, and then my troubles started. I have yet to let it run all the way through. The longest I let it run was about 20 minutes or so.
I am using PHP and MySQL to access the database. My version of dataTables is 1.9.3 I am using the server side template provided in the example.
Here is my table initalization:
[code]
var dataTable = $('table').dataTable({
"sAjaxSource": "php/pop_table.php",
"sScrollY": "200px",
"sDom": 'frtiS',
"bProcessing": true,
"bSortClasses": false,
"bDeferRender": true,
"bServerSide": true,
"oScroller": {
"loadingIndicator": true
}
}).fnSetFilteringDelay(250);
[/code]
Here is the portion of the php script that I modified:
[code]
$aColumns = array( 'date', 'phone', 'roaming', 'call_duration', 'overseas' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "phone";
/* DB table to use */
$sTable = "calls";
/* Database connection information */
$gaSql['user'] = "#########";
$gaSql['password'] = "########";
$gaSql['db'] = "#########";
$gaSql['server'] = "#######";
[/code]
I didn't modify the anything else in the php script because I'm currently testing with a basic query.
I read just about every thread in the forum here and outside sources. I tried about 20 different things. You can see I am currently using the filteringDelay plugin, Scroller, DeferRender, and disabled SortClasses.
Unfortunately I ca
Please let me know any other information you need from me.
Thanks in advance for the help.
Debug Code: http://debug.datatables.net/unalul
I am pretty new to DataTables. I use to use tablesorter, but my new project requires several millions of records at any given time. I was told DataTables is what all the cool kids use. I was just like to first say that I am very impressed with this library so far.
I ran through the documentation, examples, and API. Based on my needs, it looks like I needed to use server side scripting. When running test data (~50,000) records, it seemed to work great for me. The table rendered in less than a second. Then i jumped over to the million+ data records, and then my troubles started. I have yet to let it run all the way through. The longest I let it run was about 20 minutes or so.
I am using PHP and MySQL to access the database. My version of dataTables is 1.9.3 I am using the server side template provided in the example.
Here is my table initalization:
[code]
var dataTable = $('table').dataTable({
"sAjaxSource": "php/pop_table.php",
"sScrollY": "200px",
"sDom": 'frtiS',
"bProcessing": true,
"bSortClasses": false,
"bDeferRender": true,
"bServerSide": true,
"oScroller": {
"loadingIndicator": true
}
}).fnSetFilteringDelay(250);
[/code]
Here is the portion of the php script that I modified:
[code]
$aColumns = array( 'date', 'phone', 'roaming', 'call_duration', 'overseas' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "phone";
/* DB table to use */
$sTable = "calls";
/* Database connection information */
$gaSql['user'] = "#########";
$gaSql['password'] = "########";
$gaSql['db'] = "#########";
$gaSql['server'] = "#######";
[/code]
I didn't modify the anything else in the php script because I'm currently testing with a basic query.
I read just about every thread in the forum here and outside sources. I tried about 20 different things. You can see I am currently using the filteringDelay plugin, Scroller, DeferRender, and disabled SortClasses.
Unfortunately I ca
Please let me know any other information you need from me.
Thanks in advance for the help.
Debug Code: http://debug.datatables.net/unalul
This discussion has been closed.
Replies
Ouch!
Is that time at the server which is being used to get the data? Might the server be running out of RAM, or the SQL / PHP limit be low enough that one of the processes is hitting its head?
Allan
It's definitely on the server side. I checked in firebug. When the page loads the php script. It never finishes.
[quote]
the SQL / PHP limit be low enough that one of the processes is hitting its head
[/quote]
If by SQL/PHP limit you mean the memory_limit entry in the php.ini, it was set to 512M. I temporarily increased it to 2000M just to be sure. That didn't seem to work. I'm using InnoDB for my table. Here is the buffer pool setting:
[code]innodb_buffer_pool_size=512M[/code]
I'm not sure where else to check.
[quote]
Might the server be running out of RAM
[/quote]
This server is a WIndows Server, and unfortunately I'm a Unix guy, so I don't know much about managing a Windows server. The only way I knew of to monitor RAM usage was by monitoring the Performance in the Task Manager. It didn't seem to be affected much when I loaded the page.
Thanks again,
Sean
Allan
Thanks,
Sean
Allan