jorainer
commented
2 years ago CCLE data set
A MsqlBackend compliant database (MariaDB) was created for the full data which consists of 504 mzML files with in total 42,592,313 spectra (MS1 and MS2). The total sum of mass peaks for all these spectra is 19,381,366,123. A second MariaDB database was created with the option blob = TRUE introduced in PR #4 . In contrast to the former, this stores m/z and intensity of a spectrum as a BLOB in the database table reducing the size of the database tables considerably.
Performance is evaluated based on some frequently used tasks and compared to a Spectra with a MsBackendMzR backend. Parallel processing is disabled and the files and database are stored on SSD disks with ~ 1GB/s throughput.
## con is the connection to the MariaDB database
ccle <- Spectra(con, source = MsqlBackend())
## con_blob is the connection to the MariaDB database with m/z and intensity stored as BLOB
ccle_blob <- Spectra(con_blob, source = MsqlBackend())
ccle_mzr <- Spectra(fls, source = MsBackendMzR())
## Size comparison
print(object.size(ccle), units = "MB")
## 162.5 MB
print(object.size(ccle_mzr), units = "MB")
## 13506 MBSince the MsqlBackend keeps only the primary keys in memory it's size is way smaller than the MsBackenMzR backed Spectra.
Subsetting by retention time range
system.time(
ccle_rt <- filterRt(ccle, c(100, 110))
)
## user system elapsed
## 58.140 1.556 136.230
system.time(
ccle_blob_rt <- filterRt(ccle_blob, c(100, 110))
)
## user system elapsed
## 34.038 1.796 118.031
system.time(
ccle_mzr_rt <- filterRt(ccle_mzr, c(100, 110))
)
## user system elapsed
## 0.674 0.180 0.855Filtering the MsBackendMzR is way faster since the data resides in memory. Getting the peaks data for these spectra:
length(ccle_mzr_rt)
[1] 14791
system.time(
pd <- peaksData(ccle_rt)
)
## user system elapsed
## 2.174 0.030 19.202
system.time(
pd_blob <- peaksData(ccle_blob_rt)
)
## user system elapsed
## 0.230 0.026 1.999
system.time(
pd_mzr <- peaksData(ccle_mzr_rt)
)
## user system elapsed
## 65.183 3.825 69.416Extracting peaks data from an MsqlBackend is faster. The performance is even better if the m/z and intensity values are stored as a BLOB.
Get MS2 spectra with precursor m/z matching a predefined value
pmz <- 940.1510
system.time(
ccle_pmz <- filterPrecursorMzValues(ccle, pmz[1], tolerance = 0.01)
)
## user system elapsed
## 0.251 0.047 14.008
system.time(
ccle_blob_pmz <- filterPrecursorMzValues(ccle_blob, pmz[1], tolerance = 0.01)
)
## user system elapsed
## 0.265 0.000 14.707
system.time(
ccle_mzr_pmz <- filterPrecursorMzValues(ccle_mzr, pmz[1], tolerance = 0.01)
)
## user system elapsed
## 3.041 0.146 3.191
length(ccle_mzr_pmz)
[1] 1693Subsetting is again faster if the data is in memory, but extracting peaks data is faster with the database-backed backend.
system.time(
pd <- peaksData(ccle_pmz)
)
## user system elapsed
## 0.233 0.040 6.569
system.time(
pd_blob <- peaksData(ccle_blob_pmz)
)
## user system elapsed
## 0.043 0.003 1.064
system.time(
pd_mzr <- peaksData(ccle_mzr_pmz)
)
## user system elapsed
## 64.462 3.819 69.489Note that caching e.g. the precursor m/z values locally (in memory) speeds up the filtering operation (but will obviously increase the memory demand):
ccle$precursorMz <- precursorMz(ccle)
system.time(
ccle_pmz <- filterPrecursorMzValues(ccle, pmz, tolerance = 0.01)
)
## user system elapsed
## 3.164 0.107 3.274Conclusion
The MsqlBackend has a very low memory footprint and would also allow remote access to full data sets stored in a single place. Performance for simple subsetting operations is slower than if the data was in memory, but could be improved by loading data partially into memory (i.e. caching the data).
Storing m/z and intensity values as BLOB in the database table results in better performance extracting peak data from the database. Thus, this option was set as default in PR #4 .
MikeKutz
Create a backend using various test data sets.