Mysql Slow Select

Archived Topics
1

Hi, I have a problem when I try to get data from a mysql db, I have this table :


CREATE TABLE `events_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id_tag` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1683945 DEFAULT CHARSET=latin1;

I have multiple tags with a value changed event, when this tags change the value, insert data to my table.
In one hour I get 120.000 records, so when I try to get this data to a table component in igntion I get a query that last 10 seconds, is this normal for the for the large amount of data?

2

You don’t say what indices you’ve created, nor shown the query. If the columns used in the where clause are not indexed, the DB must read every record to pick out the ones you want.

3

Sorry I’m new with databases configuration with large amount of data, I’m still learning how they work.
I created an index for date.

CREATE INDEX date ON events_table(date);

I use this query:


select date,id_tag,value from events_table where date >= '2015-12-02 21:00:00'  AND date <= '2015-12-02 23:00:00'	
246855 row(s) returned	1.591 sec / 17.893 sec

That was just 2 hours, but my client probably want data from a week or more, what can I do with this? I was thinkin to use another DB like mssql, probably that works better. I have 2 weeks for resolve this.

4

Several things will affect the speed of this query.

On the hardware side, databases want lots of memory and fast disk access. If you’re interested, I can go into the nitty gritty detail of how this query will work, but the bottom line is give the db every scrap of memory you can. This will be true for both MySQL and MS SQLServer.

On the software side, if you have a lot of inserts, indexes can sometimes need to be rebuilt. I can’t remember if MySQL is supposed to rebuild automatically from time to time, but I know on some personal projects I’ve done a manual rebuild after inserting a quarter million records and had a drastic improvement in search times. Granted, my inserts were not in index order and yours probably are, but it’s something to try.

There are definitely differences in how well different db’s optimize queries (and it’s part of what you pay for with the big name db’s), but this query is so simple I’m not seeing how switching from MySQL to SQLServer is going to get you a huge time improvement. I’d be looking at memory and disk access times first.

1 Like
5

Working with hundreds of thousands of rows prompted the creation of the Time Series DB Cache module. It breaks large queries like this into chunks, populating the client-side dataset as the data arrives. And then hangs onto it in a cache as long as it’s referenced in an expression. Plus a few minutes grace to allow a user to scroll back and forth quickly along a timeline.

It hasn’t been tested with MySQL that I know of, but I’d be happy to iron out any wrinkles you find.

6

[quote=“pturmel”]Working with hundreds of thousands of rows prompted the creation of the Time Series DB Cache module. It breaks large queries like this into chunks, populating the client-side dataset as the data arrives. And then hangs onto it in a cache as long as it’s referenced in an expression. Plus a few minutes grace to allow a user to scroll back and forth quickly along a timeline.

It hasn’t been tested with MySQL that I know of, but I’d be happy to iron out any wrinkles you find.[/quote]

If this module solve my problem I would buy without thinking because I need this functionality.
Now I’m trying to integrate this module but I can’t understand how it’s work.

I created a new window with a table component and a button, In the manual I see this two scripting function

system.db.registerSeriesCache

system.db.getSeriesCache

Now I’m trying to figurate how use this to populate my table, the system.db.registerSeriesCache section say this:

system.db.registerSeriesCache(datasourcename, tablename, timestampcolumn, whereclause)

I have to replace this parameters?

I’m trying this:

system.db.registerSeriesCache(db, events_table, date, whereclause)

Now with system.db.getSeriesCache

system.db.getSeriesCache(handle, begints, endts, [gapTolerance,] valuecolumns[])

I have to replace valuecolumns? or I have to put my columns inside the []?

7

The script functions are offered for use in background threads and other long-running situations – you have to hold onto the ID from registerSeriesCache() to use in a loop with getSeriesCache(), then release it when you’re done.
In components, you should bind the timeSeriesCache() expression function to the table’s dataset, or to a classic chart’s dataset.
In your case, it would look something like:timeSeriesCache('myDatasource', 'events_table', 'date', '', {Root Container.Date Range.startDate}, {Root Container.Date Range.endDate}, 'id_tag', 'value')Where the time span to return comes from a Date Range component. Substitute as needed. Note that you must capitalize the column names exactly as defined in the database – the cache generates the queries with explicit quoting.

