Skip to content

Instantly share code, notes, and snippets.

@PimDeWitte

PimDeWitte/gist:d342cc9518293eab8600

Last active August 29, 2015 14:09
Show Gist options
  • Save PimDeWitte/d342cc9518293eab8600 to your computer and use it in GitHub Desktop.
Save PimDeWitte/d342cc9518293eab8600 to your computer and use it in GitHub Desktop.
Download ZIP
OpenMRS Database Size Analysis
Raw
gistfile1.sql
Considering that we are using OpenMRS, an unencrypted, uncompressed MySQL database with the
data entered below is 215MB.The data consists of 5500 patients/persons, 500.000 observations
in 15.000 encounters, and 2500 concepts, and 3 forms.
Total memory is 137MB without any i/o.
On disk:
root@jenkins-3:/var/lib/mysql# ls -l
total 249880
-rw-r--r-- 1 root root 0 Nov 11 14:13 debian-5.5.flag
-rw-rw---- 1 mysql mysql 245366784 Nov 13 12:59 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Nov 13 12:59 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Nov 13 12:59 ib_logfile1
drwx------ 2 mysql root 4096 Nov 11 14:14 mysql
-rw------- 1 root root 6 Nov 11 14:14 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 Nov 13 12:59 openmrs
drwx------ 2 mysql mysql 4096 Nov 12 16:00 openmrs2
drwx------ 2 mysql mysql 4096 Nov 11 14:14 performance_schema
mysql> SELECT table_name AS "Tables",
-> round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
-> , table_rows,
-> engine
-> FROM information_schema.TABLES
-> WHERE table_schema = "openmrs"
-> ORDER BY (data_length + index_length) DESC;
+--------------------------------+------------+------------+--------+
| Tables | Size in MB | table_rows | engine |
+--------------------------------+------------+------------+--------+
| obs | 181.91 | 431405 | InnoDB |
| encounter | 6.17 | 14471 | InnoDB |
| encounter_provider | 4.25 | 14444 | InnoDB |
| person_name | 2.56 | 5407 | InnoDB |
| patient_identifier | 2.48 | 5461 | InnoDB |
| person_attribute | 2.23 | 4938 | InnoDB |
| person_address | 2.16 | 5499 | InnoDB |
| person | 1.31 | 5461 | InnoDB |
| concept_word | 1.09 | 7638 | InnoDB |
| concept_name | 1.05 | 3121 | InnoDB |
| concept | 0.72 | 2385 | InnoDB |
| concept_description | 0.70 | 2519 | InnoDB |
| patient | 0.63 | 5326 | InnoDB |
| form | 0.42 | 3 | InnoDB |
| concept_answer | 0.25 | 862 | InnoDB |
| form_field | 0.20 | 400 | InnoDB |
| field | 0.17 | 183 | InnoDB |
| orders | 0.17 | 0 | InnoDB |
| liquibasechangelog | 0.16 | 555 | InnoDB |
| patient_program | 0.14 | 0 | InnoDB |
| visit | 0.14 | 0 | InnoDB |
| person_attribute_type | 0.14 | 7 | InnoDB |
| note | 0.13 | 0 | InnoDB |
| active_list | 0.13 | 0 | InnoDB |
| relationship | 0.13 | 0 | InnoDB |
| location | 0.13 | 19 | InnoDB |
| drug_reference_map | 0.13 | 0 | InnoDB |
| order_type | 0.13 | 2 | InnoDB |
| drug | 0.13 | 6 | InnoDB |
| concept_proposal | 0.13 | 0 | InnoDB |
| patient_state | 0.11 | 0 | InnoDB |
| concept_set | 0.11 | 169 | InnoDB |
| provider_attribute | 0.11 | 0 | InnoDB |
| location_attribute | 0.11 | 0 | InnoDB |
| concept_reference_term_map | 0.11 | 0 | InnoDB |
| concept_reference_term | 0.11 | 0 | InnoDB |
| drug_order | 0.11 | 0 | InnoDB |
| concept_reference_map | 0.11 | 0 | InnoDB |
| person_merge_log | 0.11 | 0 | InnoDB |
| visit_attribute | 0.11 | 0 | InnoDB |
| concept_name_tag | 0.11 | 0 | InnoDB |
| concept_map_type | 0.09 | 70 | InnoDB |
| care_setting | 0.09 | 2 | InnoDB |
| concept_state_conversion | 0.09 | 0 | InnoDB |
| provider | 0.09 | 1 | InnoDB |
| program_workflow_state | 0.09 | 0 | InnoDB |
| concept_reference_source | 0.09 | 0 | InnoDB |
| program_workflow | 0.09 | 0 | InnoDB |
| program | 0.09 | 0 | InnoDB |
| order_frequency | 0.09 | 0 | InnoDB |
| notification_alert | 0.08 | 0 | InnoDB |
| field_answer | 0.08 | 0 | InnoDB |
| location_tag | 0.08 | 0 | InnoDB |
| encounter_type | 0.08 | 4 | InnoDB |
| provider_attribute_type | 0.08 | 0 | InnoDB |
| location_attribute_type | 0.08 | 0 | InnoDB |
| encounter_role | 0.08 | 1 | InnoDB |
| patient_identifier_type | 0.08 | 2 | InnoDB |
| report_object | 0.08 | 0 | InnoDB |
| concept_datatype | 0.08 | 12 | InnoDB |
| concept_class | 0.08 | 16 | InnoDB |
| users | 0.08 | 2 | InnoDB |
| visit_type | 0.08 | 0 | InnoDB |
| visit_attribute_type | 0.08 | 0 | InnoDB |
| global_property | 0.08 | 140 | InnoDB |
| cohort | 0.08 | 0 | InnoDB |
| serialized_object | 0.08 | 0 | InnoDB |
| scheduler_task_config | 0.06 | 7 | InnoDB |
| relationship_type | 0.06 | 4 | InnoDB |
| privilege | 0.06 | 139 | InnoDB |
| field_type | 0.05 | 5 | InnoDB |
| active_list_type | 0.05 | 2 | InnoDB |
| hl7_source | 0.05 | 1 | InnoDB |
| concept_proposal_tag_map | 0.05 | 0 | InnoDB |
| hl7_in_queue | 0.05 | 0 | InnoDB |
| concept_numeric | 0.05 | 569 | InnoDB |
| cohort_member | 0.05 | 0 | InnoDB |
| hl7_in_archive | 0.05 | 0 | InnoDB |
| concept_stop_word | 0.05 | 10 | InnoDB |
| concept_name_tag_map | 0.05 | 0 | InnoDB |
| test_order | 0.05 | 0 | InnoDB |
| form_resource | 0.05 | 0 | InnoDB |
| clob_datatype_storage | 0.05 | 0 | InnoDB |
| notification_alert_recipient | 0.05 | 0 | InnoDB |
| scheduler_task_config_property | 0.03 | 0 | InnoDB |
| role_role | 0.03 | 0 | InnoDB |
| location_tag_map | 0.03 | 0 | InnoDB |
| active_list_allergy | 0.03 | 0 | InnoDB |
| role_privilege | 0.03 | 13 | InnoDB |
| role | 0.03 | 4 | InnoDB |
| report_schema_xml | 0.03 | 0 | InnoDB |
| drug_ingredient | 0.03 | 0 | InnoDB |
| order_type_class_map | 0.03 | 0 | InnoDB |
| user_role | 0.03 | 2 | InnoDB |
| hl7_in_error | 0.03 | 0 | InnoDB |
| notification_template | 0.03 | 0 | InnoDB |
| active_list_problem | 0.02 | 0 | InnoDB |
| concept_set_derived | 0.02 | 231 | InnoDB |
| liquibasechangeloglock | 0.02 | 1 | InnoDB |
| concept_complex | 0.02 | 0 | InnoDB |
| user_property | 0.02 | 1 | InnoDB |
+--------------------------------+------------+------------+--------+
101 rows in set (0.04 sec)
@PimDeWitte
Copy link
Author

