Tag Info

New answers tagged

0

MySQL Perspective I once had a client that had a CRM aaplication with this layout 780 databases 162 tables per database See my full description in an old post : More table or more database for better performance? The challenge I faced doing database maintenance was the following 5 procedures: troubleshooting and optimizing slow queries add supportive ...


1

Having been on the receiving end of this multiple times from COTS applications, I would choose multiple databases almost every time. In this case, I would still choose multiple databases over any other, and here is why: !This is for the SQL Server related tag and is not transferrable in logic to other RDBMSs! Standards - each database is setup the same ...


0

You can choose the third option, A table with all records, make a column as CustomerId with bigint datatype and auto-incrementing(IDENTITY), and make that columns as PRIMARY KEY. Now you can think indexes later depending on how you want to fetch data from table and will be fine in terms of performance. This first two options will make too many objects ...


0

To answer your questions in order: You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment. Or close to that, depending on which version of the OS you are using your specific steps may be different. But you will only be able to see the fragmentation on a volume level, not ...


0

Since the data is updated constantly, you will have a hard time getting the benefits from index-only scans. They are mostly effective on read-only or read-mostly data. If that is the case, adding columns to the index will likely be counterproductive. If any of the constant stream of updates were into previously-unindexed columns, those could have ...


0

I would use LIKE for one very good reason. From the docco here - "This function does not work properly if the first argument contains a comma (“,”) character". You could always use an EXPLAIN to check on performance - but simply for the reason above, I would not use it. [EDIT] You could always use LOCATE(). I strongly recommend that you don't use MySQL's ...


0

Yes, I would add a surrogate 4-byte integer key. Your current two columns are 100 bytes, this could then be reduced to 58 bytes by the addition of the new identity column. You could even make the surrogate key a 2-byte smallint if you are sure you will never exceed 32,767 categories (might be still a good idea to leave as INT just in case). The space ...


0

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column. Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result ...


2

It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently): 1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately ...


1

Since we don't have the hardware, of course, I can't benchmark it While you can't simulate the exact hardware you might be able to get some estimates by comparing simpler local hardware with similar relative differences. If you are upgrading from spinning rust in a similar RAID config then you could benchmark a single traditional drive against a single ...


2

I'm a proud owner of a Samsung 850 Pro for my desktop machine. It is a great disk, but it is not server-grade, and it is far from the PCI Flashcards sold by Virident and FusionIO (to put examples of some known brands). On the specialized hardware, a recent version of MySQL is almost a must, and some configuration tuning is needed to get most of them (change ...


1

I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints. For instance, if the app uses a 0 in a key column instead of NULL to ...


1

If you want to condense it into a single SELECT, this would work: SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id) t1_id, t2_id FROM t0 ORDER BY coalesce(t2_id, t1_id), t2_id, t1_id; Equivalent, except for sort order. SQL Fiddle. If you want this to be fast, I'd try a functional index: CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), ...


1

