Tagged Questions
0
votes
2answers
17 views
Partitioning MySQL for “expired” transactions to improve performance
I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly ...
0
votes
2answers
46 views
How do I use subquery on the same table in MySQL?
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could ...
3
votes
3answers
71 views
How can I improve this query?
About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
0
votes
1answer
33 views
Having too many connection problem
We have an magento store and having 9K products and 2 store views the trouble is we are getting too many conection error while the site is in normal loads.
1) We have 100 connectoin open in mysql.
2) ...
2
votes
1answer
36 views
How to ensure that date-range queries involving multiple time zones are sargable
I am building a web analytics application for search engine traffic only. You can see some screenshots here: http://myhappyanalytics.com/
It works similar to Google Analytics but it only saves and ...
0
votes
1answer
41 views
MySQL DB Design
I am designing a db for a Social Network type website where users enter lot of information varying from family member details, education, employment, personal favorite such as TV, movie, music, food, ...
3
votes
1answer
57 views
How can I get this query to use it's index?
Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).
As I don't really know what I'm doing I tried adding indexes to all columns in ...
0
votes
2answers
178 views
How to tune mysql to be inmemory-like
I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is).
Is it possible to force ...
0
votes
1answer
56 views
MySQL Read Speed and Partitioning on Separate Drives
Introduction
Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
2
votes
2answers
115 views
MySQL replication: most important config parameters for performance on slave server?
I'm setting up a mysql master-slave configuration, where slave is located on a much weaker sever.
Since, if I understand correctly, slave only works on updates/inserts, what are the most critical ...
0
votes
0answers
18 views
Table partionning problem
I need your help for a partionning problem. We are using a "log_event" table which log a lot of events sent by our players (subscription, connection, play, share, etc...). We insert 5,000 row / s in ...
0
votes
2answers
36 views
Is there slowdown inserting into an InnoDB table that has no index set?
I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.
Those tables only contain a few thousand rows.
Would it be faster to INSERT data ...
0
votes
1answer
51 views
Cannot Utilize Maximum CPU and Memory Usage for MySQL
Good day.
I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem.
I have a single stored procedure ...
5
votes
1answer
96 views
Start/Stop MySQL
I'm seeking help to understand what happens when the following command line is executed:
root@prodn$ service mysqld stop
Yes, it shuts down the MySQL server so access to it is no longer available ...
3
votes
1answer
90 views
Simple MySQL query randomly takes forever
I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will ...
0
votes
0answers
14 views
MySQL shared server regular lockup
I have a web/ database server with 5 websites on it, all of which execute fairly complex queries regularly.
Some of these queries appear in the slow log and take around 0.6-0.8 seconds. However I ...
1
vote
1answer
66 views
Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?
We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
0
votes
1answer
62 views
Mysql: order by field or select union?
As a newbie, I expected Mysql to return the rows of this query ordered by their ids order in the id clause
select * from test where id in (3,1,2)
Unfortunatelly for me, that's not true. The results ...
1
vote
1answer
79 views
Mysql DB server hits 400% CPU
I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top.
- load average 40 to 50
- CPU % - 400%
- idle % - 45%
- wait % - ...
2
votes
1answer
116 views
Innodb Slow queries since convert from MyISAM
a few days ago we converted some write intensive tables from MyISAM to InnoDB hoping to have a better performance due the better locking system of InnoDB, but instead of gain performance, we start to ...
1
vote
1answer
84 views
For a InnoDB only DB, which of these elements can be removed?
So, I'm trying to set up a Drupal 7 my.conf file that's combining best practices from various performance blogs. I'm realizing though that some of them are older than others, and many aren't assuming ...
2
votes
2answers
125 views
MySQL - Normalization over meaningless data - Compound Primay Keys or Surrogate ID field
Please forgive my title; if you have a suggestion for it, feel free to comment.
I have a database:
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`isbn` VARCHAR(255) NOT NULL,
`title` ...
1
vote
1answer
35 views
Mysql performance for a nullable unique id column in a large table that references a record in another table
We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the ...
1
vote
1answer
56 views
Why does that query cause lock wait timeouts?
From time to time, I find a lot of these errors in my PHP error log:
MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL
The problem persists for about 2 or 3 minutes. ...
1
vote
1answer
76 views
LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?
I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE.
But we have 1GB each day we want to update in a batch process using ...
3
votes
1answer
100 views
Can I use mysql to serve 100's of millions of small image files?
I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
1
vote
1answer
47 views
Most efficient way to sort data fields into SQL
I'm trying to decide on the most efficient way to sort various data values. Here's how the data arrives:
Device X sends a text string "name=value&name2=value&name=value"
On arrival that ...
0
votes
0answers
345 views
'Incorrect datetime value: for column 'last_login' at row 1'
i am getting below error:
Last_SQL_Errno: 1292
Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. ...
4
votes
4answers
114 views
Handling a big table in mysql
I have a really big table (approx. > 100.000.000 rows and a size of > 50GB) and it's getting a big performance kill right now. Beneath the primary key (id) it uses a fulltext key on a varchar(500) ...
0
votes
1answer
53 views
Sleep Processes are Running Longer Than Allowed
From time to time my mysql server will run into a "too many connections" error. I believe part of the problem is large amount of sleep processes that are running, some over 1000 seconds.
When ...
0
votes
2answers
67 views
Multiple database servers for performance vs failover
If I have two database servers, and I am looking for maximum performance vs high-availability, what configuration would be best?
Assuming the architecture is two load-balanced web/app servers in ...
2
votes
1answer
78 views
How to improve INSERT speed in large table that has unique constraint
I have a simple MyISAM table:
explain entities;
+------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra ...
2
votes
0answers
30 views
How can one improve the performance of a query that selects on a low cardinality column in MySQL?
I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
1answer
157 views
What causes InnoDB to write 100% more pages while slowing down answering queries?
I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
0
votes
1answer
69 views
Mysql. Block/disconnect slow queries
I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches ...
0
votes
1answer
74 views
Maintenance in MYSQL when innodb_file_per_table disabled
I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.
But what if I ...
2
votes
0answers
134 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
1
vote
2answers
218 views
SELECTing multiple columns through a subquery
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
SELECT DISTINCT petid, userid,
(SELECT ...
1
vote
1answer
278 views
MySQL performance tuning + queries stuck on “Copying to tmp table”
The latter part of the question's title (queries stuck on "Copying to tmp table") has been addressed many times, and I have spent a fair amount of time researching this. I would appreciate it if you ...
0
votes
1answer
84 views
Which is better: many join conditions or many where conditions?
I am trying to compare two queries:
Query 1:
SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a
WHERE tableA.b=tableB.b AND tableA.c=tableB.c AND tableA.d=tableB.d AND ...
0
votes
1answer
142 views
Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes
I just started dabbling with query cache for our CMS.
Can anyone tell me why (or at least a good guess) I get alot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free?
...
0
votes
0answers
27 views
How Best to Parition Temporal Data for Access By Date
The latest edition of the High Performance MySQL book has this to say about paritioning a large table where all the hot data is clustered temporally:
Suppose you have a table with an ...
2
votes
1answer
269 views
What's the difference between POINT(X,Y) and GeomFromText(“POINT(X Y)”)?
I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table.
...
0
votes
0answers
69 views
What is faster - multiple access of table or using temporary table?
I have created two tables
create table one(ref_no int, name varchar(100), design varchar(100));
create table two(SNo int auto_increment primary key, use_date Date, ref_no int references one(ref_no), ...
1
vote
1answer
156 views
MySQL 5.5 - determining correct write[read]_io_threads on high-end system?
Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server.
Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write.
Currently we are running on the default ...
2
votes
1answer
146 views
Does MySQL have problems with nested insert like with subqueries in where?
Query 1:
INSERT `personal`.`locations`
SELECT DISTINCT `s`.*
FROM `references` `t`
JOIN `locations` `s` ON `first_id` = `s`.`id`
WHERE
`lat` >= 37.3
AND `lat` <= ...
3
votes
2answers
121 views
Using MyISAM for reading and InnoDB for writing data
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.
I want to use two structure like ...
1
vote
1answer
51 views
tuning feed item's table query with multiple condition
I have a query like this:
SELECT *
FROM `rss_item`
WHERE `feed_id` = 1 and `timestamp` < 1350698635
ORDER BY `rss_item`.`timestamp` ASC
MySQL: 5.1; table engine: innodb
Explain result:
...
0
votes
0answers
31 views
Performance While merging two mysql DBs
I have two similar schema DBs which I need to merge. I was thinking about the performance issues which I need to keep in mind while creating the script. These are the ones which I can think of.
...
2
votes
3answers
969 views
MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine
I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.
I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
