Tag Info

New answers tagged

1

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use... and Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use? This is partially true. Index fragmentation wont cause HIGH CPU. Internal ...


0

This snippet was pretty telling: -rw-rw---- 1 mysql mysql 18874368 Dec 13 23:03 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Dec 13 23:03 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Dec 13 23:03 ib_logfile1 Looks like you are under constant write load. What are you doing with this db?


1

You can use a TRIGGER to update db1.reps.lastSyncAt when db2.user.lastmodifieddate is updated. Trigger: DELIMITER $$ DROP TRIGGER IF EXISTS db2.user_BEFORE_UPDATE$$ USE `db2`$$ CREATE DEFINER=`root`@`%` TRIGGER `db2`.`user_BEFORE_UPDATE` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN SET @UserVerification=(select reps.veeva_rep_id from db1.reps where ...


1

This is a pure guess as I cannot find anything to support this but I believe that the reason it is much slower when you perform it on your local box is that it is going through the iteration on your local box. If you did this as a cursor I believe this would all process from SQL and be much faster. AKA GO 10000 is being iterated 10000 times from your local ...


1

Good answer from Rolando. In addition -- Triggers should not be used for logic, because a couple of inter-relating triggers later, things will get confusing fast. A nice set of instructions in a stored procedure or client side procedure can get across the business logic more clearly than a bunch of hidden logic in the database. There are also limitations ...


1

Since you have mysql.general_log already converted to MyISAM and indexed, I would recommend making snapshots of that table based on a timestamp range. What I mean by snapshot is a temp table that contains just the events you wish to mark. Suppose you want to log entries from the last 10 minutes. This Dynamic SQL should do it for you : SET sql_log_bin = 0; ...


5

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following ALTER TABLE tablename REBUILD That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway ...


0

So, your approach is good in my opinion, with little tweaks: First, when you create the temp table, or when you are generating the hash value, use DATE function (You mentioned is but not sure you're using it in the query): SELECT ...., crc32(CONCAT(column1, DATE(column10))), so you don't have to deal with 'fuzziness' [Assuming that you want the ...


0

If you are going to make a new entry means, You can just do Insert into tablename select query If you want to update your table based on some values, but the values are need to took from some other table through joining(Select query which you mentioned)you can store those values in temp table.And then you can iterate those values through do-while or ...


1

You can indeed use stored procedures to refresh the data in your database. It just means coding the INSERTs and UPDATEs appropriate to your data. If you are replacing the existing data with new data, you could use: INSERT INTO ... SELECT FROM ... syntax, after deleting the existing data, to pull the data from your query and insert it into the now empty ...


0

You should use MySQL's BENCHMARK() function Here are my posts on how to use it Apr 03, 2012 : How to use the 'select benchmark' command on a procedure Apr 16, 2013 : MySQL: CAST + SUBSTR vs. FLOOR + MOD BTW if you do configuartion changes, you should restart mysqld immediately after the configuration changes are in place. Then, run your I/O ...


0

If you dont mind about dirty read then you might either use NOLOCK hint or READUNCOMMITTED TRANSACTION ISOLATION. This could improve your query's performance in case this query is continuously blocked by other session. Otherwise your could look for what is the Max Degree of Parallelism is set for your server. If it is set to 1 then you could increase it. ...


-1

cc needs INDEX(pap, stati) (or in the opposite order). Please provide SHOW CREATE TABLE.


9

I've tried your query on a SQL 2012 instance and trace flag 4199 seems to fix the issue. With it enabled I get a merge join for a total cost of 0.24 and none of the extra branches. The specific KB article for this issue is Performance issues occur when the join predicate in your query has outer reference columns in SQL Server 2005 or in SQL Server 2008 ...


2

Well this answer depends on how your database is designed and licensed. If you have an Enterprise license, you should take a look at partitioning. This way you may partition your data which will help you in querying those table. If you make a good partitioning, you'll be able to just read a small portion of the table from the disc, instead of querying ...


-2

If that query runs for five hours, then i suspect there could be an improper join between tables which could cause duplicated records. Check your Conditions you used to make a join.


0

join on P kills the left join on P2 moc.WRK_LOC_REG_UKHP is not null kills 3 left joins from GAPDatabase.waStock t inner join GAPDatabase.mpStockStatus tss on tss.REF_STAT_UKHP = t.REF_STAT_UKHP and t.APPR_DT between '1/1/2015' and getdate() inner join GAPDatabase.Progress p on t.cust_REF = p.cust_REF inner ...


3

The first thing I would check is the fnGetUSD -function. It looks like currency conversion, and you're now calling the function 5 times for every single row. That can be a huge performance issue. At least look into changing it to a inline table valued function (the multi-statement function will not help) or adding the calculation directly to this view. For ...


0

I would suggest to filter earlier. Your where clause has some values which can be filtered in a subquery, which will preserve a join of columns which weren't used and thrown away at the end. This will normally speed things up. Another point could be the table order and the type of table joins. But to get throw this I would need the execution plan. Another ...


0

schemas is used for organizational structure and namespacing. By namespacing I mean that you can have similarly named table in few places. And another feature of using schema is additional access control for that schema. You will have joins depending on your data model. You are using schemas implicitly already. There is a "search path" defined in PostgreSQL ...


0

I have found that if tables get too large the server load can go up dramatically and CPU usage skyrocket.


0

I have a way to do it in one query, though it may not be faster than your current solution. DELIMITER $$ CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent_id`(`id` INT) RETURNS varchar(1024) CHARSET utf8 DETERMINISTIC BEGIN DECLARE return_string VARCHAR(1024); DECLARE return_string_separator CHAR(1); DECLARE parent_id INT; SET ...


2

So I came to find out that my heroku instance wasn't in the same availability region as my rds instance. Once I changed it over to east coast the RDS DB was slightly quicker then my heroku DB. Incase anyone is stuck on how to create a rds instance in a different region through aws console, you need to click on region (i.e. Oregon) button in the top right ...


1

Drawbacks of what you propose: Temp tables (for complex SELECTs) cannot use MEMORY, which is faster than the fallback of MyISAM. Storing numeric 'values' VARCHAR makes it difficult or slow to test for. Key-value schema leads to ugly JOINs. And they are inefficient. In InnoDB, depending on the ROW_FORMAT, TEXT fields may be stored in other blocks. This ...


0

In addition to Shawn Melton, because I can not comment yet, you can update your statistics regularly. This will generate a new execution plan. After that recompile your stored procedure with sp_recompile. Here is a link for a great script: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


1

What you are referring is not related to windows. It's for *NIX systems. For windows, you should monitor •\LogicalDisk\Avg. Disk Sec/Read •\LogicalDisk\Avg. Disk Sec/Write •\LogicalDisk\Disk Bytes/Sec •\LogicalDisk\Disk Reads/Sec •\LogicalDisk\Disk Writes/Sec •\LogicalDisk\Split IO/sec •\LogicalDisk\Disk Transfers/sec You can use PAL to analyze the ...


0

MyISAM only -- Normally a record is a continuous stream of bytes in the .MYD file. This includes TEXT and BLOB columns. An index has a byte offset (or record number) to point into the .MYD file. After row(s) have been DELETEd, there can be holes in the .MYD. MyISAM prefers to fill in the holes before appending to the .MYD. However the hole(s) may not be ...


1

Points: RAID 5 is a poorly performing configuration for database files especially for writes, and Partitioning is even worse. Yes, it works and is reliable, but it's definitely a low-performance disk configuration that I would only use myself for development or the most light-weight of apps. The standard configuration for the physical host of a SQL ...


1

Even in your updated query, you hit FIELDDATA eight times and Events three times. Rather than focusing so much on indexes, let's refactor to reduce the number of table hits. See below for an example. This is just my best guess at a refactor without a full understanding of your data, so keep or ignore the bits that work for your data. Changes include: Use ...



Top 50 recent answers are included