Partition Table in STARS: Concept and Evaluations

Submission Dates:
11/05/2022 to 12/31/2022
Citation Author(s):
Penidas Fiodinggo
Tanaem
Universitas Kristen Satya Wacana
Submitted by:
Penidas Tanaem
Last updated:
Sat, 11/05/2022 - 08:08
DOI:
10.21227/72az-6381
Data Format:
*.zip
Links:
Partition Table in STARS: Concept and Evaluations
Dataport
License:
Creative Commons Attribution
164 Views
Categories:
Other
Keywords:
database, partition tables, postgreSql
 
ACCESS DATASET CITE SHARE/EMBED

Abstract 

Database performance is one of the main components in supporting the sustainability of a system in this case is STARS. One way to improve the performance of the database is to use the concept of a partition table. Thus, in this study, the design and evaluation of the partition table will be carried out which will then be applied to the SWCU STARS database. The results of this study indicate that partition tables have better performance than non-partitioned tables seen from several SQL syntaxes, like update, delete and select. Meanwhile, insert performs poorly for partitioned tables compared to non-partitioned tables.

Instructions: 

Note: tools needed for testing include: apache2, postgresql 14, and apache jmeter

Intro

  1. please extract the project file, then extract it to the apache 2 folder (/var/www/). the extract results in the form of a folder named into.
  2. create database in postgres namely "coba".
  3. config "into/application/config/database.php" file for database connection
  4. install pg_stat_statements in postgresql and useing default configuration. (https://www.postgresql.org/docs/current/pgstatstatements.html)
  5. enable AUTOVACCUM in /etc/postgresql/14/main/postgresql.conf file. (https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-...)
  6. set "shared_buffers = 512MB" inside /etc/postgresql/14/main/postgresql.conf file
  7. runing query "CREATE EXTENSION pg_stat_statements;" inside the database coba.
  8. runing query "alter database coba set track_io_timing = 1;" inside the database coba.
  9. restart postgresl server
  10. using browser to call link "localhost/into/welcome/createDataSet/". this step can help us to setup table inside the database.
  11. run apache jmeter, load and run "into/analisis/Thread Group Setup.jmx" file
  12. runing query "SELECT pg_stat_statements_reset();" inside the database coba to clear other statistic data
  13. run apache jmeter, load and run "into/analisis/jemeter insert non.jmx" file
  14. run apache jmeter, load and run "into/analisis/jemeter insert.jmx" file
  15. run apache jmeter, load and run "into/analisis/jemeter update non.jmx" file
  16. run apache jmeter, load and run "into/analisis/jemeter update.jmx" file
  17. run apache jmeter, load and run "into/analisis/jemeter delete non.jmx" file
  18. run apache jmeter, load and run "into/analisis/jemeter delete.jmx" file
  19. run apache jmeter, load and run "into/analisis/jemeter select non.jmx" file
  20. run apache jmeter, load and run "into/analisis/jemeter select.jmx" file
  21. finally, runing query "

SELECT query, calls, round(total_exec_time::numeric, 5) AS total_time, rows, 

100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,

round(mean_exec_time::numeric, 5) AS mean,

round(stddev_exec_time::numeric, 5) AS stddev,

round(min_exec_time::numeric, 5) AS min,

round(max_exec_time::numeric, 5) AS max,

round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 5) AS percentage_cpu

FROM pg_stat_statements

where query ilike '%participant%' and (query ilike '%select%' or query ilike '%insert%' or query ilike '%update%' or query ilike '%delete%')

order by query desc; " inside the database coba to clear other statistic data. this process can show result for sql sintaxs "insert, uodate, delete, and select" for table partition and table no partition

 

Dataset Files

You must be an approved participant in this data competition to access dataset files. To request access you must first login.

Login

QUESTIONS?

Send Author a Private Message

Report a problem with this Dataset