Wdmysql memory usage

Archive Archived Weather Display Problems
21

I think the records you’re seeing now might be mine. When I was fiddling with getting MySQL logging working I had to restart the MySQL service on the server and I think that might have lost your connection with the server. I can see record arriving from you in the log, but they’re not ending up in the database. Is there any way for your code to check if the connection is still up and if not attempt to re-connect?

Also as I mentioned yesterday I tried to clear out my own records from the database table and managed to delete all the records by mistook! So there aren’t actually any Awhitu records in the database at the moment due to the service restart I mentioned above :frowning: That might be confusing your PHP code and causing it to display old data from 1st August?

You might also like to mod your PHP code to make sure it only picks up your own records, e.g.

SELECT * FROM wx_data WHERE station_id = ‘Blackpool-’ ORDER by date,time DESC LIMIT 10

22
i have made a small tweak leet me know...

Still doing it. Here’s a log of what happens from your initial connect…

040925 9:56:04 6 Connect
6 Query SHOW VARIABLES
040925 9:56:05 6 Query Select * from wx_data limit 0
040925 9:56:06 6 Query SHOW INDEX FROM wx_data
6 Query DESCRIBE wx_data ‘station_id’
040925 9:56:07 6 Query DESCRIBE wx_data ‘date’
040925 9:56:08 6 Query DESCRIBE wx_data ‘time’
6 Query DESCRIBE wx_data ‘average_windspeed’
040925 9:56:09 6 Query DESCRIBE wx_data ‘wind_direction’
040925 9:56:10 6 Query DESCRIBE wx_data ‘gust_windspeed’
6 Query DESCRIBE wx_data ‘temperature’
040925 9:56:11 6 Query DESCRIBE wx_data ‘outdoor_humidity’
040925 9:56:12 6 Query DESCRIBE wx_data ‘barometer’
6 Query DESCRIBE wx_data ‘daily_rainfall’
040925 9:56:13 6 Query DESCRIBE wx_data ‘monthly_rainfall’
040925 9:56:14 6 Query DESCRIBE wx_data ‘yearly_rainfall’
6 Query DESCRIBE wx_data ‘rain_rate’
040925 9:56:15 6 Query DESCRIBE wx_data ‘max_rain_rate_curent_day’
040925 9:56:16 6 Query DESCRIBE wx_data ‘indoor_temperature’
6 Query DESCRIBE wx_data ‘indoor_humidity’
040925 9:56:17 6 Query DESCRIBE wx_data ‘soil_temperature’
040925 9:56:18 6 Query DESCRIBE wx_data ‘forecast_icon’
6 Query DESCRIBE wx_data ‘wmr968_extra_temperature’
040925 9:56:19 6 Query DESCRIBE wx_data ‘wmr968_extra_humidity’
040925 9:56:20 6 Query DESCRIBE wx_data ‘wmr968_extra_sensor_number’
6 Query DESCRIBE wx_data ‘yesterday_rainfall’
040925 9:56:21 6 Query DESCRIBE wx_data ‘extra_temperature_sensor_2’
040925 9:56:22 6 Query DESCRIBE wx_data ‘extra_temperature_sensor_3’
6 Query DESCRIBE wx_data ‘extra_temperature_sensor_4’
040925 9:56:23 6 Query DESCRIBE wx_data ‘extra_temperature_sensor_5’
040925 9:56:24 6 Query DESCRIBE wx_data ‘extra_temperature_sensor_6’
6 Query DESCRIBE wx_data ‘extra_temperature_sensor_7’
040925 9:56:25 6 Query DESCRIBE wx_data ‘extra_humidity_2’
040925 9:56:26 6 Query DESCRIBE wx_data ‘extra_humidity_3’
6 Query DESCRIBE wx_data ‘extra_humidity_4’
040925 9:56:27 6 Query DESCRIBE wx_data ‘hour’
040925 9:56:28 6 Query DESCRIBE wx_data ‘minute’
6 Query DESCRIBE wx_data ‘second’
040925 9:56:29 6 Query DESCRIBE wx_data ‘station_name’
6 Query DESCRIBE wx_data ‘dallas_1_wire_lightning_count’
040925 9:56:30 6 Query DESCRIBE wx_data ‘actual_solar_reading’
040925 9:56:31 6 Query DESCRIBE wx_data ‘day’
6 Query DESCRIBE wx_data ‘month’
040925 9:56:32 6 Query DESCRIBE wx_data ‘wmr968_battery_level_1’
040925 9:56:33 6 Query DESCRIBE wx_data ‘wmr968_battery_level_2’
040925 9:56:34 6 Query DESCRIBE wx_data ‘wmr968_battery_level_3’
6 Query DESCRIBE wx_data ‘wmr968_battery_level_4’
040925 9:56:35 6 Query DESCRIBE wx_data ‘wmr968_battery_level_5’
040925 9:56:36 6 Query DESCRIBE wx_data ‘wmr968_battery_level_6’
6 Query DESCRIBE wx_data ‘wmr968_battery_level_7’
040925 9:56:37 6 Query DESCRIBE wx_data ‘current_windchill’
6 Query DESCRIBE wx_data ‘current_humidex’
040925 9:56:38 6 Query DESCRIBE wx_data ‘max_daily_temperature’
040925 9:56:39 6 Query DESCRIBE wx_data ‘min_daily_temperature’
6 Query DESCRIBE wx_data ‘icon_type’
040925 9:56:40 6 Query DESCRIBE wx_data ‘current_weather_desc’
040925 9:56:41 6 Query DESCRIBE wx_data ‘barometer_trend_last_hour’
6 Query DESCRIBE wx_data ‘max_gust_current_day’
040925 9:56:42 6 Query DESCRIBE wx_data ‘dew_point_temperature’
040925 9:56:43 6 Query DESCRIBE wx_data ‘cloud_height’
6 Query DESCRIBE wx_data ‘max_humidex’
040925 9:56:44 6 Query DESCRIBE wx_data ‘min_humidex’
040925 9:56:45 6 Query DESCRIBE wx_data ‘max_windchill’
6 Query DESCRIBE wx_data ‘min_windchill’
040925 9:56:46 6 Query DESCRIBE wx_data ‘davis_vp_uv’
040925 9:56:47 6 Query DESCRIBE wx_data ‘max_heat_index’
6 Query DESCRIBE wx_data ‘min_heat_index’
040925 9:56:48 6 Query DESCRIBE wx_data ‘heat_index’
040925 9:56:49 6 Query DESCRIBE wx_data ‘max_average_windspeed_day’
6 Query Select * from wx_data ORDER BY station_id, date, time
040925 9:58:03 6 Query INSERT INTO wx_data (station_id, date, time, average_windspeed, wind_direction, gust_windspeed, temperature, outdoor_humidity, barometer, daily_rainfall, monthly_rainfall, yearly_rainfall, rain_rate, max_rain_rate_curent_day, indoor_temperature, indoor_humidity, soil_temperature, forecast_icon, wmr968_extra_temperature, wmr968_extra_humidity, wmr968_extra_sensor_number, yesterday_rainfall, extra_temperature_sensor_2, extra_temperature_sensor_3, extra_temperature_sensor_4, extra_temperature_sensor_5, extra_temperature_sensor_6, extra_temperature_sensor_7, extra_humidity_2, extra_humidity_3, extra_humidity_4, hour, minute, second, station_name, dallas_1_wire_lightning_count, actual_solar_reading, day, month, wmr968_battery_level_1, wmr968_battery_level_2, wmr968_battery_level_3, wmr968_battery_level_4, wmr968_battery_level_5, wmr968_battery_level_6, wmr968_battery_level_7, current_windchill, current_humidex, max_daily_temperature, min_daily_temperature, icon_type, current_weather_desc, barometer_trend_last_hour, max_gust_current_day, dew_point_temperature, cloud_height, max_humidex, min_humidex, max_windchill, min_windchill, davis_vp_uv, max_heat_index, min_heat_index, heat_index, max_average_windspeed_day) VALUES (‘Awhitu,Gra’, ‘2004-09-25’, ‘20:57:07’, 6.4, 200, 8.7, 9.9, 93, 1008.8, 9.7, 84.1, 949.5, 0, 0.6, 22.1, 50, 12.6, 2, 0, 0, 0, 36.1, 12.6, 18.8, -129.6, 0, -100, -129.6, 63, 0, -100, 20, 56, 57, ‘Awhitu,Grahams_Beach-20:56:57’, 0, 0, 25, 9, 100, 100, 100, 100, 100, 100, 100, 8.3, 10.7, 12.3, 9.9, 4, ‘Stopped_raining\Nigh’, 0.2, 22.6, 8.9, 444.1, 13.6, 10.7, 11.3, 6.8, 0, 12.3, 9.9, 9.9, 17.6)
040925 9:58:05 6 Query Select * from wx_data ORDER BY station_id, date, time
040925 9:59:04 6 Query INSERT INTO wx_data (station_id, date, time, average_windspeed, wind_direction, gust_windspeed, temperature, outdoor_humidity, barometer, daily_rainfall, monthly_rainfall, yearly_rainfall, rain_rate, max_rain_rate_curent_day, indoor_temperature, indoor_humidity, soil_temperature, forecast_icon, wmr968_extra_temperature, wmr968_extra_humidity, wmr968_extra_sensor_number, yesterday_rainfall, extra_temperature_sensor_2, extra_temperature_sensor_3, extra_temperature_sensor_4, extra_temperature_sensor_5, extra_temperature_sensor_6, extra_temperature_sensor_7, extra_humidity_2, extra_humidity_3, extra_humidity_4, hour, minute, second, station_name, dallas_1_wire_lightning_count, actual_solar_reading, day, month, wmr968_battery_level_1, wmr968_battery_level_2, wmr968_battery_level_3, wmr968_battery_level_4, wmr968_battery_level_5, wmr968_battery_level_6, wmr968_battery_level_7, current_windchill, current_humidex, max_daily_temperature, min_daily_temperature, icon_type, current_weather_desc, barometer_trend_last_hour, max_gust_current_day, dew_point_temperature, cloud_height, max_humidex, min_humidex, max_windchill, min_windchill, davis_vp_uv, max_heat_index, min_heat_index, heat_index, max_average_windspeed_day) VALUES (‘Awhitu,Gra’, ‘2004-09-25’, ‘20:58:22’, 5.5, 196, 4.3, 9.9, 94, 1008.8, 9.7, 84.1, 949.5, 0, 0.6, 22.1, 50, 12.7, 2, 0, 0, 0, 36.1, 12.7, 18.7, -129.6, 0, -100, -129.6, 63, 0, -100, 20, 57, 58, ‘Awhitu,Grahams_Beach-20:57:58’, 0, 0, 25, 9, 100, 100, 100, 100, 100, 100, 100, 8.5, 10.8, 12.3, 9.9, 4, ‘Stopped_raining\Nigh’, 0.2, 22.6, 9, 378.9, 13.6, 10.7, 11.3, 6.8, 0, 12.3, 9.9, 9.9, 17.6)
040925 9:59:05 6 Query Select * from wx_data ORDER BY station_id, date, time

