Why do you create a new topic? Normally topics have a long life span. It would be possible for remote devices to update the database but then it becomes a management and security issue and doesn't resolve the bottleneck problem, it just moves it elsewhere, but limits your options to address it. The whole point of a publish and subscribe approach is to create boundaries as well as manage throughput. I did, however, suggest earlier that one Pi doing everything might be a bottleneck. I'd suggest stop creating new topics as that's an overhead you don't need. Another Pi, just as a listener to a topic, could be used solely to do database updates. If that proves a bottleneck you can have multiple listeners to a topic until the database itself becomes the bottleneck.

 

I am setting a new topic to update different parts of databases as there are many dynamic parts of databases that change very often. I save a current_operation data , completed_operations and more. Current_operation data is the most dynamic one, I am saving real time data there about what is currently happening with certain devices/items and boxes. I have initially tried to create a few topics but I soon realized that when I try to save different pieces of data to a single topic it gets very messy and hard to keep track. When you create a topic with a unique name and save data that matches the topic name, its much easier to follow and keep track whats going on. For example, I have a topic "available_items", which only handles the information about how many items are left in the box, then i have topic "items_left_to_pick" which handles the data about how many are left pick up from the box for current operation, I also have "current_operation_items" topic which just holds the information about how many items in total are needed to pick from the box. All this data seems very simmilar but its a little bit different therefore I have split it in multiple tasks.

So you suggesting I take a new raspberry PI, give it access to MYSQL, and handle all the database operations on that raspberry PI. On my main raspberry PI, i just send MQTT commands but dont do anything regarding the databases

 

I am setting a new topic to update different parts of databases as there are many dynamic parts of databases that change very often

I'm sure it makes sense to you, but are you saying you create new database structures on the fly? How often is often? That doesn't sound particularly efficient.

Are you saying that if you have a box of apples and a box of oranges you'd have a topic "available_apples_in_box_1" and another "available_oranges_in_box_2"?

Rather than a system-wide "available_items" topic and a message structure such as:

struct available_items{
  int boxNumber;
  int oldCount;
  int newCount;
  string itemName;
  etc.
  etc.
}

to pass action information about how the number of items has changed?

Or even topics like "itemCountAction" and "itemCountResult" and a message structure something like

struct countChangeMsg{
  int boxNumber;
  int itemCount;
  enum action {add, remove, info};
  int changeAmount;
  string itemName;
  etc.
  etc.
}

which is both an action request and a confirmation depending on direction/topic?

 

I'm sure it makes sense to you, but are you saying you create new database structures on the fly? How often is often? That doesn't sound particularly efficient.

Are you saying that if you have a box of apples and a box of oranges you'd have a topic "available_apples_in_box_1" and another "available_oranges_in_box_2"?

Rather than a system-wide "available_items" topic and a message structure such as:

struct available_items{
  int boxNumber;
  int oldCount;
  int newCount;
  string itemName;
  etc.
  etc.
}

to pass action information about how the number of items has changed?

Or even topics like "itemCountAction" and "itemCountResult" and a message structure something like

struct countChangeMsg{
  int boxNumber;
  int itemCount;
  enum action {add, remove, info};
  int changeAmount;
  string itemName;
  etc.
  etc.
}

which is both an action request and a confirmation depending on direction/topic?

I am creating a new database every time an operator starts a new tak which is lets say every few minutes. Actually, im not creating it, but clearing an old one and update it with whole new set of values.

With your example with apples and oranges - that is exactly how my system is designed right now.

I probably should spend some more time optimizing my topics ... since most of the time my database gets updated from the mqqt message, it seemed quite hard at a time to build an object or array of data and passing it all at once and then parsing it and update the database where I need. Maybe thats what i should di

 

You need to create objects or classes to encapsulate message structures, their construction and data extraction. This may seem unnecessary but trust me it'll make your life so much easier...

 

UPDATE:

I want to thank everyone once again for giving advising. I have finished developing a working prototype now I am waiting for PCB's and components. Everything will be enclosed together and ready to be used in our factory (apart from few software tweaks that I will need to do once I fully test the assembled version).


I am using python on my raspberry PI which controls the whole operation and sends the messages to the remote devices.

I will upload some pictures once I get the PCB's!


The only thing that is left is to make some sort of user interface that would guide an operator and tell him what to do. The instructions need to be clear and simple and there wont be many of them..

I just need to tell an operator to scan an item code when needed, sometimes an operator will need to scan an item twice ( when replacing an inside a component bin). Also, when operator is filling the boxes, he will manually require to input a quantity of items that he is putting inside a component bin. I have tried few python libraries that are designed for user interface but havent managed to get anything to work out of them yet! Ideally I would do my user interface on python as well since everything is running on my python so might as well do it all the way.