1
vote
1answer
16 views

Need help tuning my.cnf file

I need help tuning mysql. Currently it is using a lot of CPU. I dont find any query that last longer than 3 seconds in SHOW PROCESSLIST and there are only around 20-25 running most of the time. Still ...
0
votes
1answer
21 views

Does having many databases affect MySQL performance?

I have huge unused databases in MySQL. Do they affect my MySQL engine performance even if they are unused? Do they consume memory on their own? Thanks
1
vote
1answer
45 views

Optimizing a slow MySQL query

I'm trying to optimize a ~100GB database. All data values are in one single innodb table, is this bad? Here's what a common query looks like: SELECT `CensorCode`, `LocalDateTime`, `UTCOffset`, ...
0
votes
1answer
24 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
0
votes
1answer
22 views

Impact of replication compression & encryption on performance, traffic?

What is the impact of MySQL compressed-slave-protocol on replication traffic? According to High performance MySQL it can lower the traffic to a third of the same replication without the compression. ...
0
votes
0answers
14 views

Performance : MySQL replication or only one big server?

I'm searching about the best choice. We have to host a website with 10.000 - 20.000 visitors per day actually, growing. At the beginning, which would be the best : Having 2 MySQL servers (12 GB ...
0
votes
2answers
37 views

How to fix slow LIMIT queries with big offsets without heavily rewriting them?

Imagine a very basic example of your average discussion board. For example: CREATE TABLE threads ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) ) CREATE ...
0
votes
0answers
23 views

Big Mysql table optimization

I have a table that I use to cache google translate API answers. Table structure is orig_string and group are indexes Now it has more that 1million rows with 35000 distinct values in group field. ...
1
vote
0answers
21 views

“dynamic set” in a MySQL table

I have a table where one column will have a few values many times (maybe 20 different values). Now I would like to use something like the set to improve performance and memory consumption. The problem ...
0
votes
1answer
41 views

Slow MySql server - even on a super fast server

I have just upgraded my 12 year old server to a really fast new one. It is a dedicated server, Dell R620 1 x Intel E5-2620 (2.0GHz, 6C) CPU 32GB RAM - 4 x 250GB SSD RAID 1, in short vastly faster in ...
3
votes
2answers
437 views

A little clarification on how 'LIMIT' works

Not being a full blooded member of the DBA clan, but more of a dev clan, I find myself trying to figure out some slow running queries. The subject of my question however is not 'Why my queries are ...
2
votes
1answer
35 views

Will MySQL be effective when selecting a row from a table with billions of records?

Note: this is a hypothetical scenario Lets say that: I'm running MySQL, InnoDB engine, UTF-8 encoding I have a table with 50+ billions of rows The tables primary key is a BIGINT The table also has ...
0
votes
0answers
31 views

Efficient way to fetch/update X records in a loop

What I need is a simple process that handles requests. I have setup a simple table as follows: CREATE TABLE Requests ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, request_param VARCHAR(250) ...
1
vote
1answer
43 views

Innodb buffer pool and multiple joins

assume we have 1 Billion records and want to store them in One or Three tables by post_type. post_id -> primary key post_type -> ( page, group, user ) user_id -> index date -> ...
0
votes
1answer
54 views

How should I config the my.ini(or my.cnf) file for mysql?

Thanks for taking your time to look at my question, and I hope that you can help me with this. I have a Windows Server 2012 server, it has: Memory : 3840MB Ram Processor: Intel(R) Xeon(R) CPU ...
1
vote
1answer
47 views

MySQL 5.1: Upgrade innodb plugin

My application is running on MySQL 5.1.58 community edition. As I studied on SO websites that After acquisition by Oracle, new InnoDB plugin has been released and MySQL 5.1 with InnoDB plugin is the ...
0
votes
0answers
29 views

How can the performance of a query that has a condition on two fields of each row be improved?

I have an images table with approximately 5 million rows in it. For the purposes of this question lets say the table looks like this: CREATE TABLE `images` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
33 views

What's the complexity of renaming a column in MySql?

We want to rename a column in our MySql (version 5.5) database with something like this: ALTER TABLE t1 CHANGE a b INTEGER; Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
1
vote
2answers
78 views

Baffled with MySQL LEFT JOIN query that I have to speed it up

I am kind of a puzzled with this query trying to optimize it but no way I am able to (due to my limited knowledge as a DBA). So, I asked the experts. In my setup I have three (primary) tables. ...
0
votes
1answer
34 views

Concatenating multiple rows in one query

I have two tables with the following design movies : ID,name genres: movie_id, genre Each movie can have more than one genre. I wish to list all movies and their genres, so I'd get something ...
0
votes
0answers
43 views

Mysql profiler different duration information_schema.profiling

Hi! I have a problem and no idea how to resolve it. I'm trying to use mysql profiler. I'm using the command: set profiling=1; to turn it on. Later, i execute my query which is correlated ...
0
votes
1answer
46 views

Is there a table size above which SELECT performance degrades significantly?

In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, ...
0
votes
5answers
150 views

How to determine why a MySQL server has become slow and requests sometimes time out?