As you can see there are three SELECTs for all records in the database (in red) from your session. Maybe you might recognise something from that log?

I’m worried though…I can see your INSERT INTOs appearing in the log, but your records aren’t arriving in the database for some reason. I’ve just tried manually inserting one of your records into the database and it works OK, so I’m not sure why MySQL is logging your updates but the records don’t appear.

Gotta go do some DIY now, so won’t be back for a few hours.

23

hi
I just hooked up the table, and my data is there…
good news
i might have it fixed
i took out the POST and total Record count

24

that version 2.0 of wdmysql is now available in a new 10.17r of wd, ready now
bed time here !

25

I don’t know if you’re running v2.0, but you’re still sending SELECTs. I’m just in th process of upgrading to see what happens from here.

I’ve stopped worrying about where your data is going now. I’d forgotten that I’d set up a different database for my data, so your data is being stored and you won’t be seeing any of my data :oops:

26
i might have it fixed i took out the POST and total Record count

That’s not it. :frowning: I’m running v2.0 now and still getting SELECTs.

Hmmmmmmmmm…

I think it’s the wdmysql Query facility. I don’t fully understand how Query works, because even though I can get it to say ‘found/not found’ to my queries I can’t seem to get it to display any data. That’s not important at this stage though.

If I do a query…say ‘Filter where barometric_pressure >= 1021’, then I’ll get a found/not found response. Even though it’s not displaying any data I can see it’s searching the data and giving sensible responses. The important thing is that when you do a query wdmysql DOESN’T try to access the database on the server. There is no log entry for a SELECT matching the query and in fact the log doesn’t show any query sent to the database.

This must mean that the query component is keeping a local copy of all the records in the database! My suspicion is that the query component is watching for database updates and each time it sees one it sends a query to the server to keep its local copy up to date.

This would account for the massive memory usage seen by many people using wdmysql and also the continual SELECTs after the remote database is updated. If this is true, perhaps it’s worth dropping the query facility. There are other tools around that can do this and a lot more, e.g. MySQL Control Centre, so perhaps people should be encouraged to use something like that. You could even drop the data display table and keep wdmysql small/simple, i.e. it would just sending database updates.

I hope I’m right because it would be good to sort out this problem at last.

27

i dont do any query…its the component doing this all on its own…
just in case i didnt compile it properly, i am trying again

then, i will try this other component i have next

28

Brian,
I have had to disable wdmysql - the memory load is a big problem, but the contsant selecting of the entire table also means that mysql itself is using 80% CPU, wdmysql is using another 12% and that leaves very little for WD itself. In fact the FTP program does not seem to have enough CPU cycles and crashes a lot. Let me know if you figure out a work around. Perhaps you can contact whoever makes the control you are using.
Ian

29
i dont do any query...its the component doing this all on its own...

Does the component have any settings, e.g. something like ‘keep a local copy of all data’, that need setting a different way? If not, it would be interesting to hear the authors reason for downloading all the data as a local copy. Imagine the effect if you were trying to query a table containing 100 million records!

