0

There are a java web application which does use Hibernate for interacting with database. Recently, the web app started working slowly and its getting worse for some of its operations and after debugging I've found out that it's a database issue. One of the tables, responding for specific data got about 3mil rows and its increasing, so it takes about to 5-10 sec to add one record.

I was thinking about creating different tables for each year, so the data of a year 2018 for example would be stored only in table_xxx_2018. One solution would be to manually, beforehand create all tables and classes and map them in .hbm.xml files, but I don't know if its good solution or no. Also, it doesn't seem to be sustainable. So, I was looking forward to see if its possible to create dynamic tables using hibernate and map them with responding classes in java.

I also googled some tweaks and improvements for PostgreSQL but it didn't help. The main problem is increased flow of the data which is coming to the app, and it seems that Postgres started choking at 3mil+ rows per table.

These are the server specs, only Postgres is running on this server and nothing else:

  • CPU Intel Xeon E5-2630 @2.30Ghz
  • RAM 32 GB and SSD drives in RAID

Any suggestions are welcome

EDIT1:

An example of the piece of code

@Override
public void saveOrUpdateCitizen(Citizen citizen) {
    Session session = sessionFactory.getCurrentSession();
    session.saveOrUpdate(citizen);              
}
Daler
  • 1,205
  • 3
  • 18
  • 39
  • Your question is unclear, what operations are getting slow? read or write? or both? – Jerry Chin Feb 20 '19 at 05:40
  • @JerryChin both – Daler Feb 20 '19 at 05:41
  • have you tried executing EXPLAIN again your database? what's the output? – Jerry Chin Feb 20 '19 at 05:50
  • An `INSERT` won't get slower as the table grows. You'll have to figure out the SQL statements that actually run and analyze the `EXPLAIN (ANALYZE, BUFFERS)` output for them. – Laurenz Albe Feb 20 '19 at 05:52
  • I do use hibernate to insert, update, or select from database. I dont write any SQL statements.. how would one run the EXPLAIN from hibernate? Is there an option to do it. – Daler Feb 20 '19 at 05:53
  • 1
    On a small table with just 3 million rows, an inserts should take a **lot** less than 5 seconds - actually a lot less than even a second (more in the range of 100 milliseconds, especially with SSDs). I would actually suspect Hibernate to be the culprit here. But what you are looking for is called [partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) but doing that for a small table like that seems like overkill. –  Feb 20 '19 at 06:49
  • To find out if your obfuscation layer is the culprit or if indeed the database is slow for some reason, you could turn on logging of slow statements, e.g. everything that is slower than 2 seconds. If the `insert` statements are not logged, then it's the obfuscation layer. _If_ they are logged, then we need to see the actual `create table` statement for the tables in question, all `create index` statements for that table and the real INSERT statement that was generated by your obfuscation layer. See [here](https://stackoverflow.com/questions/2536829/hibernate-show-real-sql) on how to do that –  Feb 20 '19 at 07:41

0 Answers0