Tag Info

New answers tagged

0

There are a few things that might be causing this issue, but I can't be sure any of them are the real problem. The troubleshooting all involves turning on extra logging in the database, then seeing if the slow parts line up with messages there. Make sure you put a timestamp in the log_line_prefix setting to have useful logs to look at. See my tuning intro ...


2

SQL Server does not page, not in the sense the OS pages. Data does not need to be paged becase the memory is just a cache of the data files. Data can simply be removed from memory and read back when needed. Caches (eg. Procedure cache) are not paged either. Entries are removed and, if needed, they can be recreated from scratch (eg. compile). The closest ...


1

I remember to have the same problems and it has something to do with how vBulletin is programmed. Please check regular "cronjobs" (not talking about system cronjobs you have already mentioned not to be the culprit) run by vBulletin which are triggered by visting users. You can find them in version 4 of vBulletin in your admin panel, just below the settings ...


1

You can definitely keep all your dimensions and measures in one fact table and not use any dimension tables. Make sure your OLAP tool supports this though. Normalizing out your dimensions into other tables is done mostly to minimize the size of the fact table, which can get large fast. With no dimension tables you're looking at about 336 MB per year (not ...


2

@RolandMySQLDBA has given the right hint to answer the question. The problem seems to lie in the query and that for the results to be given back, each of those fields has to be read (somehow from the database). I dropped all indexes but the PRIMARY KEY, and inserted this new index: ALTER TABLE newbb_innopost ADD INDEX threadid_visible_dateline_index ...


0

Your Sharepoint db instance will have a ReportingService_weirdsequenceofcharacters database. It stores report execution and performance metrics. There is a view called ExecutionLog2 in that database which has 3 Times. Use them to find whether the bottleneck is sql, report processing, or rendering. You should also be able to compare these values to the values ...


1