Does WD actually need a query function anyway? There are plenty of tools available to do MySQL queries, both client and server based, so why re-invent the wheel? I would expect most people use wdmysql to get data onto a server to process it using some other code like PHP or Perl, so I’m not sure that the query is an essential WD function.

30

just back from milking cows
i will have some breakfast, then try out the other componnet i have

31
just back from milking cows

I hope the alarm woke you in time?

32

yup!
worked great!

33

My turn to go to bed now! If you manage to get anything running with the other component let me know.

34

hi
just done a test…(added null records, just a valid time)
which wiped my database
but it did add a record!!
it was fast too!
and only 3,464 k memory used!
the component uses a dll
no borland database components used at all
this is good!

this is actualy the original component i was working with

35

good news!
i have vers 2.2 ready here:
http://www.weather-display.com/downloadfiles/wdmysql.zip

i.e unzip to where you have wd installed

virtualy no memory use or CPU use and the adding in of past data files works faster and better too!!!

you guys will be impressed!!!

a new 10.17r version of WD with this wdmysql.exe will be ready soon too

36

And the SELECT * has disappeared as well :smiley: :smiley: :smiley:

37

i think there is a problem with the date…the year and the day are getting mixed up…
fixing now

38

And the outbound bandwidth from my server has dropped off. I can see when you started to use your new code!

Is your Awhitu station still running the old code? I’ve just noticed some Select *'s starting to re-appear from that station.

39

ahhh
its a windows regional setting thing
i need to force year, month, day

40
i think there is a problem with the date.....the year and the day are getting mixed up... fixing now

Yes, I’m seeing the date as 2026-09-20. If the component thinks the date is in YY-MM-DD format, then it is probably adding 20 (to get into this millenium) to the date, e.g. 2026-09-2004 which then gets truncated to YYYY-MM-DD format.

Also…the station_id field is blank.