Also this is the memory consumption without any i/o

Total memory is 137.363MB

#141113 16:17:48 INNODB MONITOR OUTPUT

Per second averages calculated from the last 47 seconds

BACKGROUND THREAD

srv_master_thread loops: 293 1_second, 293 sleeps, 28 10_second, 32 background, 32 flush

srv_master_thread log flush and writes: 302

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 263, signal count 262
Mutex spin waits 43, rounds 1290, OS waits 39
RW-shared spins 113, rounds 3390, OS waits 112
RW-excl spins 0, rounds 3360, OS waits 112

Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 3360.00 RW-excl

TRANSACTIONS

Trx id counter 1267C
Purge done for trx's n:o < 124FF undo n:o < 0
History list length 1105
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 72, OS thread handle 0x7f4246320700, query id 216647 localhost root

SHOW ENGINE INNODB STATUS

FILE I/O

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3718 OS file reads, 64889 OS file writes, 28887 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 3, seg size 5, 24 merges
merged operations:
insert 876, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 11 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

LOG

Log sequence number 792067165
Log flushed up to 792067165
Last checkpoint at 792067165
0 pending log writes, 0 pending chkp writes

27715 log i/o's done, 0.00 log i/o's/second

BUFFER POOL AND MEMORY

Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 1438788
Buffer pool size 8191
Free buffers 0
Database pages 8180
Old database pages 2999
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2523, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3472, created 11535, written 36187
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8180, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2578, id 139922277172992, state: waiting for server activity
Number of rows inserted 613520, updated 12528, deleted 10120, read 162639

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

END OF INNODB MONITOR OUTPUT

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment