RE: io thread very slow copying binlogs
Hi Brad, > MySQL community edition 5.6.29, running Linux. > > Binlogs never seem to get caught up on slaves. > > I've done all I can, to validate that this isn't network or disk related. > > Disk tests (using iostat and other methods) show lots of bandwidth left on > the slave and master. > > Network tests, such as: > > - using scp to copy binlogs directly > - using different NICs to copy binlogs > - using mysqlbinlog to snag logs (the most 'real' way I can think to > simulate the replication thread copying binlogs from the master) > > All seem to show that network speed is blazingly fast. > > Yet, MySQL is barely getting 4mbit/sec across the network, and onto the disk. > And that's on a good day. > > Any immediate suggestions here? This seems very weird, and SQL thread is > constantly running out of stuff to process. Networking is not my strong-suit, but I have a suggestion: Try lowering slave-net-timeout http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option _mysqld_slave-net-timeout I remember that we lowered the default in MySQL 5.7 (from 1hr to 60 seconds) so that the connection between master/slave would be considered broken faster. If you have the throughput on a graph it might better explain if it is a constant 4mbit/sec or more broken. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, Found this after I found out what caused it: https://www.percona.com/blog/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/ I suspect that this also apply to ext4, or? I would go more specific and say that sync_binlog=1 does not play well with single-threaded workloads. MySQL 5.6 and above can group commit to the binary log (reducing a lot of IO if transactions arrive at the same time). I have an explanation on how it works here: http://www.tocker.ca/2014/12/30/an-easy-way-to-describe-mysqls-binary-log-group-commit.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, - The data collector system processing jobs, is it multi threaded? Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases system using several script running sequently in an infinite loop. Each script taking care of part of the job of processing the data. Newer MySQL releases are often more scalable (= works better for multi-threaded applications). On a single threaded basis though, as functionality is added it is difficult to always keep the same performance numbers. When you mentioned that there was a regression in 5.6 as well, it might be interesting to compare to the most recent 5.6. There were some single-threaded regression fixes after the GA release. - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) This will be BIG, since it it so many different queries and tables. Not sure what you mean by We pay close attention to regressions”. Bugs that introduce a loss of performance have a higher priority factor applied to them. We are very interested to hear clear cases where an operation takes more time in MySQL 5.7 versus earlier releases. In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. I know, but sofare no swapping is taking place. The test server is used for testing this system only. And in 99% of the time, only one client is using the SQL server. This makes some sense based on your workload being single threaded as well. It looks from show engine innodb status that your server is just starting up, and caches are empty, so versus a 5.5 server that has been running for a while it will likely be slower. What you may be able to do to track a specific set of statements that take longer in 5.7, is convert a mysqlbinlog to sql (mysqlbinlog mybinlogfile output.sql) and replay it on a 5.5 and 5.7 server. If you can pair it down a little from there, then the output.sql file can be uploaded to bugs.mysql.com and evaluated. Thanks! - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query time taken on disk
Hi Satendra, On Jul 14, 2014, at 3:48 AM, Satendra stdra...@gmail.com wrote: Hi there, I'm struggling to find the total time taken by a database query on the disk? As I understand when a database query start execution it takes some time inside the database engine some time to seek the result from disk (if that is not in cache/buffer) Can anybody from the group please suggest any clue about the execution time on the disk? I have a performance_schema example demonstrating total IO wait time for a workload: http://www.tocker.ca/2014/02/18/todays-practical-use-case-for-performance-schema.html To prepare this data non-aggregated (per-query) is always a little bit difficult: - With select statements there is read ahead. - With write statements there is redo logging (which is grouped together with other statements). Maybe someone else on the list has better ideas on how to accommodate this? - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW FULL COLUMNS QUERIES hogging my CPU
Hi Jatin, On Jun 2, 2014, at 7:56 AM, Jatin Davey jasho...@cisco.com wrote: I found this blog while trying to find a fix for my problem. Thought it might be useful to share. Here it is: http://whirlpool.net.au/blog/5 To confirm - did you manage to fix your problem? I was about to comment that it looks like queries generated by an ORM or connector. It looks like from your version string you have an MySQL enterprise, may I suggest creating a ticket with support? Regarding your most recent reply: All the SHOW FULL COLUMN queries that we do on the respective tables are very small tables. They hardly cross 50 rows. Hence that is the reason whenever these queries are made i can see high cpu usage in %user_time. If it were very large tables then the cpu would be spending lot of time in waiting for I/O as the databases reads would have to do a lot of I/O to read the data from the disks. If it helps - I believe performance should be similar with large tables, since in the case of big table or small table, what is accessed here should just be meta data. Earlier versions of MySQL could rebuild InnoDB statistics on some SHOW commands (I’m not sure about SHOW FULL COLUMN), but this is now disabled by default: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance boost by splitting up large table?
Hi Larry, On May 14, 2014, at 5:05 AM, Larry Martell larry.mart...@gmail.com wrote: We have a table with 254 columns in it. 80% of the time, a very small subset of these columns are queried. The other columns are rarely, if ever, queried. (But they could be at any time, so we do need to maintain them.). Would I expect to get a marked performance boost if I split my table up into 2 tables, one with the few frequently queried columns and another with less frequently queried ones? Doing this will require a lot of code changes, so I don't want to go down this path if it won't be beneficial. Can folks here offer their experiences and learned opinions about this? There are some advantages to splitting the table. If we use InnoDB as an example: Storage is row-oriented: - All those less-needed columns will by stored together in the same page (unless text or blob - in which case it can be a pointer to an external page). - This can consume more memory than required as the less important columns has to be loaded with the important columns. Locking is row-oriented: - Having a non-normalized structure may mean more contention. - One transaction is updating one part of a very wide row, another transaction is blocked waiting to update a different column. This might not happen in a normalized schema. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi Christophe,
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
When a MySQL server receives a query, it goes through a process called query
optimization and tries to determine the best way to execute it (based on
availability of indexes etc). You can think of this as similar to how GPS
software picks the fastest route - it is very similar.
The order of the WHERE clause does not matter, and in fact more complicated
transformations happen in query optimization automatically. For Example:
SELECT * FROM Status WHERE 1=1 AND DWProcessed = 0;
1=1 is detected as a tautology and removed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Reindl, The order of the WHERE clause does not matter that is simply not true otherwise that documentation would not exist http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html Yes, the order of the columns in composite indexes *does matter*. This is a different question to what Christophe asked. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hi Brad, I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down? mysql SHOW CREATE TABLE `my_table`; ... 1 row in set (37.48 sec) We tend to execute many of these statements concurrently, but it's never been a problem until recently. I upgraded the IO subsystem, and our statistics indicate that it's not maxing out IO (at least IOPS). This is problematic because the ORM we're using uses that to figure out the structure of our DB... I am going to guess that it could be contention trying to open the table. i.e. when you run into this issue and run SHOW PROCESSLIST, does it show any threads waiting in state “Opening tables”? (If you can paste a sanitized SHOW PROCESSLIST this helps a lot.) - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hi Brad, That sounds right. Here's the process list (scrubbed) and the show engine innodb status. Notice that all of the SHOW CREATE TABLE aren't for hte same table, just got cleaned up that way. It shouldn't matter if they are for the same or different - in 5.5 there is one table open cache “instance” - so only one person can be opening or closing tables at a time. In 5.6 this is configurable to reduce contention: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances https://gist.github.com/bradhe/c9f00eaf93ac588b8339 We have the defaults for table_definition_cache and table_open_cache (400 each). I am going to guess and say that you may have a sharded environment with a large number of tables? Another solution that may work, is to increase these caches. In most cases it will work fine, but MPB has also blogged about the exception where you can get negative scalability (so many cache misses the cache can’t work effectively): http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ I’m not sure how up-to-date the edge case issue is. But hopefully this gives you some starting points. (Others, feel free to chime in!) - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW CREATE TABLE suddenly slow on InnoDB?
Hi Brad, We actually only have about 60 tables in that database. I've tried increasing the cache and open tables limits and get the same behaviour. Hmm.. Shawn’s guesses are probably better than mine then. A few other tests I've tried: 1. Stand up a new machine, dump just the schema in to it, and run the test. Performs flawlessly, so it's probably just this machine/snapshot. 2. Stand up a snapshot of my existing machine, truncate the tables, optimize the truncated tables, and run the test. I get the bad behavior! Correct me if I'm wrong but it'd appear that there's just something fundamentally broken this machines' InnoDB ibdata file/data dictionary? All the contention comes out of the dictionary, but I'd expect the optimize to re-write the dictionary entries… InnoDB data dictionary is always stored in ibdata1 + there is MySQL data dictionary stored in .frm files. I can’t think of a specific reason why accessing it could be slower until after a dump and restore. I believe that Performance Schema could be helpful here. This is a view that will work with 5.5: https://github.com/MarkLeith/dbahelper/blob/master/views/p_s/io_global_by_wait_by_bytes.sql (PS is not enabled by default in 5.5, but file IO is instrumented.. you just need to turn it on.) - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: New to MySQL
Hi Asma, It looks from this error that there is an existing 5.1 installation present. As well as RPM packages (which can be downloaded from http://dev.mysql.com/downloads/mysql/5.6.html), there is also a yum repo for Enterprise Linux 6. It is available here: http://dev.mysql.com/downloads/repo/ Instructions on upgrading are here: http://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/updating-yum-repo.html I actually prefer (and would recommend) the yum repos, since the underlying rpm packages are in a newer format. - Morgan On Mar 3, 2014, at 1:00 AM, Asma rabe asma.r...@gmail.com wrote: I installed my sql using RPM as follows: rpm -i MySQL-server-5.6.16-1.el6.x86_64.rpm I got the following errors file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/danish/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/dutch/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/english/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/estonian/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/french/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/german/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 When i tested if the server has installed or not bin/mysqlshow -bash: bin/mysqlshow: No such file or directory Any help is appreciated . Best Regards, Rabe On Fri, Feb 28, 2014 at 1:36 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Asma rabe asma.r...@gmail.com Subject: Re: New to MySQL so,Is it recommended to install RPM bundle which has compatlibs, MySQL serveretc rather than installing the server only? The Server should suffice; the Bundle is merely a convenient way to download ALL the RPMs in one go. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Crash after shutdown/restart
Hi Jørn, I would say that this sort of performance drop is not typical. Some users have reported a smaller performance loss in single threaded workloads in 5.6. But dropping from an average of 1800 jobs per minute down to 300? I don't think that should be expected. I would agree with you here. A few weeks ago I stopped the test and restored the initial database starting the test over again. Now the performance was back to 1700 jobs per minute, but it slowly went down as the test ran. Yesterday it was down to 300 per minutes and still (but very slowly) dropped. Yesterday I did the following: * stopped the test * dumped all databases * stopped the mysql server 5.6 * Downloaded 5.5.33-log source and installed it * Removed all inodb* and ib_log* files * Removed all databases * Started and initialized mysql * Restored all databases * Started the test where I left it. After a few hours I could see that the performance was back to normal - 1800 - 2000 jobs per minute. There is no sign of drop in performace so far. Please explain. I want to suspect that there might be a specific query regression (where 5.6 has introduced a new feature, and you fall in an edge case where it is being optimized incorrectly). The way to deduce this is to run EXPLAIN for key queries in MySQL 5.6 and 5.5, and compare for differences: https://dev.mysql.com/doc/refman/5.6/en/explain.html When you have one, there are a lot of people on the list that would be happy to pair this down to a test case, and file a bug. There is also a switch to disable specific optimizations, so you may have an easy work around that would allow you to restore back on 5.6: https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: LIKE sql optimization
Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error - ould be looked up with ./bin/resolveip
Hi Érico, mac:mysql-5.6.16-linux-glibc2.5-x86_64 ericomtx$ sudo scripts/mysql_install_db --user=mysql sh: ./bin/my_print_defaults: cannot execute binary file FATAL ERROR: Neither host 'mac.local' nor 'localhost' could be looked up with ./bin/resolveip Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option how my /etc/hosts file should look like so I can fix this ? The hostname resolving script is used to build the privileges tables. Here is what I have in /etc/hosts on my OS X laptop: morgo@Rbook:~$ cat /etc/hosts ## # Host Database # # localhost is used to configure the loopback interface # when the system is booting. Do not change this entry. ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost fe80::1%lo0 localhost 127.0.0.1 rbook - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 release plan/date
Hi Igor, When MySQL 5.7 planned to be released as stable/production ready? We do not provide planned release dates. If I can recommend a video to watch about the release process, please watch Tomas Ulin’s Percona Live keynote April 2013: http://www.youtube.com/watch?v=OpHTV59I1gs Watch from 4m30 onwards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Index Query Tunning
Hi Anupam, We are keep on getting deadlock due to index locking, there is index on FLAG, we can allow phantom read in session 1, we tried with READ COMMITTED but still same, I think issue with next-key locking. Did you try setting binlog-format=ROW as well? I have a brief explanation of this here under 'Write scalability of certain statements': http://www.tocker.ca/2013/09/04/row-based-replication.html If i do following in SESSION 1 would that help in locking or still it would lock index. Any suggestion. SESSION 1: START TRANSACTION; UPDATE XYZ SET FLAG=0,ID=(@oID:=ID) ORDER BY TIME LIMIT 1; SELECT @oID AS ID; COMMIT; Locks are held for the duration of the transaction, so I don't think it will help here. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Crash after shutdown/restart
Hi Jørn, But I must say I'm not very impressed by the speed. I'm running a test on an application that do a lot of reads and writes queries and the general performance has dropped to 50% of the what I had in 5.5.20. I would say that this sort of performance drop is not typical. Some users have reported a smaller performance loss in single threaded workloads in 5.6. It's possible to redeem some of this performance loss by tuning Performance Schema, with the caveat that you will lose some visibility into diagnostics: http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html It's not clear from your description if the test is run in parallel-threads, but this is something you may want to research. May I also suggest commenting out/assuming some defaults for some of your config settings. 5.6 has much better defaults, and it is always better to 'maintain less customization' yourself: key_buffer_size = 384M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 14 I also don't typically recommend these settings, but there will be some cases when they are warranted: innodb_thread_concurrency = 14 innodb_support_xa = 0 innodb_fast_shutdown= 0 - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key reference
Ugh, that seems quite right. Now, why did they do that? It was added for compatibility. A separate specification is less convenient, and also less transparent. Please click affects me on http://bugs.mysql.com/bug.php?id=47771 - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key reference
On 1/6/2014, 12:21 PM, h...@tbbs.net wrote: Are INNODB foreign-key references ignored in 5.6? You might be hitting: Important The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored. MySQL only accepts REFERENCES clauses defined as part of a separate FOREIGN KEY specification. See: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
