Tagged Questions
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
189 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
2answers
40 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
2answers
64 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 ...
1
vote
1answer
69 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 ...
1
vote
1answer
83 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
125 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 ...
1
vote
1answer
65 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. ...
2
votes
1answer
158 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
77 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 ...
1
vote
1answer
164 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 ...
3
votes
2answers
122 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 ...
2
votes
3answers
1k 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 ...
3
votes
2answers
137 views
Tablestructure for fast inserts/deletes with foreign keys
Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:
id | competitors | some infos about competitor | ...
1
vote
1answer
481 views
InnoDB Performance Tuning
I'm new to database administration so please excuse any omissions of what would seem like basic info to provide for this problem.
I'm a RoR developer and our team is currently short a DBA, so we're ...
1
vote
2answers
356 views
Mysql innoDB write operations are extremely slow
I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
3
votes
2answers
115 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
1
vote
2answers
816 views
Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?
I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). ...
2
votes
1answer
263 views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
2
votes
2answers
96 views
MySQL Server Config, Small Timeframe, Extreme Usage-Scenario
I want to configure a MySQL server for the following scenario.
A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a ...
2
votes
3answers
1k views
How can I configure MySQL Innodb to handle 1000s of inserts per hour?
I have a very high traffic website where it is possible that 1000s of new records are inserted every hour.
This one error is crippling the site:
PDOException: SQLSTATE[40001]: Serialization failure: ...
1
vote
1answer
455 views
MySQL / InnoDB: Does daily optimization improve performance?
Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
10
votes
3answers
525 views
Where is the INNODB Performance Leak?
I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me.
The Situation
I am working on a system which handles a lot ...
2
votes
0answers
2k views
Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded
Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
1
vote
1answer
120 views
Optimizing table and choosing storage engine
Having the follow MyISAM table:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
1answer
344 views
Fastest way to copy data from MyISAM to InnoDB
I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.
However, every approach I try ...
2
votes
1answer
92 views
why/how does the number of matched columns influences the way of excecuting a query
Imagine the following situation:
Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index.
FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4
Table B uses InnoDB and ...
4
votes
1answer
1k views
Why is DELETE so much slower than SELECT, then DELETE by id?
I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
5
votes
3answers
3k views
How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
1
vote
3answers
502 views
whether a big (but not used) ibdata1 slow anything down
Hi I have an ibdata1 that is around 10 gig. I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore.
I recently ...
4
votes
1answer
1k views
Capabilities of InnoDB INSERT Performance
Hi I am running the most recent version of Percona Server.
Server version: 5.5.24-55 Percona Server (GPL), Release 26.0
I have a 10 cpu box of these characteristics.
processor : 0
vendor_id ...
2
votes
1answer
163 views
Need advice for a 'tag' table design
My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design:
2 tables option : 1 table for the ...
3
votes
2answers
1k views
Frequently slow queries while in the 'Update' state
I have two tables that I update very frequently, one with a direct UPDATE .. and another INSERT .. ON DUPLICATE KEY UPDATE ... Normally these queries are instant but at times will take 0.1s to 1+ ...
6
votes
2answers
399 views
Investigate peak in MySQL throughput
Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the ...
1
vote
5answers
4k views
Mysql: Insert performance INNODB vs MYISAM
I am inserting into a simple small table with 5 attributes and 1000 rows.
I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds.
I changed the engine to MYISAM, then the ...
2
votes
2answers
827 views
How to sync two MySQL high load tables with 10M rows?
I have two identical MySQL innodb table with 10M rows each. One is used at backend while other at application end. Thus, either of them loaded. I need to synchronize them in a way which is fast and ...
6
votes
4answers
3k views
Why using innodb_file_per_table?
There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
4
votes
4answers
3k views
Mysql int vs varchar as primary key (InnoDB Storage Engine?
I am build a web application (project management system) and I have been wondering about this when it come to performance.
I have an Issues table an inside it there are 12 foreign keys linking to ...
3
votes
2answers
2k views
Percona vs MySQL
What is Percona?
How does it differ from MySQL?
When should we consider switching (or upgrading) from stock MySQL to Percona?
To add some specifics in our situation we almost exclusively use ...
2
votes
1answer
417 views
Are many NULL columns harmful in mysql InnoDB?
As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance.
Does the presence of excess ...
3
votes
2answers
859 views
“freeing items” taking forever on inserts, updates and deletes
Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/).
After the migration, ...
3
votes
2answers
4k views
Is InnoDB Engine up to speed against Memory Engine?
I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows:
SELECT P.col1, P.col2, ...
6
votes
2answers
4k views
Fastest way to check if InnoDB table has changed
My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.
My ...
1
vote
3answers
442 views
Improve `Update` performance (rows locking issue)
I am running 30 scripts (PHP CLI) on Linux , each script are updating (loop) the data in the MySQL database.
When I typed 'mysqladmin proc' in terminal, I can see a lot of rows has been locked for ...
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
16
votes
3answers
16k views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help
Setup
The servers are quite hefty with an ...
4
votes
3answers
6k views
Optimizing InnoDB default settings
I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
2
votes
2answers
2k views
Using MySQL triggers or transactions?
I want to ask your opinion in using MySQL triggers or transactions in a website.
Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
6
votes
2answers
7k views
MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load
i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
