{"id":1669,"date":"2024-06-27T12:50:45","date_gmt":"2024-06-27T12:50:45","guid":{"rendered":"https:\/\/www.hammerdb.com\/blog\/?p=1669"},"modified":"2024-06-27T13:25:25","modified_gmt":"2024-06-27T13:25:25","slug":"hammerdb-v4-11-new-features-cpu-metrics-for-cli-gui-and-jobs","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-v4-11-new-features-cpu-metrics-for-cli-gui-and-jobs\/","title":{"rendered":"HammerDB v4.11 New Features: CPU Metrics for CLI, GUI and Jobs"},"content":{"rendered":"

\"\"<\/a>One of the key metrics for measuring database performance is CPU utilisation. Therefore since version 2.16 HammerDB has included a CPU monitor in the GUI to graphically observe this utilisation during a workload.<\/p>\n

At HammerDB v4.11 the CPU metrics functionality has been enhanced to add the CPU data to the CLI as well as recording this CPU data for HammerDB jobs to view with the HammerDB web service. This post explains the changes to the CPU metrics in HammerDB v4.11 and how to use them.<\/p>\n

Capturing CPU utilisation data<\/h2>\n

HammerDB can record a number of metrics for a workload (such as transaction count and response times) that are stored in the SQLite databases repository for viewing with the web service. HammerDB v4.11 adds the same functionality for the CPU metrics as well as adding a number of commands to the CLI so that the CPU data can be recorded for both.<\/p>\n

It is important to note however the distinction between data such as transaction count and CPU utilisation in that the former can be retrieved from the target database with SQL, however the latter requires additional data gathering at the system level. To do this HammerDB uses a separate agent process to gather the CPU utilisation and send it to HammerDB.\u00a0 If HammerDB is running on a separate system to the database being tested, then the agent needs to run on the system where the database is running.<\/p>\n

On Linux the HammerDB agent uses the mpstat utility from the sysstat package and therefore this package must be installed on the system being tested.<\/p>\n

$ mpstat -V\r\nsysstat version 11.5.7\r\n(C) Sebastien Godard (sysstat <at> orange.fr<\/pre>\n
On windows the mpstat utility is provided by HammerDB instead and is installed in the agent directory.\u00a0 This uses the Windows PDH functions to consume the counter data.<\/p>\n
\"\"<\/a><\/p>\n
If HammerDB is installed on the same system as the database then the agent can be run directly from the HammerDB install. If the database system is separate and HammerDB is connecting over the network then HammerDB should be installed on the database server for running the agent.<\/p>\n
Note that you may have already installed HammerDB on both systems as running a schema build locally on the same system will typically be much faster then running a build over the network.<\/p>\n
Starting the agent manually<\/h2>\n
Note that if you are running HammerDB on the same host as the database then you can skip to starting the agent automatically.\u00a0 However the choice is yours, even if the host is the same then you can still follow the manual process of starting the agent and then connecting display that you have to take when the systems are separate.<\/p>\n
The agent can be started manually by running .\/agent on Linux or agent.bat on Windows.<\/p>\n
\"\"<\/a><\/p>\n
When run without arguments the agent will randomly select a port and report the port and hostname to connect to.<\/p>\n
\"\"<\/a><\/p>\n
Alternatively, if you provide an argument of a port number to the agent<\/p>\n
C:\\Program Files\\HammerDB-4.11\\agent>agent.bat 10000<\/pre>\n
Then the agent will start with this specified port number<\/p>\n
\"\"<\/a><\/p>\n
The agent is now ready to be connected to with a display.\u00a0 When connected the agent will run mpstat and gather and send CPU data to the display.<\/p>\n
GUI Metrics interface<\/h2>\n
Under the GUI tree view you can see Metrics Options and Display. Options provides the Display with the configuration of the agent to connect to and Display starts the GUI connecting to the Display.<\/p>\n
\"\"<\/a><\/p>\n
Under the metrics agent options enter the Agent ID that was either randomly chosen or specified when the agent was started and the hostname it is running on, either local or remote.<\/p>\n
\"\"<\/a><\/p>\n
If the hostname is the localhost, then you can verify if the agent is running with the Agent Status button. As we already started the agent manually on port 10000 then we can see that HammerDB reports that the agent is already running.<\/p>\n
\"\"<\/a><\/h2>\n
Note that you can also see that we have the options to start and stop the agent from the GUI when we are running both the agent and the GUI on the same host.\u00a0 This starts and stops the agent in exactly the same way as described when running manually with providing the port number that you have specified under Agent ID.<\/p>\n
With the agent running either locally or remotely, click Display in the tree view, the HammerDB Display will now connect to the tree view and begin showing the CPU metrics.<\/p>\n
\"\"<\/a><\/p>\n
Note that the agent will also report that a Display has connected.<\/p>\n
\"\"<\/a><\/p>\n
Also the metrics window can be dragged out of HammerDB to display standalone, or if closed will return to the HammerDB notebook.<\/p>\n
\"\"<\/a><\/p>\n
Finally, if you select the Start Display with Local Agent checkbox, if this is selected and you click Agent Start, then both the Agent and Display will start running on the localhost in a single step.<\/p>\n
\"\"<\/a><\/p>\n
CLI Metrics interface<\/h2>\n
The CLI metrics interface follows the same steps as the GUI. Firstly use the print generic command to observer the metrics settings.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
hammerdb>print generic\r\nGeneric Dictionary Settings\r\n...\r\nmetrics              {\r\n agent_hostname = localhost\r\n agent_id       = 10000\r\n}\r\n...\r\n<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
The metset command can be used to change the agent_hostname or agent_id.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
hammerdb>metset\r\nUsage: metset [agent_hostname|agent_id] value\r\n<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
The metstart command is used to start the CLI metrics display.\u00a0 If the agent host is the localhost and the agent is not already running then metstart will also start the agent.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
hammerdb>metstart\r\nStarting Local Metrics Agent on ubuntu\r\nafter#1\r\nhammerdb>Connecting to Agent to Display CPU Metrics\r\nMetric receive port open @ 37065 on ubuntu\r\nConnecting to HammerDB Agent @ localhost:10000\r\nTesting Agent Connectivity...OK\r\nMetrics Connected\r\nStarted CPU Metrics for Intel(R) Xeon(R) Platinum 8280L CPU @ 2.70GHz:(112 CPUs)\r\nCPU all usr%-0.06 sys%-0.04 irq%-0.00 idle%-99.87\r\nCPU all usr%-0.08 sys%-0.04 irq%-0.00 idle%-99.85\r\nCPU all usr%-0.06 sys%-0.03 irq%-0.00 idle%-99.90\r\nCPU all usr%-0.04 sys%-0.03 irq%-0.00 idle%-99.91\r\n...\r\n<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
metstatus checks whether the agent is running. Note that because the agent is a remote process then there may be some delay between starting the metrics and the status returning as running.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
hammerdb>metstatus\r\nCPU Metrics are running on ubuntu\r\nMetrics Agent running on localhost:10000\r\n<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Finally metstop stops the display and agent.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
hammerdb>metstop\r\nStopping Metrics Agent and Display on localhost:10000\r\n<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Typically these commands will be included in a script as follows:<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
set tmpdir $::env(TMP)\r\nputs \"SETTING CONFIGURATION\"\r\njobs profileid 0\r\ndbset db maria\r\ndbset bm TPC-C\r\ngiset timeprofile xt_gather_timeout 1200\r\ngiset commandline keepalive_margin 1200\r\n\r\ndiset connection maria_host 127.0.0.1\r\ndiset connection maria_port 3306\r\ndiset connection maria_socket \/tmp\/mariadb.sock\r\n#\r\ndiset tpcc maria_user root\r\ndiset tpcc maria_pass maria\r\ndiset tpcc maria_dbase tpcc\r\ndiset tpcc maria_driver timed\r\ndiset tpcc maria_rampup 2\r\ndiset tpcc maria_duration 5\r\ndiset tpcc maria_no_stored_procs false\r\ndiset tpcc maria_allwarehouse false\r\ndiset tpcc maria_timeprofile true\r\ndiset tpcc maria_purge true\r\n#start CPU\r\nmetstart\r\nputs \"TEST STARTED\"\r\nloadscript\r\nvuset vu 80\r\nvuset logtotemp 1\r\nvucreate\r\ntcstart\r\ntcstatus\r\nvurun\r\ntcstop\r\nvudestroy\r\n#stop CPU\r\nmetstop\r\nputs \"TEST COMPLETE\"<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
and when the script is run you can observe that the CPU utilization is reported. Note that the difference from the GUI is that the CLI reports utilization for all CPUs whereas in the GUI you can observe the individual cores.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
 .\/hammerdbcli auto .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc_run_80vu.tcl\r\n...\r\nVuser 1:Rampup 1 minutes complete ...\r\n1616094 MariaDB tpm\r\nCPU all usr%-56.20 sys%-7.32 irq%-0.00 idle%-36.39\r\n1621218 MariaDB tpm\r\nCPU all usr%-56.71 sys%-7.29 irq%-0.00 idle%-35.94\r\n1605342 MariaDB tpm\r\nCPU all usr%-56.55 sys%-7.18 irq%-0.00 idle%-36.19\r\n1614732 MariaDB tpm\r\nCPU all usr%-56.67 sys%-7.34 irq%-0.00 idle%-35.94\r\n...\r\nVuser 28:FINISHED SUCCESS\r\nVuser 29:FINISHED SUCCESS\r\nVuser 1:Calculating timings...\r\nVuser 1:Writing timing data to \/home\/HammerDB-4.11\/TMP\/hdbxtprofile.log\r\nCPU all usr%-4.81 sys%-0.33 irq%-0.00 idle%-89.32\r\n111918 MariaDB tpm\r\nCPU all usr%-2.82 sys%-0.17 irq%-0.00 idle%-96.98\r\n0 MariaDB tpm\r\nCPU all usr%-2.73 sys%-0.13 irq%-0.00 idle%-97.11\r\n0 MariaDB tpm\r\nCPU all usr%-2.84 sys%-0.11 irq%-0.00 idle%-97.03\r\n0 MariaDB tpm\r\nVuser 1:Starting purge: history list length 3044852\r\nCPU all usr%-2.67 sys%-0.08 irq%-0.00 idle%-97.23\r\n0 MariaDB tpm\r\nCPU all usr%-2.83 sys%-0.08 irq%-0.00 idle%-97.05\r\n0 MariaDB tpm\r\nCPU all usr%-2.61 sys%-0.09 irq%-0.00 idle%-97.21\r\n0 MariaDB tpm\r\nCPU all usr%-2.87 sys%-0.08 irq%-0.00 idle%-96.99\r\n0 MariaDB tpm\r\nCPU all usr%-3.00 sys%-0.07 irq%-0.00 idle%-96.88\r\n0 MariaDB tpm\r\nCPU all usr%-3.03 sys%-0.07 irq%-0.00 idle%-96.82\r\n0 MariaDB tpm\r\nCPU all usr%-2.94 sys%-0.11 irq%-0.00 idle%-96.85\r\n0 MariaDB tpm\r\nVuser 1:Purge complete in 0 hrs:01 mins:11 secs\r\nVuser 1:Starting write back: dirty buffer pages 1161320\r\nCPU all usr%-0.77 sys%-0.25 irq%-0.00 idle%-98.27\r\n0 MariaDB tpm\r\nVuser 1:Write back complete in 0 hrs:00 mins:14 secs\r\nVuser 1:FINISHED SUCCESS\r\nALL VIRTUAL USERS COMPLETE\r\nTransaction Counter thread running with threadid:tid0x7f83cd7fa700\r\nStopping Transaction Counter\r\nvudestroy success\r\nStopping Metrics Agent and Display on localhost:10000\r\nTEST COMPLETE<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
When the job has finished the new job system command will record the name of the CPU and the number of cores.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
hammerdb>job 667D3F1561BD03E203533353 system\r\n[\r\n  \"Intel(R) Xeon(R) Platinum 8280L CPU @ 2.70GHz\",\r\n  \"112\"\r\n]<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
and the job metrics command will show the CPU utilization data for a particular job.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
hammerdb>job 667D3F1561BD03E203533353 metrics\r\n{\r\n  \"2024-06-27 03:29:51\": {\r\n    \"usr%\": \"6.21\",\r\n    \"sys%\": \"0.46\",\r\n    \"irq%\": \"0.0\",\r\n    \"idle%\": \"90.63\"\r\n  },\r\n  \"2024-06-27 03:30:01\": {\r\n    \"usr%\": \"24.55\",\r\n    \"sys%\": \"1.45\",\r\n    \"irq%\": \"0.0\",\r\n    \"idle%\": \"72.86\"\r\n  },\r\n  \"2024-06-27 03:30:11\": {\r\n    \"usr%\": \"39.02\",\r\n    \"sys%\": \"2.81\",\r\n    \"irq%\": \"0.0\",\r\n    \"idle%\": \"57.01\"\r\n  },\r\n  \"2024-06-27 03:30:21\": {\r\n    \"usr%\": \"50.87\",\r\n    \"sys%\": \"5.16\",\r\n    \"irq%\": \"0.0\",\r\n    \"idle%\": \"42.97\"\r\n  },\r\n...\r\n}<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Viewing Metrics with the Web Service<\/h2>\n
When you run a job with the CPU metrics running, either in the GUI or in the CLI the job will record the CPU that the workload is running on and the metrics data. Note that if the metrics is not running then the metrics and system links will be missing for a particular job.<\/p>\n
\"\"<\/a><\/h2>\n
In the example we can see that the metrics was running for this job and the system and metrics links are present.<\/p>\n
\"\"<\/a><\/h2>\n
The system link reports the CPU that the job was run against.<\/p>\n
\"\"<\/a><\/h2>\n
and the metrics link shows a graph for user% and sys% across the time the job was run.<\/p>\n
\"\"<\/a><\/h2>\n
Summary<\/h2>\n
HammerDB v4.11 adds functionality to improve capturing CPU utilisation during a job and storing this data for displaying along with other job related data. It also adds the metset, metstart, metstop and metstatus commands to run and capture CPU utilisation from the CLI interface.<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the key metrics for measuring database performance is CPU utilisation. Therefore since version 2.16 HammerDB has included a CPU monitor in the GUI to graphically observe this utilisation during a workload. At HammerDB v4.11 the CPU metrics functionality has been enhanced to add the CPU data to the CLI as well as recording … <\/p>\n
Continue reading “HammerDB v4.11 New Features: CPU Metrics for CLI, GUI and Jobs”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"ppma_author":[5],"class_list":["post-1669","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"authors":[{"term_id":5,"user_id":2,"is_guest":0,"slug":"hammerdb","display_name":"HammerDB","avatar_url":{"url":"https:\/\/www.hammerdb.com\/blog\/wp-content\/uploads\/2018\/10\/logo-white.png","url2x":"https:\/\/www.hammerdb.com\/blog\/wp-content\/uploads\/2018\/10\/logo-white.png"},"author_category":"","user_url":"http:\/\/www.hammerdb.com","last_name":"","first_name":"","job_title":"","description":""}],"_links":{"self":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1669","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/comments?post=1669"}],"version-history":[{"count":11,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1669\/revisions"}],"predecessor-version":[{"id":1708,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1669\/revisions\/1708"}],"wp:attachment":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/media?parent=1669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/categories?post=1669"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/tags?post=1669"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=1669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}