Checkpoints

[former_SQLA_member1694891]

Hi,

We are running SQLAnywhere 17 (Version 17.0.8.4003), and some of our clients are complaining about a lack of performance. We notice a lot of checkpoints in a single minutes on their database.

Here an example of the database server messages :

I. 03/06 11:27:35. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:35. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:37. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:37. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:38. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:38. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:38. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:38. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:39. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:39. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:42. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:42. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:45. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:45. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:48. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:48. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:51. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:51. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:52. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:52. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:53. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:53. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:54. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:54. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:55. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:55. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:55. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:55. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:57. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:57. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:59. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:27:59. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:27
I. 03/06 11:28:00. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:00. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:02. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:02. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:03. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:03. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:12. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:12. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:12. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:12. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:14. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:14. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:16. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:16. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:17. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:17. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:17. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:17. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:17. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:17. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:18. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:18. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:21. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:21. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:24. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:24. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:27. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:27. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:28. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:28. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:49. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:50. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:51. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:52. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:52. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:54. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:54. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:55. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:55. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:58. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:28:58. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:28
I. 03/06 11:29:00. Starting checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:29
I. 03/06 11:29:00. Finished checkpoint of "NEUTRIK" (NEUTRIK.db) at Tue Mar 06 2018 11:29

Does this can explain the lack of performance ? If it is not the reason of the lack of performance why the server is doing all this Checkpoints ? on the database we are just doing select, updates, and some times a delete.

[former_SQLA_member1694891]

Thank for your reply.

But in a normal use of our application we don't do all of this stuff (load table, ALTER TABLE, etc).

We only do (a lot) Select, update, some insert and a few delete.

[Breck_Carter]

Do you have any stored procedures or scheduled events inside your database?

Regardless of the answer, it is unlikely the checkpoints are the originating cause of any performance problems since they are not taking very long.

[former_SQLA_member1694891]

Yes we have some stored procedures and functions and scheduled events in our database.

I don't think either that the checkpoints are the cause of performance problems, but I want to know why we have some checkpoints storms with no reason :).

[Breck_Carter]

Oh, there will be a reason 🙂

Try running this query to look for possible "LOAD ..." statements (yes, I know there won't be any inside the views, but this came from a general-purpose template 🙂

BEGIN
SELECT * FROM SYSTABLE     WHERE view_def     LIKE '%LOAD %';
SELECT * FROM SYSPROCEDURE WHERE proc_defn    LIKE '%LOAD %';
SELECT * FROM SYSTRIGGER   WHERE trigger_defn LIKE '%LOAD %';
SELECT * FROM SYSEVENT     WHERE source       LIKE '%LOAD %';
END;

[Breck_Carter]

Request level logging can be used to determine what kind of SQL statements are actually coming from the client side.

--------------------------------------------------------------------- 
-- To log incoming SQL requests.

-- On
CALL sa_server_option ( 'Request_level_log_file', 'c:/temp/rlog.txt' );
CALL sa_server_option ( 'Request_level_logging', 'SQL+hostvars' );

-- Off
CALL sa_server_option ( 'Request_level_logging', 'NONE' );

Note: Yes, this old-style syntax works fine in SQL Anywhere 17.

Note: Notepad can be used to view the output file while it is still being written to... Notepad is magic 🙂

[Breck_Carter]

FWIW all this effort might not help at all with the performance problems. Try one of the products listed in Table 1 on this page.