I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having. Most of the time, responses are returned quickly from the ...
-1
votes
1answer
42 views

Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows ...
0
votes
0answers
31 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
0
votes
1answer
36 views

MySQL: What points should we consider when create composite index

There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key. Beside this, we create composite index on the ...
0
votes
0answers
29 views

Slow exporting SAP Data Services to MySQL (Oracle is faster)

We've a MySQL 5.1 server and we are trying to import data from SAP Data Services from another server. It works but it takes 3h! We tried to setup an Oracle XE server to test the same operation and it ...
0
votes
0answers
28 views

How to optimize subselect join on large tables

There exists a need to migrate from one definition of a column to the next, so a backfill must occur. In order to get a grasp for the task at hand, I needed some background on the data being updated. ...
0
votes
0answers
25 views

Mysql View Performance on Aggregate Functions

I've a table that contains weekly statistical infos. ex. Society - Week - Year - Data AAA Inc - 1 - 2014 - 10250 AAA Inc - 2 - 2014 - 5235 BBB Inc - 2 - 2014 - 3210 ... Over this table i need to ...
4
votes
1answer
110 views

Settings for a MySQL backup only slave instance

I have two instances of MySQL running one a server, set up as master and slave. It's all working smoothly together and I'm only using the slave instance for backups to avoid locking up the live ...
2
votes
2answers
134 views

MySQL on SSD - what are the disadvantages? [closed]

Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost. Given that 20% of all queries in my database are INSERTs and ...
1
vote
1answer
144 views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
5
votes
1answer
289 views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
0
votes
1answer
73 views

Poor SQL performance with raid10

Maybe I'll try my luck here. Server Fault site was quite hostile for non-professional server user. I recently bought HP ProLiant ML310e Gen8 v2 server with 8G's RAM and 4 HP 1TB 6G 7.2k rpm SATA ...
0
votes
1answer
63 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
0
votes
3answers
38 views

Should frequently accessed tables containing large blobs with one-to-one relationships be normalized and columns split into two tables?

I have a frequently accessed table containing 3 columns of blobs, and 4 columns of extra data that is not used in the query, but just sent as result to PHP. There are 6 small columns (big int, small ...
0
votes
0answers
67 views

Order before Group

I have this query: SELECT HOTEL.hotelname AS NAME, HOTEL.`boardbasis` AS board, HOTEL.`engine`, HOTEL.`itemcode`, Group_concat(Round(((hotel.cheapestprice / 2) + ...
1
vote
1answer
161 views

Delete data from huge InnoDB table from MySQL Master and Slaves

We have some huge tables in MySQL database, We have already archived/deleted data older than 2012 in 2013, Now we need to archive/delete data older than 2013 means we need to archive data for year ...
2
votes
3answers
124 views

MySQL query extremely slow after server restart

I am managing a very large database for a custom application I wrote to keep track of visitors. The DB server was rebooted for the first time in over a year - not even to install any updates, but ...
1
vote
1answer
110 views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - ...
1
vote
1answer
108 views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
0
votes
1answer
30 views

Should the value of a select max be indexed when the where clause is already indexed

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company ...
0
votes
0answers
25 views

Why does a scrambled junction table destroy the speed of my query?

I have two schema in a MySQL database, one containing raw data in MyISAM tables and the other containing the results of some processing tasks performed on the raw data, also in MyISAM tables. For ...
2
votes
1answer
107 views

Is this amount of disk IO expected with MySQL?

I am running MySQL 5.5.34 on an Amazon EC2 micro instance with EBS (elastic block storage). The instance has been running for around 10 days and according to Amazon I've accumulated around 8.5 ...
0
votes
0answers
98 views

how I can set tmp_table_size for 16GB long table?

I am posting the same question as I posted in SO, but somebody advice me to post it here. I have a 16GB big table with 8 partition. And RAM 4 GB. I am running a query - SELECT ...
0
votes
0answers
14 views

how I can set tmp_table_size for 16GB long table? [duplicate]

I have a 16GB big table with 8 partition. And RAM 4 GB. I am running a query - SELECT c.eveDate,SUM(c.dataVolumeDownLink)+SUM(c.dataVolumeUpLink) FROM cdr c WHERE c.evedate>='2013-10-01' GROUP ...
4
votes
2answers
130 views

Architectural Help Needed - MySQL or NoSQL for a table with 54 million records

We have a table with 54 million records. Here is the table structure. CREATE TABLE `metaplay` ( `track_id` int(11) NOT NULL DEFAULT '0', `user_id` int(11) DEFAULT NULL, `completed` int(11) ...
2
votes
2answers
366 views

Mysql Performance optimization, max CPU load, low qps

I would like if anybody could look into performance optimize this: I have a Ubuntu 12.04 running on VMWare 5.1, with 32 GB RAM and 8 Cores (No problems regarding cpu scheduling as the VM is almost ...
0
votes
1answer
36 views

Does size of one particular column in a database affect query performance?

I have a table user_list CREATE TABLE IF NOT EXISTS `user_list` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `email` ...
0
votes
2answers
100 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...