Looking at the my.ini, I have two suggestions
I would bump up the following settings in your my.ini
This will make some joins and sort stay in memory. Of course, once a JOIN or an ORDER BY needs more than 4M, it will page to disk as a MyISAM table.
If you cannot login as root@localhost, then restart ...
It is legacy from SQL Server 2000
0 and 100 were different back then
100 meant "fill all pages including all b-tree index levels"
0 meant "leave some space at higher levels in the b-tree index"
Since SQL Server 2005, both mean "fill all pages including all b-tree index levels"
Quotes from BOL (My bold)
SQL Server 2000:
A fill factor value of 0 does ...
For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL.
You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. <-- This is no longer true in newer PgJDBC versions, which can now batch prepared statements to reduce round-...
I answer my own question here for completeness
I will select @RolandoMySQLDBA as the preferred answer because it gave me the most hints even though it didn't actually solve my problem.
Below are the results of my investigation
MySQL on Windows just creates lots of temporary tables and tuning MySQL by modifying the content of the configuration ...
If you can't change the query you can use a plan guide.
Test the performance of the query with OPTION (QUERYTRACEON 4138) (will need someone with sysadmin permissions to try this).
If that produces satisfactory performance you can apply this with a plan guide. If it doesn't produce satisfactory performance try and find a hint that does. Possibly OPTION (...
Based on your stated requirements, your model is in pretty good shape.
Here are some suggestions for improvement:
You don't say so explicitly, so it's hard to say - but it looks like you might be storing the user password directly. This would be very bad! If you look at common authentication databases, passwords are stored in encrypted form. You often ...
First, the costs are not supposed to directly relate to the execution time. They're strictly relative; a plan which costs more should take longer to actually execute. You can adjust sequential_page_cost in order to "tune" costs so that they're closer to milleseconds of execution, but IMHO that's a waste of time.
For 99% of users, there's only three cost ...
Refer the the Concepts Guide - Overview of Views for this sort of question:
Overview of Views
A view is a logical representation of one or more tables. In essence, a view is a stored query.
Characteristics of Views
Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a ...
PostgreSQL relies on the operating system's disk cache for most caching. This cache is usually reported as "free" RAM by most tools, because modern operating systems use all but a little bit of the currently-free RAM for disk cache.
This is normal.
To confirm, use a better tool that shows buffers/cache separately from truly free memory. On Linux, free -m ...
Starting with MySQL 5.6, the performance_schema instruments Table I/O, and computes aggregated statistics by table, and by index.
See table performance_schema.table_io_waits_summary_by_index_usage:
Finding the least recently used index ...
Depending on how many different data sets there are, one option would be to partition the tables per-dataset.
When a dataset is updated, BEGIN a new transaction, TRUNCATE the table, COPY the new data into it, and COMMIT. PostgreSQL has an optimisation where COPYing into a table that's been TRUNCATEd in the same transaction does much less I/O if you're using ...
The slow plan isn't calculating the MAX for each row in the outer query.
In fact it never explicitly calculates it at all.
It gives a plan similar to
AS (SELECT TOP(1) WITH TIES *
WHERE year IS NOT NULL
ORDER BY year desc)
GROUP BY month
Slow Plan (...
You need to tune your database. This is a complicated process with many variables and decision trees. Without specifics we can only really get you started here.
If you have a script that runs lots of SQL and slows down your DB, and your cpu and memory usage are fine while it's running, then you are probably running into I/O problems. i.e. You are hammering ...
Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique.
The side effect of this is that queries via primary and unique keys are usually "fast".
Now if you haven't defined primary or unique keys on your tables,
You don't have a relational table but you have junk (OK, ...
Short Answer: Yes.
There are many reasons but the few that stick to mind:
1.) Trust but verify - SQL cares a lot about its environment, the hardware or virtualized system it is on. When I help a company with SQL on VM issues it is normally a misconfigured VM. In many cases the idea of SQL on VM is about to be thrown away.
2.) DBAs should look at memory ...
You've got two basic options.
Reduce the workload required by the tempdb database
Get faster hard drives
As for #1 look for indexes in other databases which are missing. Fixing missing indexes will cause less spill to the tempdb database which will reduce the workload.
If the applications on the server are using temp tables a lot there's nothing you can ...
Your CPU is spending most of the time in user space. That is most likely due to locking issues or very inefficient queries.
What I would do is go through the following steps and check after each if the problem is still present. Proceed only if it is.
Disable query_cache: you don't get too much benefit from it anyway and it's a common contention point.
If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):
select * from my_table
(from_field <= 1533177608)
(to_field >= 1533177608)
How well the database is running is just how well SQL statements are running.
Pick a representative sample of SQL statements that hit each database on your server. Picking a sample is easy; picking a representative sample isn't. Ideally, a representative sample should include some SELECT, INSERT, UPDATE, and DELETE statements. But it might not be ...
The default minimum size of a text column (representing a NULL value) depends on the rest of the row. In most cases it is absolutely free, if the NULL bitmap has room for another bit.
Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
More about calculating the effective row size:
Configuring PostgreSQL for read performance
This looks like a job for InnoDB Architecture Tuning !!!
Where can BLOB data become a bottleneck for InnoDB? Three places
Place #1 : InnoDB Log File
The size of the InnoDB Log Files (set by innodb_log_file_size) could be a bottleneck if there are many transactions being written that update BLOB data.
Place #2 : InnoDB Log Buffer
The InnoDB Log Buffer (...
There is something you are not taking into account.
MyISAM caches index pages into the Key Cache
InnoDB caches data and index pages in the Buffer Pool
I wrote a post about this : What are the main differences between InnoDB and MyISAM?
The 466092032 you gave is just the number of bytes taken by indexes. The InnoDB Storage Engine is moving data and index ...
Yes as the DBA you should have access to view the vSphere info. The biggest reason being to troubleshoot the noisy neighbor problem where another VM is taking all the cpu power not leaving any resources for you. It isn't possible to see this from within a VM. This isn't limited to CPU but can also be disk IO as well.
You also want to be able to see exactly ...
There could be many reasons for this. A few questions:
Do you do index maintenance?
Do you import data? Or is it all entered via a front-end application by users?
Option 1 - We could look at your wait stats and see if that helps pin-point a direction to look.
Option 2 - We could look at the execution plan of a slow-running query.
For option 1:
Run this ...
Perfectly ok - especially since you can add and subtract integer to / from date directly in Postgres.
However, int2 might not be better than a plain integer. True, integer occupies 4 bytes instead of 2, but many operations are optimized for integer. Among other things, integer is the default numeric type for numbers without decimal point. For int2 you ...
But why do Oracle stores this so inefficiently? (a separated structure
for the primary key)
It is inefficient for this use case.
Do Oracle has something similar to MS SQL's clustered index, that
makes the index part of the related table?
It is called IOT (Index-organized Table) in Oracle.
Overview of Index-Organized Tables
The database does this by default, no hints/tuning needed.
create table data(partitioned_key_index number, some_dummy_measure number)
partition by list (partitioned_key_index)
partition p1 values (1),
partition p2 values (2),
partition p3 values (3),
partition p4 values (4),
partition p5 values (5)
insert into data with g as ...
Thanks @Frank Heikens, @dezso, and @Colin 't Hart for suggestions and involvement, +1 to you guys.
The problem was with the way I was using MyBatis. I had used a 'special' :) singleton pattern which was able to spawn new instances if needed and had one shared connection for some general stuff.
Though for the operations mentioned in my question I was using ...
Unfortunately you cannot do this with Extended events unless the data from XE can be converted into the format that DTA uses or can be stored in the tables generated by DTA.
Not recommended though but Trace is is still available, so can be used if you want.
You can even use DMV's for finding missing indexes, etc.
Also worth mentioning Aaron's post : Don't ...
My rules of thumb for indexing are to
first, read the execution plan,
then think about adding (or verifying) an index on
every column used in a WHERE clause,
every column used in a JOIN condition,
every column used in an ORDER BY.
In some cases, one multi-column index will be faster in SELECT statements than several single-column indexes.
Then, make ...