8

I do not know where to start, the manual show a list of function but there is not a step by step guide, or a video demostration.

I created a custom property in my table, and I put timeSeriesCache expresion inside this property and I don’t get any data, I think because I don’t have data in cache.

Now I’m trying to use “system.db.registerSeriesCache” to get cached data, I put it in a buton and I get the handle.

handle=system.db.registerSeriesCache('DB', 'events_table','date',' ')
event.source.parent.getComponent('Table').handle = handle

Now I don’t know if I have to use the expression “timeSeriesCache” or “system.db.getSeriesCache” in a button component.

9

[quote=“MoisesZV”]I created a custom property in my table, and I put timeSeriesCache expresion inside this property and I don’t get any data, I think because I don’t have data in cache.[/quote]Please show the expression you used (paste it in a code block, please).
You shouldn’t have needed a custom property. Put the expression on the ‘data’ property of your table. Make sure you provide all of the information the expression needs, as shown in the table here. The WHERE clause should be an empty string if you don’t need it. (Not even a space.) If you have syntax errors in your parameters, they’ll show up in the gateway console when it tries to run the query.
The expression function takes all of the arguments and takes care of registering, getting, and releasing in a background thread for you.[quote=“MoisesZV”]Now I’m trying to use “system.db.registerSeriesCache” to get cached data, I put it in a buton and I get the handle.[/quote]Please don’t. The scripting functions are for use in background threads, not in components. Let the expression function do all the work. If you can’t get the caching to work with the expression function, it isn’t going to work with the scripting functions.
{If you have strange polling requirements, the scripting might be useful later. I added the script functions to the module to allow me to run a keep-alive script for some critical data that isn’t always displayed.}

10

Thanks pturmel, now is working, but with some issues. When I query a large amount of data like a 1 million records, the table begin to show the data but in some point the CPU Usage is going to 100% then I can’t do anything, I have to force close ignition.

AMD APU A6 6400K 3.9 GHz Dual Core
8gb Ram 1333mhz

11

Caching like this uses a lot of ram, especially on the client. Make sure you’ve allowed the client and the designer to do so. It’s a trade-off between speed/bandwidth and ram.
Another possibility is that the either the chunk size (Bulk Limit) or the pace (Bulk Pace ms) is too aggressive for your hardware. You can set these on a cache-by-cache basis in the gateway configuration pages under “Time Series Caches”.

12

I’m trying with RT Pace ms 1000, Bulk Pace ms 1000 and Bulk Limit 2000, but still 100% cpu when it reaches more than 1 million records

13

Is it the gateway machine or the client machine @ 100% ? And does it recover if you leave it alone for 10-15 minutes?

14

I’m testing in the gateway machine, in the designer. Now I’m testing with RT Pace ms 1000, Bulk Pace ms 1000 and Bulk Limit 350, and the cpu is 50% and 60%.

Edit: Now it’s freeze again.

15

[quote=“MoisesZV”]I’m testing in the gateway machine, in the designer.[/quote]How much memory is each taking up? Also, how quickly do queries like the one originally given, with a limit clause of 1000, run outside of Ignition? Using MySQL Workbench or similar. The pace settings should be significantly larger than the worst case query response time for the chosen bulk limit.[quote=“MoisesZV”]Now I’m testing with RT Pace ms 1000, Bulk Pace ms 1000 and Bulk Limit 350, and the cpu is 50% and 60%.[/quote]That won’t help much. You can’t make progress querying millions of rows 350 at a time. In fact, the module ignores any Bulk limit less than 1000. Set both pace ms to 10000 for a bit to test with. It sounds like your test machine is also your database? And only has two cores & 8G ram? You might have unreasonable performance expectations for millions of rows :slight_smile:[quote=“MoisesZV”]Edit: Now it’s freeze again.[/quote]I’m curious if you can connect to ignition, the database, or the OS from another machine while it appears to be frozen. Please turn on “Debug” logging for the AbstractCache class. Then send your logs to support@automation-pros.com.

16

Although your example query only shows date in the Where clause, if you are querying based on date and id_tag then you’ll want to create an Index on date and id_tag.

But, why not just use Ignition’s Historian? You can setup partitioning in the Historian, this would make smaller tables for which to query, hence speeding up your query.