0

I am running a couple of live and vod streams being segmented with ffmpeg. I run one channel per cloud server. I have a central, more robust server which i use for various tasks one of those task being ingesting the main m3u8 playlist and storing the segment names and creation times in a database.

The segments are created about every 8 seconds currently for 8 channels. This means that every 8 seconds i call the playlist.m3u8 (which is on a remote server) and store the information for each segment in the database.

This allows me to quickly create the vod (Select from channel 1 between time and time).

However, once or twice the database seems to have stopped working and I need to repair it before it can take new information.

I am wondering, am I hitting it too hard? Is there a better way?

My statement opens a new connection, checks if the entry already exists, if not it will enter it, then it closes the connection.

I do this once per channel per 8 seconds.

This feels like it could be better but I don't quite know how. Should I / can I keep the connection open maybe? Maybe its not a problem at all.

Anyone have any advice?

If you need more information just ask.

Query:

$conn = new mysqli($servername, $username, $password, $dbname);
$eight =  date('Y-m-d', strtotime("-8 day"));
$vq = "SELECT *  FROM `".$channels['channels'][$id]['fo_id']."`
                   WHERE `segment_name` = '".$oline."'";

$query = mysqli_query($conn, $vq);

if(mysqli_num_rows($query) == 0){
$sql = "INSERT INTO `".$channels['channels'][$id]['fo_id']."`
        (`segment_name`, `server`, `created`, `lenght`)
        VALUES
        ('".$oline."', '".$channels['channels'][$id]['server']."',
         '".date("Y-m-d H:i:s", $date)."', '".$otime."')";
$conn->query($sql);
}

$query = mysqli_query($conn, "DELETE FROM `".$channels['channels'][$id]['fo_id']."`
                           WHERE `created` < '".$eight." 00:00:00'");

$conn->close();
Rick James
  • 135,179
  • 13
  • 127
  • 222
Poopy Doop
  • 344
  • 4
  • 13
  • 4
    It doesn't sound that demanding tbh.. The whole transaction probably completes very quickly and it's only happening 8 times every 8 seconds.. It doesn't sound that intensive but am I missing something? You could try processing 4 of the 8 channels or even 1 to start with and see if that causes no issues? That then gives you a clue that maybe it can't cope with 8 channels at once. – EM-Creations Nov 21 '18 at 14:42
  • What does "seems to have stopped working" mean and what type of repair do you have to do? As EM-Creations said this sounds like a small amount of work. Add the code in question and the DDL for the database which might help us provide more information/clues. – Dave Nov 21 '18 at 14:46
  • Well Dave what i mean is that that table just stops taking data. It returns successful but it has not actually been inserted. I did not include the code as its not really necessary as we are talking about best practice and i described the exact process above.The repair i run is the mysql repair table as an Operation – Poopy Doop Nov 21 '18 at 14:51
  • @EM-Creations I totally agree with you that it is not that big a task. I might try doing all the channels at once. The worrying part for me is the final system may run 50+ channels and i am trying to avoid serious problems early on – Poopy Doop Nov 21 '18 at 14:54
  • 1
    What about the repair part? What do you have to repair? Your code would help us understand exactly what you are doing. Without it we're guessing. – Dave Nov 21 '18 at 14:55
  • As you can see Dave its just a standard insert – Poopy Doop Nov 21 '18 at 15:12
  • 2
    I would strongly recommend to use Redis if you need these records saved temporary all the time. It seem that you only use it as temp storage. **insert - time to live - delete** that is exactly what is Redis or even Memcached are good for. – Alex Nov 21 '18 at 15:13
  • Thank you Alex i will look into these two as I am unfamiliar. – Poopy Doop Nov 21 '18 at 15:14
  • Please `echo` those 3 statements so we can see what the arguments look like and whether there are subtle syntax errors. – Rick James Nov 21 '18 at 22:51
  • Instead of WHERE `created` < '".$eight." 00:00:00'", you could do `WHERE created < CURDATE() - INTERAL 8 DAY`. – Rick James Nov 21 '18 at 22:52
  • With a suitable unique key, you could do `INSERT IGNORE` instead of `SELECT` plus `INSERT`. – Rick James Nov 21 '18 at 22:53
  • Check for errors after each query -- this may give you the answer you seek. – Rick James Nov 21 '18 at 22:54
  • 1
    _"It returns successful but it has not actually been inserted"_ that's what happens when you don't check for errors. See [How to get MySQLi error information in different environments](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments) – Phil Nov 21 '18 at 23:04

0 Answers0