0

Looking for some advice. Trying to return a long query with around 65,000 results that has a lot of aggeration on the server-side. Takes around 16 seconds to return all the data. This is what I have so far. It's returning the query results but never seems to store in Memcached. If I print $cached_data, it always says false. Thank you.

<?php
header('Content-Type: application/json');
include('pdo.php');
$memcache = new Memcache();
$memcache->addServer("127.0.0.1", 11211);
$query = "SELECT TOP 5000 Snumber,Number,Name,Status,dials,Size,Y04,Y05,PD0405,Y06,PD0506,Y07,PD0607,Y08,PD0708,Y09,PD0809,Y10,PD0910,Y11,PD1011,Y12,PD1112,Y13,PD1213,Y14,PD1314,Y15,PD1415,Y16,PD1516,Y17,PD1617,Y18,PD1718,Y19,PD1819,Y20,PD1920 FROM table ORDER BY Snumber";
$key = md5($query);
$cached_data = $memcache->get($key);
$response = [];

if ($cached_data != null) {
    $result = $cached_data;
} else {

$statement = $conn->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$memcache->set($key, $result);

}
foreach ($result as $row) {
    $output[] = array(
        'Snumber' => $row['Snumber'],
        'MeterNumber' => $row['Number'], 
        'Account' => $row['Name'], 
        'Status' => $row['Status'], 
        'dialsHI' => $row['dials'], 
        'MeterSize' => $row['Size'], 
        'Y04' => $row['Y04'], 
        'Y05' => $row['Y05'], 
        'PD0405' => $row['PD0405'],
        'Y06' => $row['Y06'], 
        'PD0506' => $row['PD0506'],
        'Y07' => $row['Y07'], 
        'PD0607' => $row['PD0607'],
        'Y08' => $row['Y08'], 
        'PD0708' => $row['PD0708'],
        'Y09' => $row['Y09'], 
        'PD0809' => $row['PD0809'],
        'Y10' => $row['Y10'], 
        'PD0910' => $row['PD0910'],
        'Y11' => $row['Y11'], 
        'PD1011' => $row['PD1011'],
        'Y12' => $row['Y12'], 
        'PD1112' => $row['PD1112'],
        'Y13' => $row['Y13'], 
        'PD1213' => $row['PD1213'],
        'Y14' => $row['Y14'], 
        'PD1314' => $row['PD1314'],
        'Y15' => $row['Y15'], 
        'PD1415' => $row['PD1415'],
        'Y16' => $row['Y16'], 
        'PD1516' => $row['PD1516'],
        'Y17' => $row['Y17'], 
        'PD1617' => $row['PD1617'],
        'Y18' => $row['Y18'], 
        'PD1718' => $row['PD1718'],
        'Y19' => $row['Y19'], 
        'PD1819' => $row['PD1819'],
        'Y20' => $row['Y20'], 
        'PD1920' => $row['PD1920']);
}
echo json_encode($output, JSON_PRETTY_PRINT);
Michael Brown
  • 47
  • 1
  • 6
  • 1
    May be worth having a look at the memcache data itself, perhaps something on https://stackoverflow.com/questions/36889/memcache-control-panel may help. – Nigel Ren May 20 '21 at 19:15
  • Did some testing and it looks like I'm running into some kind of max records issues. Not sure. I reduced it down to 500 records and it work. 5000 is too much. And I need to be able to do much more. Might have to swap to something else. Not sure yet. Going to start digging in. – Michael Brown May 20 '21 at 19:54
  • 1
    There is a limit on the size of data of about 1MB per key. You could experiment to see if `MEMCACHE_COMPRESSED` helps. – Nigel Ren May 20 '21 at 19:57
  • I was able to adjust the limit in the config. https://stackoverflow.com/questions/4795536/increase-item-max-size-in-memcached/29227471 [/etc/sysconfig/memcached] PORT="11211" USER="memcached" MAXCONN="1024" CACHESIZE=4096"" OPTIONS="-I 128M" – Michael Brown May 20 '21 at 20:46

0 Answers0