While there some manual things that could be done at database level (moving tablespaces around, etc.), most of the settings for MySQL are transparent to the storage layer, meaning that you do not need to do anything to take advantage of the RAID. While a RAID 0 could provide you a better theoretical throughput in reads and writes (at the cost of having 4 ...


2

Firstly: unless this is a replication slave, development box, or similar, that can easily be reconstructed from other sources should the worst happen, RAID0 is not recommended. It provides good performance but potentially seriously bad reliability: if any one of your four drives fails then the whole array is toast. Edit after new information: it would ...


0

Looks like a case of Parameter Sniffing! Check google on it. Running these commands will have the same effect: DBCC FREEPROCCACHE This command removes all of the cached query plans and execution contexts from the plan cache. It is not advisable to run this command on a production server because it can adversely affect performance of running applications. ...


0

Performance is a very fluid concept. It can vary according, for example, to the number of rows in your tables (use of indexes or not). It can depend on whether you use InnoDB or MyISAM. It can depend on what else is occurring on the system at the same time. It can depend on your hardware. And it can certainly depend on your RDBMS. You ...


4

YOUR QUERY SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506. REALITY CHECK No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them ...


2

Try: LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId AND LastOperationTime IS NOT NULL The engine could be grabbing the Null row separately, as it's a slightly different scenario to not finding one. But as you essentially want the same behaviour, just out the explicit filter in there, within the ON clause of your LEFT JOIN. Then the only NULLs ...


0

I'm no Postgres expert so this might be wrong! Your primary key has 3 columns, sessionID as the first field. Does the file contain a decent spread of timestamps? you might consider making that the first field in the primary key or using a surrogate key as currently this is fairly wide. From your script I dont think you have a cluster. Different to SQL ...


2

This is because of the changes affected when you modify the compatibility level of any database within SQL Server. This was an affect seen starting at SQL Server 2008 I believe, at least it shows up in documentation since then. As stated here on MSDN for the ALTER DATABASE SET COMPATIBILITY_LEVEL: Compatibility Levels and Stored Procedures When a ...


-1

Your tables do have several indexes to update, did you remember to run a optimize table from time to time? /usr/bin/mysqlcheck -o --auto-repair MyDatabaseName This will optimize all tables and indexes in the Database MyDatabaseName. Depending on how your data looks this might help with INSERT and SELECT speed. I guess since you have tried everything you ...


2

PROBLEM From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column. Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on. SOLUTION What you really need is a single FULLTEXT index with all 3 columns ALTER TABLE ...


1

That is 100% normal: what you are seeing is the background threads performing the following operations: Flushing dirty pages from the buffer pool into the tablespace files. You can see that is happening on your case due to the difference between your LSN and the latest checkpoint on the SHOW ENGINE INNODB STATUS. Merging non-unique secondary indexes ...


0

This is almost certainly memory fragmentation, as redis is well-known and loved in production and you probably haven't found a memory leak. The recommendations about setting the size of the pool won't help fragmentation. You'll have to specifically lower the Redis size - lower than your actual memory size - because Redis can't account for fragmentation - ...


3

I might be flying blind on this one, but here it goes ... In your question and comments, you stated the following: The MySQL version in both servers is the same, even the config files are the same (the only difference is the path of the data and the fact that the production server doesn't log anything but the errors) Yes, the DB are the same ...


1

Your entities are based on hours, not days, so you should go with one row per hour, just like you did, not a full day in a row. I did not understand if when you say data between two timestamps, if timestamp refers to the "inserted" column, or if it refers to the date and times on the data file. If it is the latter one, then having a datetime column for your ...


1

Try to start a transaction or set autocommit=false prior the updates. start transaction; .... a lot of updates here ... commit; Also, the swapping may yield to the lack of physical RAM which occurs in some situations (big resources created by MySQL). You may also try to - increase the "key_buffer_size" to the maximum permitted by your SO and MySQL ...


2

Shown RDS statistics are too global and broad to provide an answer to the question "Who is to blame for the increased load?". There are serveral ways to profile your queries, but as you are using RDS with MySQL 5.6, I would recommend you to use PERFORMANCE_SCHEMA to do it easily, as you won't need external software (some of which is not fully ...


0

Are there any benefits in disabling mongodb journaling besides performance gain? The only other marginal benefit is if you are using a 32-bit build (which is also not recommended for production use). Since 32-bit builds are limited to ~2Gb of addressable data for memory mapped files, the journal is off by default to allow for more data (with journal ...


0

I have had success in general replacing lengthy IN operations with a join against a temporary table. This makes sense because RDBMS are optimized to perform JOIN's as efficiently as possible, and the handling of lengthy IN lists will most likely be done by repeating the query for every value in the IN list. The performance issue you see could easily be ...


0

The only reason that I can think of is that the system was on the limit of physical memory and using a little bit of virtual memory (on Windows, paging file). Once you add more memory to Oracle, you push a lot of that buffer out of physical memory, which causes a huge increase in response time due to paging. Having a lot of paging taking place on an Oracle ...


1

Identical subqueries are normally only performed once. However, the way you write a query isn't neccessarily the actual execution order, so there are no guarantees. To make sure, view your estimated execution plan with one or both of the subqueries, and compare them. In your example, you could place the subquery in a JOIN instead, which would make it both ...


1

Given that you can not afford to drop and recreate the table, this related answer would be a better fit: Best way to populate a new column in a large table? You might drop expendable indexes and recreate them when you are done (if they aren't completely expendable). And all the general advice for performance optimization applies. There is not much more ...


5

Assuming you are dealing with a single time zone at a time (e.g. British Time), and can identify all rows that are in British Time (maybe that's everyone so far), you could create a calendar table that has the DST time change dates for Summer Time. You subtract an hour for all times that fall between, and don't touch the ones that don't. Sample data: CREATE ...


4

Best is to use a calendar table. To update, do it in batches. shamelessly copied from Aaron's 3 part series on MSSQLTips. Thanks @AaronBertrand ! Note: Read all the 3 parts thoroughly as I have implemented the concept in my company and it works flawless. CREATE TABLE dbo.TimeZones ( TimeZoneID TINYINT NOT NULL PRIMARY KEY, StandardDescription ...


1

The order in which tables are joined isn't defined in the query (unless you use a join hint, which I wouldn't recommend), so you can't be sure that A is joined to B before B is joined to C. To answer your question, in most cases SQL Server can tell from the statistics on the table (or other criteria) that the query won't return any rows. In that case, ...


1

I have three suggestions SUGGESTION #1 : Rewrite the query You should rewrite the query as follows SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( DATE(NOW() - INTERVAL 1 DAY) + INTERVAL 0 SECOND ) GROUP BY http ORDER BY count; or SELECT * FROM ( SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( ...


-2

Change your date column type to an integer. Store the date as a Unix date in integer. Timestamp Is a lot larger than an int. You'd get some bang out of that.


1

Let's do a hypothetical answer to a hypothetical question (all of this are ball-park calculations, I do not intend to be very exact, but to have a big picture): Your table, at the bare minimum, will have a storage requirements of: 50 000 000 000*(8 + 8 + 1 + 1 + 20*3) / 1024^4 ~ 4TB I do not have into account the TEXT because, even if it can be inline, ...


0

Primary key look-up will much faster than indexed columns. For the hardware question if your database fits on memory then searching by primary key only one row then you can achieve 0.1 secs.


-1

@ursitesion We also had a similar problem after we migrated our MySQL DB from Windows to Linux. We were lucky that our table names were already in small caps, the only issue we faced was that inside our application & some Stored Procedures (SP) we had mentioned the table names in Large Caps / Camel Case. We followed the following steps and were able to ...


6

It makes no difference: the optimiser evaluates the query and will work out predicate order for itself, matching a suitable index and all the other good stuff it does. This is because SQL is "declarative" not "procedural": you say what you want, not how to do it. It's nicer to read though...


2

Keep it. A foreign key exists primarily to ensure that the value given in the child table really does exist in the parent (foreign) table. Cascading deletes or updates are, generally, a distant second. That's the general case; your specific business rules may be different.


2

I have an old post Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios. In that post, I do not use a foreign key constraint in my example. Soft deletes are way faster. You just have to integrate JOINs for tombstone tables or AND deleted=0 in WHERE clauses. In light of these things, you probably could live without the ...


2

This is SQL Server updating the statistics on a table. It can be triggered automatically when about 20% of the data in your table changes, or on demand if you run jobs that update statistics. You have a few ways to lessen the pain. You can enable asynchronous statistics updates, which lets SQL Server update stats behind the scenes without blocking other ...


0

Take a look here - it's a recommended book list from those who bring you (us) MariaDB. As you will see, many of the books are (also) about MySQL. Take a look here for the differences between MariaDB and MySQL. As I understand it, MariaDB is gradually drifting away from MySQL and will not remain plug and play compatible for long. With most of the differences, ...


1

Yes, all the basic optimizations in MySQL 5.5 apply to MariaDB 5.5. However, starting with MariaDB 10, a real fork, not all improvements in 5.6 are in MariaDB codebase, and alternatively, MariaDB has some exclusive features (Hash JOINS). For now, those are not too different, that may change in the future. For a book, where most of the optimizations are ...


1

What finally solved my problem was turning archive mode off, perform the truncation, then turn archive mode back on. Whith archive mode on, the truncation toook to long and corrupted the database.


0

1a) If the two insert methods create the same data then making them one table seems to make the most sense. After a bulk load I would recomend running maintenance scripts to make sure that all indexing is up to date. 1b) The only way to know if the bulk insert and the single insert would effect each other in this specific case would be to test it. With ...


1

The whole point about profiling is to ascertain where your system is spending time. This can often give developers some clues as to how to optimise their queries (by adding indexes for example). The 600 pound gorilla in this area is Oracle's Wait Interface (OWI) which is one of the most sophisticated monitoring and profiling tools available to database ...


2

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it. In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a JOIN wold omit that ...


1

Here you have a video on the basic inner structure and inner workings of indexes. I recommend you to watch it all. Basically, indexes are ordered structures on disk (although they can be cached, and they normally will for better performance) that will allow certain operations to be done faster. In particular, in MySQL, B-tree/B+tree (the most common ones) ...


0

Adam has covered most of the points. However, there are a few things to add. Usage of Windows in a production environment For tl;dr candidates: It is a Very Bad Idea™ to run a mongod instance except for a config server on Windows. Here is why. Both ReFS and NTFS are at least 1/3 slower than ext4 or XFS, the recommended filesystems for Linux production ...



Top 50 recent answers are included