For the simple case, I can only think of minor improvements to the query: ( SELECT DISTINCT ON (t2_id) t1_id, t2_id FROM t0 WHERE t2_id IS NOT NULL ORDER BY t2_id, t1_id -- to get consistent results ) UNION ALL ( SELECT DISTINCT ON (t1_id) t1_id, NULL -- cheaper FROM t0 WHERE t2_id IS NULL -- if you retrieve ...


0

For starters, I suspect your current query is incorrect, missing an additional condition AND v.user_id is null. Your query, simplified with table aliases and improved somewhat: SELECT DISTINCT ON (coalesce(e.user_id, e.browser_id), e.user_id) e.browser_id, e.user_id, v.time FROM events e JOIN ( SELECT DISTINCT ON (coalesce(user_id, browser_id), ...


-1

I am running 2008 R2 with 46GB of Ram. No virtual machines. SQL Server 2008. Databases are about 300GB. I put the databases on a solid state drive recently and tripled my data output. The server is currently using 45GB of Ram and running good. FC Sata Raids and SAS SCSI Raids. 26 logical drives in all. 144 Spinning disks. Yesterday I was only using 24GB ...


2

Performance It depends A LOT on the actual data (are there many people having read a few books each, or a few people having read many books), skewing (are there some power-readers?) and the books which you query - like ypercube mentioned the bible. And this is before the optimizer really sets in and decides to completely rewrite your query because it ...


1

My diagnosis from the information that is currently available: Sounds like your tables are for whatever reason mostly uncached. The first run brings all data into cache. The plans probably have tons of random IO. This is extremely slow to run on a disk, and almost unnoticeable in memory. Therefore, I guess that the new edition does not provide you as much ...


0

Before deciding on value for max_connections I suggest that you look at WHY it reached the max_connection condition. A bunch of sleeping/idle connection due to loose coding is very common cause. Check if you have proper value for wait_timeout and adjust if this is your symptom. Besides that, I would check for INSERT/UPDATE query that holds table locks for ...


2

Give your example, this will do it: select distinct on (coalesce(t2_id, t1_id)) t1_id, t2_id from t0 order by coalesce(t2_id, t1_id); It essentially says "do a distinct on t2_id, but if that is null use t1_id instead".


0

You would save yourself a fortune in time by changing data types to `gender` varchar(1) -- or even `enum('m','f') `city` varchar (60) -- or something sane like that The index on these would be much more friendly to you and so much smaller. Trust me, the smaller and more exact you can make the data the easier it is to search. I would expect your search ...


4

Databases require certain guarantees from the storage it runs on to implement ACID. Unfortunately, it is not always possible for the database to determine if those guarantees are met. Here are some requirements that the storage (no matter if it is NAS, direct attached or SAN) must meet: Write Ordering: Writes my happen in the order they are confirmed to ...


5

Even if you have plenty of DRAM, tempdb may still be used. This happens in a few situations: Snapshot isolation: Using this feature can create a lot of tempdb activity. Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is ...


0

If your MySQL server is handing all those connections parallely and you are trying to perform select and insert queries simultaneously you may end up with a lock. Your queries will be waiting to return the results on a locked tables. Check which queries are taking time by running SHOW PROCESSLIST Refine the queries that takes long time to compute, ...


-2

mysql started with option --defaults-file=FILE Use the specified defaults file change config.file(my.cnf)


1

You might also check the frequency / size of your commits: I ran into an issue recently in which I was trying to update > 1 million records in a single transaction. I got log messages similar to those described by OP, but the transaction could not complete even after several hours. When I broke the write into several smaller transactions (10,000 records or ...


0

You can use the below comment to check the connections: SELECT count(*) FROM INFORMATION_SCHEMA.PROCESSLIST; SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST order by DB; For MySql you can have the below value to optimize the performance innodb_log_file_size = 512M max_connections=2000 max_allowed_packet=64M wait_timeout=604800 key_buffer = 384M table_cache ...


1

Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring ...


1

From the online manual http://dev.mysql.com/doc/refman/5.6/en/show-open-tables.html The number of table locks or lock requests there are for the table. For example, if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1. If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client will block ...


1

Have you looked at stats from \s? I wasn't sure by 'transaction' you simply mean queries.


0

50K rows tables are rather small. Before throwing more hardware at it, I'd recommend checking/tuning the following: my.cnf setting: make sure you have good buffers on InnoDB buffer, Key Cache, Query cache, etc. If website is calling same queries over and over, query cache could help. Check slow query log and optimize worst queries. It'd say this would ...


-1

Create folder mysql_tmp on the biggest partition you have. Add the line tmpdir = /path/to/the/mysql_tmp to the section [mysqld] of the mysql.conf. restart mysql perform REPAIR for all the tables in your working base


0

I had same problem in Centos 6.5 couple of months ago. Make sure your mysql folder at new location has same ownerships and permissions. Problematic thing for me was SELinux permissions. Didn't get chance to find a way to stop SeLinux for mysql only. But I disabled my SeLinux for overall system using Check if its Enabled with selinuxenabled && ...


8

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember them being effective was in SQL Server 2000, so it is not terribly surprising that you find they are ignored in SQL Server 2012. For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is ...


0

During that period can you get a list of the sql processes running? show full processlist; Is this a server where you don't have root access to the actual OS like in AWS? If you do have root access and this is something you can research it might be unrelated to MySQL, i.e., some other process on the server is affecting performance. This might be out of ...


0

Partitioning is ideal for this sort of thing. For that you would need to define a key, however. If you really just want to slice a big pile of rows into small piles how about something like select top 1000000 <your columns> into dbo.SubTable1 from dbo.ReallyBigTable; delete dbo.ReallyBigTable where ID in (select ID from dbo.SubTable1); Repeat ...


1

Start with the database and work your way up. Check out the queries you run using the command line before sending them through a framework. You can activate the general log to check every query that's sent to the database if you want to check exactly what, exactly, is arriving at your server after having used the framework. Frameworks can be good (relieving ...


3

As a general rule of thumb, you should give the database as much information as possible about the task that you're implementing. How does this apply to your scenarios? Scenario 1 (INSERT .. SELECT) The database knows that you're about to bulk-move a whole set of data from one table or from one derived table to another. It can optimise the execution given: ...


1

I wrote these posts Mar 28, 2014 : MySQL not releasing memory Apr 24, 2012 : How costly is opening and closing of a DB connection? May 24, 2011 : What are user connections - when are the created and destroyed? and discussed the many buffers that are allocated for each connection. They are part of RAM and not encapsulated within the cache. Since the ...


2

mysqldump, or .sql files, which is what that module uses is probably the least efficient way to import a database (the only less efficient way I can think of is to import and commit each row at once). If you want to speedup the import process, you should change the method. There are several things that you can do in the MySQL configuration that will speed ...


1

For your specific example scenario 1 keeps all the data on the server. Scenario 2 will require the data to be packaged, sent over the wire to the client, where is must be buffered (and, perhaps, spilled to disk) then un-buffered, re-packaged and sent back to the server, where it will finally be processed. This network time adds up. Do this often enough ...


1

If you stay with MySQL 5.1 Since you cannot upgrade at this time, you need to install the InnoDB Plugin. It was available since MySQL 5.1.38 (See the Release Notes). I wrote a post about it : MySQL - Installing InnoDB Plugin Surprising fact: Percona already ahd the InnoDB in Percona Server 5.0.45 long before Once you do, you need to set the following ...


0

The same query can react differently under heavy load or not and this is the case here. Your query is taking time on the Sending data part on the master. It indicates that this query is gathering, computing and sending back data to the client. It probably stresses data on memory or on disk. If your query is accessing data on InnoDB tables, you can check the ...


0

Well, you shouldn't encounter any errors from SQL Server, if that is what you are asking about. SQL Server doesn't care where the files are located. That being said, there are good reasons for having different file types on different drives. Performance - Having different files on different drives means that there are different sets of drive heads reading ...


0

I often notice that PostgreSQL benchmarks run faster once more than half the CPUs are busy. I think that what happens is Linux tries to keep the client and the server on different CPUs, which slows down the intense back-and-forth between them (which is the main thing your benchmark is benchmarking). Once Linux is forced to run them on the same CPU, it gets ...


1

Remember, MongoDB has a dynamic schema. So it is perfectly ok to store this document: { "JobNumber" : "50001-01", "CustomerId" : "joe", "IdentifierNumber" : NumberLong(8812739), "TimesPrinted" : 0, "Packaging" : {"bundle":1200,"box":120,"pallet":3} } and this document { "JobNumber" : "50001-02", "CustomerId" : "jane", "IdentifierNumber" : ...


0

The difference is negligible much of the time. You'll see significant benefits if: There are lots of joins or lots of predicates; or The query contains long IN lists or similar large data elements In general, unless it's a "hot" code path where you really hammer a particular query I wouldn't make much effort optimising to focus on re-usable prepared ...


0

To change innodb_log_file_size do following Stop MySQL gracefully. Check the error log and make sure it was "Normal shutdown", no crashes, no errors etc. Move existing ib_logfile* to safe place. You will need them if you want to roll back Change innodb_log_file_size in my.cnf to the new value Start MySQL. InnoDB will notice there are no ib_logfile* and ...


0

You really need a fast HDD, but you need a properly sized innodb_log_file_size. Why not SSD for MySQL ? I learned something from this layout from a FaceBook Engineer's blog I wrote old posts about this Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations? Feb 06, 2014 : MySQL on SSD - what ...


2

In order for you to manage the transaction log size of your database(s) which is in full recovery mode you should be taking regular and frequent transaction log backups. The frequency should be according to RPO and RTO defined Do NOT change the recovery mode and shrink the log. You stated in your question that you didn't want any data loss by doing this ...



Top 50 recent answers are included